Multiple dbf import using files same name different directories 1

[MKH2001]

Long winded subject title I know, but was hoping would attract someones attention that might think hey I know how to do that was trying just the same thing recently.

Anyway I have a back up folder of files including relevant .dbf files.

These files are ALWAYS the same filename but reside in seperate directories within the folder.

for example

C:\Documents and Settings\Admin\Desktop\Jim\AV Backups\week 01 back up\AVNDT1_01

and

C:\Documents and Settings\Admin\Desktop\Jim\AV Backups\week 01 back up\AVNDT1_02

and

C:\Documents and Settings\Admin\Desktop\Jim\AV Backups\week 02 back up\AVNDT1_02

C:\Documents and Settings\Admin\Desktop\Jim\AV Backups\week 02 back up\AVNDT2_02

and so on. Usually about 7 folders in each week and about 52 weeks. each "AVNDT" folder has a specific dbf file in it (there are other dbf files) named route.dbf

What I need is to pull each individual "route.dbf" from each of the multiple locations into a single table within access so that I can query the COMPLETE table against another.

Can anyone think of a way I can do this effectively using on click event in a form efficiently using scripting?

Any help would be a bonus. I am actually trying to dig myself out of a hole. I had a method of individually importing each file seperate at point of "check in" but some how there are large quantities of files either not checked in or not checked in in their entirety so i need to pull all data into one table so that I might ascertain what has gone wrong and whether I can identify any pattern so as to alleviate issue.

Ta in advance for any contribution you might be able to make to the cause =)

 

[PHV]

Unless you use ac2007 you may consider the Application.FileSearch object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MKH2001]

Have been looking at the filesearch you mention but have failed to put the results into any sort of import.

What I do get is a rather useful listbox with results in.... but the list box appear to not allow the quantity of dbfs I am looking to import into one table.

So far I have the following:

Private Sub cmdImport_Click()
On Error GoTo ErrHandler
Set fs = Application.FileSearch
With fs
.NewSearch
.SearchSubFolders = True
.MatchTextExactly = True
.LookIn = "C:\Documents and Settings\mharris\Desktop\Jim\" & [cbo_InsptypeFolder] & "\"
.FileName = "LIGHTING.dbf"
.Execute
If Len(.FileName) > 0 Then
For b = 1 To .foundfiles.Count
List2.AddItem (.foundfiles(b))
Next
MsgBox b & " dbf files were Found."
End If
End With


ErrHandler:
MsgBox Err.Description
End Sub

The counts and obtains the path strings for ALL LIGHTING.dbf (including $Pack_LIGHTING.dbf's which I was hoping to filter but no biggie) and displays them all in a listbox on the form.

This would be useful if this was able to cope with the quantity of files (I assume that is the reason for the error) and IF I had a means of either selecting all strings (or even better multiselect from the list box) those for import and then import all to one table.

But in this I seem to be at a loss.
Someone mentioned returning the list to a table rather then a list box and looping through each record in this to import.... but again do not seem to be able to get my head around that either.

Any help would be appreciated.

Regard

 

[MKH2001]

Now have the following:

Private Sub cmdList_Click()
    Dim I As Integer
    With FileSearch
        .NewSearch
        .SearchSubFolders = True
        .MatchTextExactly = False
        .LookIn = "C:\Documents and Settings\mharris\Desktop\Jim\" & [cbo_InsptypeFolder] & "\"
        .FileName = "HL*.dbf"
        .Execute
        If .foundfiles.Count > 0 Then
            For I = 1 To .foundfiles.Count
                DoCmd.TransferDatabase acImport, "dBase IV", "tbl_ImportAllDBF", .foundfiles(I)
          Next I
        End If
    End With
ErrHandler:
MsgBox Err.Description
End Sub

But at the docmd.transfer (text or database) I get either a cannot transfer this file (text) or "mismatch" error (database).

Frustrating very close.
But the hounds are baying.

Anyone see what the problem might be?

 

[MKH2001]

Still a work in progress.....
But not much progress made.

Any thoughts?

 

[MKH2001]

Now have

Private Sub cmdImport_Click()
    Dim i As Integer
    Dim SQL As String
     With FileSearch
        .NewSearch
        .SearchSubFolders = True
        .MatchTextExactly = True
        .LookIn = "C:\Documents and Settings\mharris\Desktop\Jim\" & [cbo_InsptypeFolder] & "\"
        .filename = "0*.dbf"
        .Execute
        If .FoundFiles.Count > 0 Then
            For i = 1 To .FoundFiles.Count
            filename = Mid(.FoundFiles(i), InStr(.FoundFiles(i), ".dbf") - 8)
              SQL = "Insert into [tbl_ImportAllDBF]" _
              & " Select """ & (.FoundFiles(i)) & """ as [Key],*" _
               & " from " & Left(filename, InStr(filename, ".dbf") + 3) _
                & " IN " & "C:\Documents and Settings\mharris\Desktop" & """ ""dBASE 5.0;"""
            MsgBox "Insert into [tbl_ImportAllDBF]" _
              & " Select """ & (.FoundFiles(i)) & """ as [Key],*" _
               & " from " & Left(filename, InStr(filename, ".dbf") + 3) _
                & " IN " & "C:\Documents and Settings\mharris\Desktop" & """ ""dBASE 5.0;"""
       MsgBox (SQL)
       DoCmd.RunSQL SQL
  Next i
  End If
  End With
ErrHandler:
MsgBox Err.Description
End Sub
{/Code]

Have batch updated all the files for import so that they are now simply conqequtively numbered .dbf files with padding to ensure consistent file length.

IE 00000000.dbf, 00000001.ddf etc.

The SQL string I am attempting to return looks something like this:

[Code]
Insert Into [tbl_ImportAllDBF] Select "C:\Document and Settings\mharris\Desktop\Jim\AV Back ups\Week 01 back up\AVNDT1_01\00000000.dbf" as [Key],* from 00000000.dbf IN C:\Documents and Settings\mharris\Desktop" "Dbase 5.0;"

I then get a runtime error 3131 Syntax error in From Clause.

 

[PHV]

Your SQL should probably be like this:

INSERT INTO tbl_ImportAllDBF SELECT 'C:\Document and Settings\mharris\Desktop\Jim\AV Back ups\Week 01 back up\AVNDT1_01\00000000.dbf' AS [Key],* FROM [00000000] IN "C:\Documents and Settings\mharris\Desktop" "Dbase 5.0;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MKH2001]

00000000 is a dbf file as opposed to a table or field? Does that effect?

 

[PHV]

I *think* the syntax is:
FROM [finename without extension] IN "\path\to\directory" "dBase 5.0;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MKH2001]

Definitely the answer )

I was so close but glad for the clarification. Taken me ages to get this far.

Now I am getting errors in Field names....(hey least it means it IS reading the dbf file =P) and I am assuming will be because not ALL the files might have the same field name structure (some are from old versions then others) any idea how might overcome that?

I have literally hundred of different files. Each with at least 30 or more fields =(

 

[MKH2001]

All good.

Works fine now.
Ran import and anytime found a field missing from destination table simply added it to destination table and ran again from scratch until no erros. Only had to add like 2 or 3 extra fields and now all works great.

End result code:

Private Sub cmdImport_Click()
    Dim i As Integer
    Dim SQL As String
     With FileSearch
        .NewSearch
        .SearchSubFolders = True
        .MatchTextExactly = True
        .LookIn = "C:\Documents and Settings\mharris\Desktop\Jim\" & [cbo_InsptypeFolder] & "\"
        .filename = "0*.dbf"
        .Execute
        If .FoundFiles.Count > 0 Then
            For i = 1 To .FoundFiles.Count
            filename = Mid(.FoundFiles(i), InStr(.FoundFiles(i), ".dbf") - 8)
              SQL = "Insert into [tbl_ImportAllDBF]" _
              & " Select """ & (.FoundFiles(i)) & """ as [Key],*" _
               & " from " & "[" & Left(filename, InStr(filename, ".dbf") - 1) & "]" _
                & " IN  """ & Left(.FoundFiles(i), InStr(.FoundFiles(i), ".dbf") - 10) & """ ""dBASE 5.0;"""
              DoCmd.RunSQL SQL
  Next i
  End If
  End With
ErrHandler:
MsgBox Err.Description
End Sub

 

[MKH2001]

One small issue.

Loop doesnt end neatly.

Gets to what I have figured must be the LAST file and throws up an error.

Notice that even with no records FoundFile count is 1 and assume this to be the problem.

It detrunks the file name (since presumably there is no file name left in the "IN" clause and throws up an error saying no file found (which there wouldnt be).
It DOES import the last file.
Is there any way i can tidy this up.