Automate Import of Multiple .DBF Files.

[Robeen]

Hi!



I've been asked to help importing about 50 - 60 .DBF files into an Access database daily - and processing the data in Access to specs I don't have yet.
I'm pretty comfortable doing whatever once I have the data in the Access Database.

I was just wondering if someone has already worked with importing .DBF files and can give me some pointers. Any 'heads-ups' or caveats also welcome.

I would really appreciate any pointers you can give me.
I don't know if there are any straight VB programmers on this forum - but that is another option for me as well.

First choice - MS Access. After that . . .

Thanks in advance!

Robeen

[June7]

I have imported dbf but it was a one-time event so didn't use code but it should be possible. Would have to set up ODBC.

Heads up: I think dbf support is removed from Access 2013.

[Robeen]

Hi June7!

Could you please give me a little more detail regarding:

Would have to set up ODBC.

Do you mean something to do with an ODBC Driver . . . or are you talking more about setting up an ODBC Connection Object/String [like you do in Visual Studio]?
Is there a web site that you could point me to - or just give me a code snippet . . . or are we talking about something I would do in the Settings . . .
I haven't done anything like that in a while - and never with regard to MS Access. Perhaps you could outline the process for me?

When I last worked full-time using Visual Studio - I would set up a Connection [object/string] to a Database and then use that object in my code.
Though I HAVE worked on a contract once where the Access front end connected to a SQL Server 2008 back end . . . I didn't set the Application up . . . plus the issue in that contract turned out to be all in the Stored Procedures - so I really didn't get much of a chance to see how the connection was set up.

Any tips/pointers/leads you can give me would be appreciated.

Thanks!

Robeen

[June7]

To set links to legacy dbf tables I had to set up ODBC connection with Windows Administrative tool Data Sources (ODBC). Click System and Security on the Control Panel to expand the menu and then select Administrative Tools.

Or set Control Panel to view by small or large icons and will then see Administrative Tools listed.

[ssanfu]

I found this thread about importing dBase files http://www.utteraccess.com/forum/Imp...-t1958387.html

It uses

DoCmd.TransferDatabase acImport, "dBase IV", "D:\", acTable, "Cabsch.dbf", "cab", False

(see Help)
I did a test with an old dbf file and it created a table and imported the data.
Unfortunately, if you try to import another dbf file into the dame access table, a new table is created.
You can import the dbf data as a new table, but you cannot append a dbf file to an existing table.

You would need to write code to move (append) the data to a production table for each dbf file imported.

[Robeen]

I know it's been a while . . .
Does anyone know of a way [VBA . . . VB . . .] that I can read a dBase III table with the intent of only writing certain records into Access?
Thanks!

[June7]

I think you might have to import (or link) the entire table then filter that table and move data into your permanent table.

[Robeen]

Thanks, June. I appreciate your help.
I've already determined that I CAN link and import the dbf files into Access.
The reason I was asking about accessing the data IN the dbf's and then writing it into Access tables is that there is a lot a data in some of the tables and I was wondering if writing code might be quicker.
Thanks again!
I think we can work with the Linked Tables - or with importing.
Have a great weekend!

[June7]

I expect it would be possible to programmatically set links. Link or import is only way I know to get data from dBase tables. Unlike a text file where VBA can open the file as an object and read each line of the file and parse the string.