Importing to Access Records

[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

 

[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
__________________________________________

 

[GLT]

Hi Tom,

Thanks for your assistance - it works great...

Cheers,
GLT.

 

[Pete D.]

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

 

[Tom Wickerath]

You're welcome. Glad I could help.

By the way, if you ever need to find all files of a particular type in a
folder and in subfolders child to this folder, take a look at this free
article in Advisor Magazine, written by Access MVP Doug Steele:

Find Your Data
http://my.advisor.com/articles.nsf/aid/16279


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

 

[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

 

[Tom Wickerath]

Hi Afrosheen,

Just wondering if this routine will work on other importing.

The best way to find out is to give it a try. If you have trouble getting it
to work, then my advice is to create a new thread with your issue, rather
than adding to this thread.


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