Importing to Access Records


G

GLT

Hi,

I am importing .txt files into records into my database.

All works fine, except at the end of the import it displays a 76 path not
found error. I think it may be looping one too many times but I am not sure.

The code I am using is as follows:

Sub sImportAll()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("tbl_LogFileDetails")

On Error GoTo E_Handle
Dim strImport As String
Dim lngChars As Long
Dim intFile As Integer
Dim MyFile As String
Dim Fname As String
Dim Counter As Long

'Create a dynamic array variable, and then declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)

intFile = FreeFile

'Loop through all the files in the directory by using Dir$ function

MyFile = Dir$("c:\backupdb\*.*")

Do While MyFile <> ""

DirectoryListArray(Counter) = MyFile
MyFile = Dir$
Fname = "C:\backupDB\" & MyFile

'MsgBox MyFile

Open Fname For Input As intFile
lngChars = LOF(intFile)
strImport = Input(lngChars, intFile)

With rst
.AddNew
rst!LogDetails = strImport
.Update
End With

Counter = Counter + 1

intFile = FreeFile

Loop

sExit:
On Error Resume Next
Reset

Exit Sub
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit

End Sub

Can anyone advise what is going wrong?

Cheers,
GLT
 
Ad

Advertisements

T

Tom Wickerath

Hi GLT,

Try this version of your code:

Sub sImportAll()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("tbl_LogFileDetails")

On Error GoTo E_Handle
Dim strImport As String
Dim lngChars As Long
Dim intFile As Integer
Dim MyFile As String
Dim Fname As String
Dim Counter As Long

'Create a dynamic array variable, and then declare its initial size
' Dim DirectoryListArray() As String
' ReDim DirectoryListArray(1000)

'Create an array variable (no need to use a two-step process)
Dim DirectoryListArray(1000) As String

intFile = FreeFile

'Loop through all the files in the directory by using Dir$ function

MyFile = Dir$("c:\temp\*.*")
'MyFile = Dir$("c:\backupdb\*.*")

Do While Len(MyFile) > 0
'Do While MyFile <> ""

DirectoryListArray(Counter) = MyFile
'MyFile = Dir$ <---Move this statement just prior to Loop
Fname = "C:\temp\" & MyFile
'Fname = "C:\backupDB\" & MyFile

'MsgBox MyFile

Open Fname For Input As intFile
lngChars = LOF(intFile)
strImport = Input(lngChars, intFile)

With rst
.AddNew
rst!LogDetails = strImport
.Update
End With

Counter = Counter + 1

intFile = FreeFile
MyFile = Dir$

Loop

sExit:
'Cleanup
If Not rst Is Nothing = True Then
rst.Close: Set rst = Nothing
End If
Set db = Nothing
Exit Sub
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit

End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
P

Pete D.

Change your filter for directory from *.* to *.cvs or *.txt what ever. *.*
returns two addtional files you can't read, . and ..
 
T

Tom Wickerath

A

Afrosheen

Hi there,
Just wondering if this routine will work on other importing. I have a DB
where I'll need to create an export from 6 different units into one unit.
Which means I'll have to export 6 and then import 6 in. The people are going
to send these to one central place on GroupWise then on the import side will
have to pull them and import them They are not going to want to "Press this
then do this then save it as and so on and so on." I want to do it all
through VBA and I was wondering if this would work. I know I'll have to
change some names.

I'm trying to make it as easy and painless as possible.

Thanks
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top