Sql SErver 2012 - Import .dbf files and Access 2007 files
Windows Server 2012 R2
SQL server 2012
We are moving our data systems from SQL 2008 to 2012. In Sql 2008 (on Windows 2008) I import:
- .dbf IV files using the Visual FoxPro driver
- Access 2007 files using the Microsoft Office 12.0 Access Database OLE DB Provider
Testing on the new Sql Server 2012 I do not see either of these 2 drivers in the Data Source list (Import wizard).
On our Windows 2012 R2 server, I downloaded the Visual FoxPro driver and installed it. I can now see it on the list, but as soon as I choose "Properties", the wizard crashes and asks to be closed
For Access, I downloaded and installed the AccessDatabaseEngine driver for both 2007 and 2010 (32 bit). The install seemed to go fine and I see both in "Add Remove programs", but I DO NOT see it in the SQL Server Data Source list (Import wizard).
HELP! As I understand, if I can get the "Microsoft Office 12.0 Access Database OLE DB Provider" to show properly, I might be able to import both my Access 2007 tables AND my dBase 4 files. Why can't I see it on my Import wizard Data Source list?
Max
SQL server 2012
We are moving our data systems from SQL 2008 to 2012. In Sql 2008 (on Windows 2008) I import:
- .dbf IV files using the Visual FoxPro driver
- Access 2007 files using the Microsoft Office 12.0 Access Database OLE DB Provider
Testing on the new Sql Server 2012 I do not see either of these 2 drivers in the Data Source list (Import wizard).
On our Windows 2012 R2 server, I downloaded the Visual FoxPro driver and installed it. I can now see it on the list, but as soon as I choose "Properties", the wizard crashes and asks to be closed
For Access, I downloaded and installed the AccessDatabaseEngine driver for both 2007 and 2010 (32 bit). The install seemed to go fine and I see both in "Add Remove programs", but I DO NOT see it in the SQL Server Data Source list (Import wizard).
HELP! As I understand, if I can get the "Microsoft Office 12.0 Access Database OLE DB Provider" to show properly, I might be able to import both my Access 2007 tables AND my dBase 4 files. Why can't I see it on my Import wizard Data Source list?
Max
Qlemo
You need to make sure to use 32bit Import Wizard for importing. If using the SQL Server itself for import, driver bitness has to be the same as SQL Server one.
Crashing Import Wizard is obvious behavior in conjunction with older drivers...
The easiest way for DBFs is to export data from simple FoxPro application to SQL Server. You will create a loop, read DBF row by row, and generate INSERT commands on SQL Server. This is possible on 10 lines of code. Everything you need is Visual FoxPro or its free run-time and SQL Server native client. And basic knowledge of FoxPro programming, of course.
MS Access Data engine (64 bit OLE DB provider) should allow to create Linked Server on SQL Server and then you may query Access data from SQL Server directly. It was also possible for DBFs but 64 bit world is not suitable for FoxPro...
Important prerequisite for drivers visibility is equal bit depth, so 64 bit SQL Server/Import Wizard needs 64 bit drivers. Unfortunately, you cannot install both 32 bit and 64 bit Access drivers on one machine. Or this was valid for Access 2010 at least.
Access 2016 does not support DBFs...
The easiest way for DBFs is to export data from simple FoxPro application to SQL Server. You will create a loop, read DBF row by row, and generate INSERT commands on SQL Server. This is possible on 10 lines of code. Everything you need is Visual FoxPro or its free run-time and SQL Server native client. And basic knowledge of FoxPro programming, of course.
MS Access Data engine (64 bit OLE DB provider) should allow to create Linked Server on SQL Server and then you may query Access data from SQL Server directly. It was also possible for DBFs but 64 bit world is not suitable for FoxPro...
Important prerequisite for drivers visibility is equal bit depth, so 64 bit SQL Server/Import Wizard needs 64 bit drivers. Unfortunately, you cannot install both 32 bit and 64 bit Access drivers on one machine. Or this was valid for Access 2010 at least.
Access 2016 does not support DBFs...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"the 32bit Visual FoxPro crashes" this is not true because the Import Wizard crashes... and Visual FoxPro works well in this environment.
Anyway, it is good to read SSIS package works for you.
Anyway, it is good to read SSIS package works for you.
ASKER
The advice given was good, but I found an alternate way of getting the files imported before I was able to review the answers