digitech8694
asked on
DBF tables into MS Access
I am trying to import a bunch of DBF files into Access tables (well ultimately SQL tables). I understand all the issues with the docmd.transferdatabase command, and that would suit me fine. The problem is that when I run that command my Access database just closes. No error, no feedback. I have tried it with MS Access 2000 and MS Access 2003. I have tried using a variety of the DBF files (all of which open fine in Excel btw). According to the header record, they are dBase III format. I cannot even use the manual "import table" functionality (same result, access closes with no feedback).
ASKER
I tried to post one of the DBF files, but the website blocked it (even after putting it in a zipfile). Please advise. There is really no code (although i tried using the docmd.transferdatabase command). I just try to use the import (or link) table button, and choose the file. It crashes after asking for the index file which does not exist.
J
J
Note the following instructions from Access Help for the TransferDatabase action arguments:
Database Name The name of the database to import from, export to, or link to. Include the full path. This is a required argument. For types of databases that use separate files for each table, such as FoxPro, Paradox, and dBASE, enter the directory containing the file. Enter the file name in the Source argument (to import or link) or the Destination argument (to export).
Database Name The name of the database to import from, export to, or link to. Include the full path. This is a required argument. For types of databases that use separate files for each table, such as FoxPro, Paradox, and dBASE, enter the directory containing the file. Enter the file name in the Source argument (to import or link) or the Destination argument (to export).
ASKER
I understand the arguments for the transferdatabase method. When that command runs, it crashes the database. Just as it does when I manually try to import the DBF files, or if I manually try to link the DBF files using the "New" button, then picking either import or link, then picking any of the DBF format choices, then navigate and choosing any of the DBF files. I have imported many different formats including various DBF files (both manually, and using code), and none have every behaved in this fashion (i would gladly attach a file, but it will not allow... i zipped it, then zipped that and it still checks inside the nested zip, finds the DBF file and rejects it). If anyone wants to email me at jason_rezac@hotmail.com I will send them the file...
ASKER
Oh, the files do abide by the 8.3 name scheme, as does the directory that they reside...
I am just trying to manually import (or link) the files. If that would work, I am pretty certain my automated code would work fine, as both crash in exactly the same manner.
I am just trying to manually import (or link) the files. If that would work, I am pretty certain my automated code would work fine, as both crash in exactly the same manner.
Do you have Norton Antivirus loaded on your machine? If so, try disabling NAV before doing the import. I have heard of several cases where the antivirus software has interfered with imported data.
Also, Access 2002 or 2003 was the first version of Access that implemented the "sandbox" security mode as the default operating mode to prevent macro code and certain expressions, considered to be unsafe expressions, from harming your computer. If disabling the Norton Antivirus program doesn't help or doesn't apply to your situation, then try setting the macro security level in Access to "Low" and try to import the dbf files.
Let me know what happens.
Also, Access 2002 or 2003 was the first version of Access that implemented the "sandbox" security mode as the default operating mode to prevent macro code and certain expressions, considered to be unsafe expressions, from harming your computer. If disabling the Norton Antivirus program doesn't help or doesn't apply to your situation, then try setting the macro security level in Access to "Low" and try to import the dbf files.
Let me know what happens.
ASKER
I do have NAV corporate edition.. i tried disabling that and it still crashed. I do not have access 2003 on this PC, just access 2000, so do not have the security settings you referred to. I wish it would just give me some feedback so I could look somewhere in particular.
Maybe the info shown below could be the answer. This was excerpted from this link.
http://office.microsoft.com/en-us/access/HP051876101033.aspx
Microsoft has created dBASE ISAM drivers for Microsoft Jet 4.0 that do not require the installation of the Borland Database Engine (BDE) to provide full read/write access to dBASE files. The default Jet 4.0-based dBase ISAM drivers that ship with Microsoft Data Access Component (MDAC) 2.1 and later, allow read-only access to dBASE files unless the BDE is installed. To obtain these ISAM drivers, you must contact Microsoft Technical Support and request that they be sent to you.
http://office.microsoft.com/en-us/access/HP051876101033.aspx
Microsoft has created dBASE ISAM drivers for Microsoft Jet 4.0 that do not require the installation of the Borland Database Engine (BDE) to provide full read/write access to dBASE files. The default Jet 4.0-based dBase ISAM drivers that ship with Microsoft Data Access Component (MDAC) 2.1 and later, allow read-only access to dBASE files unless the BDE is installed. To obtain these ISAM drivers, you must contact Microsoft Technical Support and request that they be sent to you.
ASKER
I will try that route. As I only needed read only, I expected the default jet drivers to suffice. I will post if that solves the problem.
ASKER
No luck with the new dBASE drivers....
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Not sure if that will solve it. I cannot identify the .inf file in question. There are no .inf files in the directory with the access database, nor are there any in the directory with the DBF files, nor are there any with a name similar to any of the DBF files, nor any that are obviously a DBASE related inf file.
J
J
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
It seems Access can only recognize the DOS file name of 8.3 for dBASE files. It failed to import when the file names are longer than 8 characters.
If the above is not the problem,you probably have a syntax error of some kind, and you will have to post your code in order for an expert to help you.