Batch Import and Update from DBF

[Lady_Jane]

Hi everyone,


I have hundreds of dBASE IV tables that all have the fields UCID and MEAN.
In my database I have a table with fields UCID, ....., JanMean, FebMean, MarMean etc.
I want to update the access table with all the data from the dBASE tables based on the UCID. Every UCID present in the dBASE tables is already present in the access table.
My current method is to import a handful of the tables into access, select them all using:
SELECT UCID, MEAN FROM DBFJan, UNION SELECT UCID, MEAN FROM DBFFeb etc..
and update the existing table using an update query based on the union query.

But since there are hundreds of dbf tables I would like to be able to automate the whole process, or come at it from a different angle entirely.

Any help is appreciated, thank-you.

[TheShabz]

If your tables are named in some sort of sequential order, you can used the DoCmd.TransferDatabase method inside of a loop that will import them for you.

Lets say you have tblName1 through tblName30 your look can create a variable
strTableName = "tblName" & i
and then you iterate through i from 1 through 30 and run the method for each iteration, incrementing i every time.

http://www.blueclaw-db.com/transferdatabase-docmd.htm

[Lady_Jane]

Hi, thank-you for the reply.

My tables are not in a sequential order as simple as that. They represent temperature data for different areas and are named:
T01_areacode, T01_areacode...x115
T02_areacode, T02_areacode...x115
for 115 different "area codes", where 01, 02 represent January, February etc...
That whole sequence is repeated twice for two different yearly averages.
12x115x2=2760 tables.

e.g.T01_02DD.dbf, T01_02DE.dbf etc...
The "area codes" are alphabetical to a degree, but not in a way that could be simply looped through as far as I can see.

I hope this helps clarify things.

[TheShabz]

For any sort of batch import, you would need some sort of looping mechanism. However, have you tried simply linking to them? The linked table manager has a "select all" feature. While it's not a true import, you can still query off of them and run make-table queries as you see fit.

[Lady_Jane]

I don't understand what the "select all" feature is supposed to do on the linked table manager. All it seems to to is "update" the selected tables, I guess based on the actual tables. But this doesn't really help me. I still can't get around the union query to select all records from 2000+ tables.

Is there not a way to import or link to all tables in a folder, and then add them to the union query?

[TheShabz]

Linking them would only help the import step of the process. It makes it usable in Access. The actual querying, as mentioned earlier, would need some sort of looping system to build the union sql. Even if you can loop through parts of it, you'd be better off. Outside of that, I don't know of a way to get done what you want done.

[Lady_Jane]

I've started writing some code and I'm trying to use the docmd.TransferDatabase method to import the tables.

My code thus far loops through all .dbf files in a folder and uses the file names to create my union query.

But in order for the query to work I need to import the tables at the same time. I am having trouble getting the TransferDatabase syntax right.

Here is what I have so far:

Sub AddClimate()

Dim MyFile As String
MyFile = Dir("F:\Lorna\BroadscaleMonitoring\DerivedData\Climate\1961_1990\OutputTables\T*.dbf")

Dim FileName As String

Dim LoopNum As Integer
LoopNum = 1

Dim Query As String

Dim QryDef As QueryDef
Set QryDef = New QueryDef


Do While MyFile <> ""
    'Get the name of the file without the extension
    FileName = Left(MyFile, 8)
    'Add the file name to the query
    ' If this is the first time through, no "union" is necessary"
    If LoopNum = 1 Then
        Query = "SELECT UCID, MEAN FROM " & FileName
    ' If this is not the first time through, then "union" must be added
    ElseIf LoopNum > 1 Then
        Query = Query & " UNION SELECT UCID, MEAN FROM " & FileName
    End If
    'Import the table
    docmd.TransferDatabase (acImport,"dBase IV",MyFile,acTable,FileName,FileName,False,False)
    
    
    MyFile = Dir()
    LoopNum = LoopNum + 1
Loop

Debug.Print Query

End Sub

[TheShabz]

I believe the first FileName in the arguments should be the name of the table you want to import. As I see it in your code, FileName holds the name of the db.