3

I have searched here and Google, with no solution so far.

The problem is Access 2013 no longer wants to connect to DBF.

Each month, I will get 33 DBF files (each with a CDX file) that I have to import into MySQL. The front end for this is Access 2013.
I've built a few other applications that do similar functions, so I'll just tweak one of them for this new data.

I don't know the the actual source of the original data, but it's my understanding that dbase & visual foxpro use the same format.

The application is designed to link to the data files, where I'll run queries (and maybe VBA as well) to port the data into tables linked to MySQL. All tables are linked using VBA and a DSN-Less connection.

I can use VFPOleDb, or something else if it gets me what I want. I've actually already installed this, but can't figure out the code to do what I need.

I'm running 64-bit Access & Windows 7.

Thanks.

Tom Collins
  • 4,069
  • 2
  • 20
  • 36
  • How are you doing this? Any error messages? Just an idea http://stackoverflow.com/a/11233037/2548 – Fionnuala Aug 11 '14 at 22:33
  • I wasn't getting error msgs because I don't know the code to link to OLE. I tried that link, but got 'Could not find installable ISAM'. Remember, this is Access 2013, with no native support to .dbf files. – Tom Collins Aug 11 '14 at 22:52
  • 1
    [Enabling xbase/DBF support in Microsoft Office/Access 2013](http://jonwilliams.org/wordpress/2014/03/21/enabling-xbase-dbf-support-in-microsoft-office-access-2013/) – Irawan Soetomo May 26 '15 at 02:44

5 Answers5

0

Can you launch a VB program to convert the files to xlsx. A friend of mine had the same situation and we finally found this on a forum (don't remember which one, I just had this code copied into a word doc I sent her.)

Function dbf2xlsx(dbffile)
Dim xlapp, objWorkbook As Object
Dim SaveFile As String

SaveFile = Left(dbffile, Len(dbffile) - 3) & "xlsx"

Set xlapp = CreateObject("EXCEL.application")
Set objWorkbook = xlapp.workbooks.Open(dbffile)
xlapp.Application.Visible = False
xlapp.Application.DisplayAlerts = False
xlapp.ActiveWorkbook.SaveAs SaveFile
xlapp.ActiveWorkbook.Close

End Function

Now she only had 1 file to import so I don't know how effective or efficient this may be.

Hank Wilson
  • 509
  • 12
  • 31
0

I ended up using an external program called DBF to MySQL by Intelligent Converters. Except for the fact that it doesn't convert indexes, it does a great job. It has a command line interface, so I used Access to offer the options, build the command string, then execute it.

In retrospect, I think I would have rather read the raw data files and pulled the data that way instead.

Tom Collins
  • 4,069
  • 2
  • 20
  • 36
0

For anyone with this issue you can link to a DBF table using code similar to below, (note that error checking etc ought to be included) AND obviously change the hard coded names to whatever is required

Dim tdf As TableDef
Set tdf = CurrentDb.CreateTableDef("NameYouWant")
tdf.Connect = "Dbase III;Database=c:\test\dbase\"
tdf.SourceTableName = "example.dbf"
CurrentDb.TableDefs.Append tdf
hallhmik
  • 1
0

Using access as a frontend doesn't seem to be a good idea.

Anyway, if you want to import VFP data into MySQL, then it could be done in various ways. Unfortunately VBA with 64 bits is not one of them (doable with drivers from say Sybase ADS but not worth the effort IMHO).

You can use VFP itself or C#, or any other language like Go, Python ...

With VFP itself, you would create a cursorAdapter or remote view or SPT cursor to your MySQL, and then push the data into that cursor from VFP table and commit. It is a simple process.

With C#, you would open connection to both MySQL and VFP (using VFPOLEDB) and then pump data from one to other (same with other languages as well).

AFAIK, access doesn't have an option to get data from OLEDB sources directly (Excel does), if your office were 32 bits, you could use that.

(If it were MS SQL Server, then you could use BulkCopy class in .Net)

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
0

Access 2013 dropped support for DBF files. It shows error Could not find installable ISAM when trying to open a linked DBF table. But you can install Access 2010 component that supports DBF files and keep using them in Access 2013.

First, download and install Microsoft Access Database Engine 2010 Redistributable (free, contains 32bit and 64bit versions). This will install (among other things) the required ACEXBE.DLL library to access dBase files directly. But this is not enough, since this installs it under OFFICE14 (2010), not for OFFICE15 (2013).

Now you need to copy 4 registry keys from Office 14 to Office 15. This registry patch will do it for you, but it’s highly recommended that you understand what it’s doing. This one is for 64bit Office:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Xbase]
"win32"="C:\\PROGRA~1\\COMMON~1\\MICROS~1\\OFFICE14\\ACEXBE.DLL"
"DbcsStr"=hex:01
"Mark"=dword:00000000
"Date"="MDY"
"Exact"=hex:00
"Deleted"=hex:01
"Century"=hex:00
"CollatingSequence"="Ascii"
"DataCodePage"="OEM"
"NetworkAccess"=hex:01
"PageTimeout"=dword:00000258

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE III]
"Engine"="Xbase"
"ExportFilter"="dBASE III (*.dbf)"
"ImportFilter"="dBASE III (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE IV]
"Engine"="Xbase"
"ExportFilter"="dBASE IV (*.dbf)"
"ImportFilter"="dBASE IV (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE 5.0]
"Engine"="Xbase"
"ExportFilter"="dBASE 5 (*.dbf)"
"ImportFilter"="dBASE 5 (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:00

For 32bit Office 2013 running on 64bit Windows it’s slightly different:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Xbase]
"win32"="C:\\PROGRA~2\\COMMON~1\\MICROS~1\\OFFICE14\\ACEXBE.DLL"
"DbcsStr"=hex:01
"Mark"=dword:00000000
"Date"="MDY"
"Exact"=hex:00
"Deleted"=hex:01
"Century"=hex:00
"CollatingSequence"="Ascii"
"DataCodePage"="OEM"
"NetworkAccess"=hex:01
"PageTimeout"=dword:00000258

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE III]
"Engine"="Xbase"
"ExportFilter"="dBASE III (*.dbf)"
"ImportFilter"="dBASE III (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE IV]
"Engine"="Xbase"
"ExportFilter"="dBASE IV (*.dbf)"
"ImportFilter"="dBASE IV (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE 5.0]
"Engine"="Xbase"
"ExportFilter"="dBASE 5 (*.dbf)"
"ImportFilter"="dBASE 5 (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:00

Note that win32 path may be different on your system due to 8dot3 names assignments.

After this registry patch, your database with linked DBF files will work like in previous Access versions.

Chungalin
  • 420
  • 4
  • 5