Monday, 9 June 2014

Exporting Visual Foxpro Tables to MS SQL Server

VISUAL FOXPRO 9.0 - I find out that the easiest way (for me) to upload VFP free tables into a SQL Server 2005 database is through the Upsizing Wizard provided by Microsoft Access 2010.

When Microsoft still supports VFPODBC, we can do that by setting up a data source pointing to VFP  tables and import them to a  MS Access Database.

But according to MS, they no longer support the VFPODBC and they recommend the use of Visual Foxpro OLE DB driver instead.

Too bad;  but anyway, we can still do something about this. I'll demonstrate a similar technique that does the same. Without VFPODBC, we will rely on Access' built-in dBASE importing option. The downside though is that it will only let us import one VFP table to MS Access Database at a time. If you're in for some boring software stuff, please read on. :)

Procedure

1. Open Microsoft Access 2010 and create a new database. Click on the External Data Tab and choose More then select dBASE file. Locate the VFP table by clicking the Browse button. See Image below.


2. Once the VFP table is imported, you can readily see under the Tables section. Import as many tables as you want by doing the first procedure. When you're done importing VFP tables, make sure that no tables are open in MS Access but leave the Database open.


3. These VFP tables are now converted to MS Access tables inside a MS Access database. In order for the upsize wizard to work, we must make a new Data Source for the SQL Server Database to which our tables must be uploaded.

Open the ODBC Data Source Administrator. For users with Windows XP; click start and select RUN. Type in odbcad32 then press Enter key. Go to User DSN Tab and click Add. Select the SQL Server and fill in the required parameters. In my case I used SQLServer2005 for the Name and GlenV as the Server.

For this test, I used SQL Server express installed in Windows XP on another machine. If your SQL Server has a username and password, select "With SQL Server Authentication using a login ID and password entered by user" and type in the username and password when presented with that option.

Change the default database to the name of your database and proceed with the rest of the creation.





4. Go back to Microsoft Access and click on the Database Tools menu and select SQL Server. Choose use existing database and click Next. A "Select Data Source" window should pop up; click on Machine Data Source tab. In these example, we will only upload one table to SQL Server. If you want to upload multiple table at once, select the tables  by ctrl + left clicking on the tables at the left pane.


Select the new data source that we've just created. In my case it's SQLServer2005. See the image below. Click OK after that. If a SQL Server Login  form pops up, enter your password and click OK.


5. If the connection is successful, the Upsizing Wizard form will show. You can then select the tables that you want to export to SQL Server.


The next section will let you export some table's attribute like Indexes too. If you don't want to export them as well, just keep on clicking next ignoring the rest of the options. Once finished and successful, you will see the Upsizing Wizard Report.

The table that we have uploaded into a SQL Server database will include the records from that table as well.

The table together with the records are added in the database.
This is how I export my VFP tables into a SQL Server Database. It's easy and fast. I hope you may find this helpful.



No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...