ADO uses OLE DB providers for all connections. You can have a OLE DB provider
specifically for your database or you can use an ODBC driver and use the
Microsoft ‘MSDASQL.1’ OLE DB provider to connect to it.
ADO uses an OLE DB
provider for ODBC connections if all you have is an ODBC driver for your
database. However, some applications have their own OLE DB providers that
allow you to connect more directly to the database. The ADO connection
path is layed out in the image on the left.
There are two ways to do this. You can create an ODBC User DSN or you can just use the Connection String property to set it up.
Create the DSN first. Then you can either use a .udl file or just write the ConnectionString yourself.
Here is an example creating an ODBC User DSN for a MySQL database. Then using a .udl to create the connection string.
Open the ODBC Administrator (dBASE includes a link to the 32bit ODBC Administrator here : ALL PROGRAMS | dBASE Plus 11 | ‘ODBC Administrator (32bit)
Once the ODBC
Administrator is open .. create your DSN.
Click ‘Add’. A dialog similar to the one shown here will open.
Choose your Driver and click ‘Finish’.
A Dialog will
pop up that is specific to the Database. In this case I am connecting
to a MySQLdatabase …
Once your DSN is set up ….
In Windows Explorer,
find the folder you want to use to create the UDL … Right click and choose
‘New’ … ‘Text Document’. Name the new document something with a udl extension
… 
(Say
‘Yes’ when asked if you want to change the name).
Double click
the new .udl and you will see the Microsoft OLE DB Providers dialog. In
this case we will use the ‘Microsoft OLE DB Provider for ODBC Drivers’
…
Click on the
‘Connection’ tab and you will see something like this… 
Since the User Name and Password and other information was provided in the DSN .. we do not need to do it here. Just choose the DSN and click ‘Test Connection’ to test.
Now in dBASE all we need to do is use this udl to create the connection string in dBASE.
In the dBASE Menu go to Properties | Desktop Properties .. to get the Desktop Properties dialog. Go to the ‘Connection Aliases’ tab and you will see somehting like this …
Where it says
‘Alias:’ add a new Connection Alias. Here I will use ‘MySQLTestAlias’
… then click on the yellow pencil to open the Connection String Dialog.
(IMPORTANT NOTE: If you are using a DSN … it cannot be the same name
as the Connection Alias)
Choose ‘Connection
UDL File’ … and click on ‘File…’. Find the .udl file you just created
and add it.
Then click on the ‘Load’ button and you will see something like this ..
You connection
statement (AKA Connectioln String) is “Provider=MSDASQL.1;Persist Security
Info=False;Data Source=MySQLTest”.
Click OK … You will return to the Connection Alias dialog. Click ‘Add’
to make sure it is added to the list.
(IMPORTANT NOTE: you can bypass the .udl and just put your connection statement here by writing it by hand. If you are using a DSN. Most likely the Connection Statement is always going to be “Provider=MSDASQL.1; Persist Security Info=False; Data Source=<yourDSN>”. You don’t even have to open the ‘Build Connection String’ dialog. You can just put this code, with semi colons separating the elements of the statement, directly in the ‘String’ entryfield under the Connection Aliases tab.)
Now you are ready to use your ADO connection to your database. You can check this by going in to the Navigator … click on the ‘Tables’ tab…. Choose the ADO Connection Alias you just created … and you will see a list of the tables in that database.
Now were going to Just create a connection string using a connection string without having to create a User DSN.
First you need
to know what kind of connection string to build. There are many excellent
sites for this, one of which is http://www.connectionstrings.com/
Here I did a search
for a MySQL 5.1 using ODBC and connecting to a remote server. This is
one of the samples they had…
You can take this string copy it and in the build string dialog of the new Connection Alias
you can copy this string and simply plug in the values for your database.
(IMPORTANT NOTE: You’ll notice here that each element was moved to it’s
own line. This makes viewing and editing the connection string much easier
and it doesn’t make the connection string invalid)
You may have an OLE DB Provider for your database. Again, you can create your own connection string or you can use a udl file. Here is an example using the .udl file to connect to a SQLServer Database using it’s OLE DB provider. Create a new UDL file (see above). Choose the OLE DB Provider..
Under Connection
.. enter the server info to connect to the database …
Test your connection and once you have it set up correctly, you can use
the .udl to create the connection string (and Connection Alias) in dBASE
(See instructions above under “Creating the Connection String … ”
) . If you don’t want to use the .udl dialog you can also create your
own connection string by hand (see the above section on “Bypassing
the DSN and using the Connection String only”)