Importing a dBase III file with code

[MikeAngelastro]

Hi,
I'm trying to automate the import of a *.dbf file. However, it is not part of a database. Using "DoCmd.TransferDatabase", I get an error saying that the path is invalid. I believe that this is due to having only a
table file and not a whole database. What code could I use to import the table/file?
Thanks,
Mike

 

[Pat Hartman]

Did you specify the complete path including the filename.dbf? If you did then you have a different problem. Reread the help file entry for the TransferDatabase Method. It tells you how to find out the valid database types. It doesn't list them here because the valid types depend on what ISAM drivers you chose to install when you installed Access.

Open a new macro and select the transferdatabase action. In the properties section, you will get a list of the available database types. If the one you need is not in this list, you will need to go back to the Access installation CD and install it.

 

[MikeAngelastro]

Pat,

Thanks for your help. I had already done as you had suggested. I started a macro just to find out the exact database type to use. My code is below:

DoCmd.TransferDatabase acImport, "dBase III", "C:\Entel\Usgjan00.dbf", acTable, "C:\Entel\Usgjan00.dbf", "USG"

This is one file, not a full database. It does work manually through the menus. Do you think that perhaps, because each dBase table is in a separate file, that Access is looking for a default directory structure? ie, a folder for tables, a folder for queries, etc. Is so would you or anyone else know what that is?

Thanks,
Mike

 

[Pat Hartman]

Access knows that each dBase table is a stand alone file.

I haven't had to do this in code. The only time I needed to import dBase data, I imported the data through the file/get external data option since I only needed to import it once to convert it.

I would guess that you have a syntax problem. Try eliminating the non-required parameters.

DoCmd.TransferDatabase acImport, "dBase III", "C:\Entel\Usgjan00.dbf"

 

[MikeAngelastro]

Pat,

Thanks again. I figured it out. It turns out that, for dBase, the name of the database is the path to the folder containing the dBase file and the tablename is the name of the file excluding the path. Below is the code that finally worked. I ended up doing a search in MS Access Knowledge Base for "dBase" and found an article that allowed me to solve the riddle with a little deduction.

DoCmd.TransferDatabase acImport, "dBase III", "C:\Entel", acTable, "Usgjan00.dbf", "USG"

So if anyone needs to import dBase file with code, this is the way to do it.

Thanks again,

Mike

 

[Pat Hartman]

Thanks for posting the solution Mike. This would have been a very simple task if only the help system offered an example of using the transferDatabase Method with this type of database or was a little clearer on what went in each parameter.

 

[Danielf]

I have tried to do the same thing but
when I am importing with code, I am receiving
the error message :"dbase5 type isn't an installed database type or doesn't support the operation you chose".
I have a full installation of access 2000 and
with the menu command: Get external data, it
is well working, I can import dbf files.
Any help?

Daniel

 

[MikeAngelastro]

Daniel,
Are you trying to do the import with code or with the menu? Are you trying to import a dBase V file as opposed to a dBase III file? I was importing a dBase III file. I don't know if it makes a difference.

By the way I was not using Access 2000 in the previous exercise; I was using Access 97. When I have an opportunity in the next day or two, however, I will try it with Access 2000.
Mike

 

[Danielf]

Mike,
I was trying to import a *.dbf table.
With the menu, I can import it using dbase III, IV or 5.
With the code , I am receiving the error
message.

I have tried with dbase III , IV, V

code: DoCmd.TransferDatabase acImport, "dBase III", "c:\Path", acTable, "*.dbf", "name of table"

 

[MikeAngelastro]

Daniel,

I converted the previous referenced Access 97 database to the Access 2000. The dBase file import via code worked as in Access 97. Check your database references. Give me a list of the ones you have and I will compare it to the references in my app. Perhaps we can find a reference to add that will get you going.

Mike

 

[Danielf]

Mike,

I have as databases references :
-Vba
-Ms Access Object library
-OLE for automation
-DAO
Ms Windows Common controls

Daniel

 

[MikeAngelastro]

Daniel,

My references are:

VBA
MS Access 9.0 Object Library
MS DAO 3.6 Object Library
OLE Automation
MS VBA Extensibility 5.3

Mike

 

[Danielf]

Hi Mike,

I have checked MS VBA Extensibility 5.3

but it still doesn't work

Daniel

 

[MikeAngelastro]

Daniel,

I think I have the solution. Reinstall Access 2000 in the custom mode and make sure you enable all the features you need. I believe that there is a data conversion feature in the feature tree where you select the database types Access can handle. Try this and let me know.

Mike

 

[Danielf]

Hi Mike,

I have office 2000 and I have a full
installation of access 2000.
When you are installing access , there is no feature about converting Dbase files.

It still doesn't work.

Daniel

 

[MikeAngelastro]

Daniel,

I don't know what else to suggest. I'm sorry.

Does anyone else have any ideas?

Mike

 

[TomK]

For Access 2000 users, you must have Borlands BDE or use the MS DAO 3.6 Object Library to access dBase files. A97 uses DAO. By default, A2K uses ADO and does not reference DAO. The ADO library requires the BDE. There are several MS aticles in KB about this. Search under Access 2000 for dBase.

 

[MikeAngelastro]

Tomk,

Thanks. I'll check it out. It makes sense about the ADO/DAO aspect of it.

Mike