guru needed -> problem connecting to .dbf using ADODB

On Wed, 27 Oct 2010 07:08:00 -0700 (PDT), jcandamo
wrote:

>Hi, I been looking for answers for this problem and have not been very
>lucky. I'm trying to connect to a .dbf file from VB6 (Free table
>directory, for the sample below I have a dbfFileName.dbf file inside
>the folder App.Path & "\databases"). I installed the Microsoft driver
>VFPOLEDBSetup.msi to install Microsoft OLE DB Provider for Visual
>FoxPro. The code below is crashing out in the line cnn.Open. The error
>is "Function argument value, type, or count is invalid." Any help
>would be greatly appreciated, first time connecting to dbf and not a
>lot of documentation in the error I'm getting. I hope some guru can
>give me something to go on
>
> Dim sFolder As String: sFolder = App.Path & "\databases"
> Dim sTable_ As String: sTable_ = "dbfFileName"
>
> Dim cnn As ADODB.Connection: Set cnn = New ADODB.Connection
> Dim rst As ADODB.Recordset: Set rst = New ADODB.Recordset
>
> cnn.Provider = "VFPOLEDB.1"
> cnn.Properties("Data Source") = sFolder
>*************
> cnn.Open
>*************
> rst.Open "SELECT * FROM " & sTable_, cnn
> If Not rst.EOF Then
> MsgBox rst.Fields(0)
> End If
>
> rst.Close: Set rst = Nothing
> cnn.Close: Set cnn = Nothing

I'm not a guru, but would like to make the following suggestions:

Instead of setting specific properties and attributes for the ADODB
Connection object, use a Connection String instead.

You can find sample connection strings for about any configuration of
databases and development platforms online. This is a good site:
http://www.connectionstrings.com/dbf-foxpro
Unfortunately they don't show one for the Foxpro provider.

In your case it would be something like this ...
cnn.ConnectionString = "Provider=VFPOLEDB;Datasource=" &
App.Path & "\databases\dbfFileName"

This makes everything a tad easier to view. But the real advantage in
using the Data Link Dialog to create and test custom OLE DB
connections. Once you learn how to use the dialog you become your own
ADO Connection Guru and can apply it to any future combinations you
run across.

1. Create and save a blank/empty file with a ".udl" extension.

2. Launch the file by double-clicking in explorer or typing the name
in run.

3. A very handy 'Data Link Properties' dialog will open up that can
help with a number of development issues...
* Note the 'Provider' tab. If a Provider isn't listed it means it
isn't installed or properly registered.
* The 'Connection' tab will change depending on what Provider is
selected. Saves a lot of guess work for what properties are available
for different providers.
* Note the [Test] button. This allows you to immediate test to see if
your configuration will work.

4. When done setting properties/attributes click OK and save the
information to the .UDL file.

Now you can pass the file directly in your program, but you can also
open the .UDL file in notepad and oui la - there's your connection
string ready to copy 'n paste.

If you still prefer to set them individually. Just read the values out
of the string.

hth
-ralph
 >> Stay informed about: guru needed -> problem connecting to .dbf using ADODB