ACCESSING YOUR VFP DATABASE IN ACCESS
How to create connections to a VFP database
This whitepaper gives you an outline how to connect to a VFP database through ODBC and through OLEDB. ODBC stands for Open DataBase Connection. For this, so called drivers are installed on your computer that you can configure. For those persons working on a corporate network. It might be possible that you can not come to the installation tools you need to configure the driver. Contact your system administrator, he can get you up and running. For both types of connections we preinstalled files already when you did the setup of the application you purchased from Chameleon Software.
About the sample VFP database
The VFP database used in this whitepaper is a truly existing one. It is the timeTracker database internally used by Chameleon Software. In that database we keep track of the time needed for the development of components of the software we develop.
Creating an ODBC Connection
The first type of connection described here is the ODBC Connection. These types of connections are rather easy to create. First of all, through the taskbar, go to the configuration screen. For those using win9x you will find the ODBC installer in the configuration screen directly. Those users running on Win2K or WinXP will find the ODBC installer under administrative tool. Look for the term Data Sources or ODBC.
Adding a driver
Once you found it double-click on it, you will get a window like this:
Figure 1, the ODBC data Source Administrator
With the mouse-pointer click the “ADD” button, you will get a window like this:
Figure 2, Create a new Data Source
Select the Visual FoxPro Driver as shown above and click the finish button. The next window you will get looks like this:
Figure 3, Setting up the Visual FoxPro Driver
In the respective textboxes you can fill in the name of the data source you will recognize, and a description. The Visual FoxPro database (.DBC) option is already selected, this is the default value and we leave it like this. In the “Path” textbox we type the path and the name of the database container we want to connect to. If you do not know this exactly you can use the “Browse” button as well. All the possibilities that would show when you click the “Options>>” button will not be altered, therefore we will not show that here. Click the OK button and you will be returned to the first window, now showing the name of the connection.
Figure 4, The VFP datasource in the ODBC Administrator
With this you created a connection to your VFP database and you can now retrieve data from the tables in the VFP database.
Retrieving data from a VFP database using Access
Due to its’ simplicity in use and intuitive user interface Access is very popular amongst many Office user for creating small databases. Another strong side of Access is its capability to access external data and create queries on that. Those queries can be used to create reports in access using the Access report generator. Here we’ll show you how to:
1: Create a link to your vfp tables from Access.
2: Create queries on the linked tables and,
3: Create a report, based on a query created in step 2.
Create a link to your vfp tables from Access
Start Access and create a new blank database, give it any name you feel appropriate. The database is created and opens. Choose the “Tables tab” and in the white area right-click on your mouse. A context menu pops up, from there choose “LINK TABLES”.
Figure 5, The Access Database
You will see the following window:
Figure 6, Linking an ODBC datasource
Skip the filename, and as “Files of type:” select ODBC Databases (as show above). Click the “Link” button, you’ll get the following window:
Figure 7, Selecting the ODBC Source
Select the “Machine Data Source” tab. There you should see the data source, created in the first step of this manual. Select that and click OK. The “Link Tables” window pops up as shown here:
Figure 8, Selecting table(s) from the datasource
You can select one or more tables there to link to your access database. Select the tables is done by clicking on them ONCE. Clicking on a table-name after you selected it will deselect the table. For the purpose of this sample we will restrict to one table only. Once selected click the OK button. You will return to the Access database. In the database tab you will see this:
Figure 9, The Linked table in Access
The table is now linked to your Access database, you can browse it, alter add or delete data that is actually part of an external database.
Create queries on the linked tables
Start by selecting the Queries tab.
Figure 10, The Queries tab from Access
In the above figure there are two queries already. In the following steps we will show you how to create those queries. Click the new button in the queries tab.
Figure 11, the new button in the queries tab
The following window shows. We select the Design View here and Click OK.
Figure 12, step 1 of creating a new query
The following windows appear.
Figure 13, step 2 of creating a new query
Select the table(s) to add to the query and click add. What is told before goes here as well, you can select multiple tables and then click Add.
Adding fields to the query
In the query designer you can add fields in more ways. The easiest way is to select a fieldname with the mouse from the upper half of the window and drag it to the lower half.
Figure 14, select a field here
Figure 15, drop it in the "field" of the lower half
The second way to fill the designer with fields is to click in the textbox on the field line:
Figure 16, filling fields for the query
Calculating with fields
For creating calculated fields press the “Totals” button on the toolbar of the designer.
Figure 17, The "Totals" button
When doing so you will see a new line in the query designer.
Figure 18, The "total" line in the designer
The “Total” line gives you several possibilities to group, totalize, sum, count et cetera. You simply do this by opening the combobox.
Figure 19, opening the combobox from the "total" line
This way you can Group on one field and sum on another (numeric) field.
With these queries you can now create reports. As you see the power of Access is in its easy to user interface and its flexibility in creating queries on external data. I therefore believe that if you need a good report generator outside of any RDBM access would be the first choice. For multi-user database development there are other great tools like MS Visual FoxPro, a development tool that can access to any database system, either its own, free, databases or a SQL database, ORACLE, MySQL or any other database that has a ODBC or an OLEDB driver available. Visual FoxPro is now in the 8th version available.
ABOUT THE AUTHOR: BOUDEWIJN LUTGERINK
Programming is one of the many hobbies of Boudewijn. He has worked with computers since 1985 and is the author of two books from Sybex. He has a weblog at
@ 6/24/2010 7:21:14 PM
I'm reading your article: "ACCESSING YOUR VFP DATABASE IN ACCESS". Isn't there a way to connect using an ADO connection without having to define a [User DSN] ? I'd like my code to work on various machines without having to configure a DSN for each. Thank you.
@ 10/22/2015 10:18:55 PM
I would like to know if you can help me to migrate my VFP9 database to SQL..
and to connect to it from my VFP9 application.
thank you very much.
Enter the code shown: