SQL Database Access Using FoxPro Database, Visual Basic Frontend and ADO

I learned about databases today and how to work with them. It took me a long time to run through different code to figure out how to open, view and work with an existing database. For my purposes, I was working with a *.dbf database file, created in FoxPro. I am using visual basic to access this database. The following code shows how you can connect to a database, send an SQL command to read all the fields within the database, and then two different ways to print those fields elsewhere.

Sub Whatever()
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
con.ConnectionString = "Provider=vfpoledb;" & _
           "Data Source=C:\ab\dname.dbf;" & _
           "Mode=Read|Share Deny None;" & _
           "Password=''"

con.Open

  With cmd
    .ActiveConnection = con
    .CommandText = "SELECT * FROM name;"
    .CommandType = adCmdText
  End With

  With rst
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd
  End With

ActiveDocument.ActiveWindow.Selection.InsertAfter _
Trim(rst.GetString(2, 1, ",", Chr(13), ""))

For Each x In rst.Fields
ActiveDocument.ActiveWindow.Selection.InsertAfter _
Trim(x.Name) & "=" & Trim(x.Value)
Next


con.Close
Set con = Nothing
Set cmd = Nothing
Set rst = Nothing
End Sub

Now, this code was created with the displaying of only one row of information in mind. I wrote it with a more complex SQL command which should only return one row of information (searching for a row by number). This code will then write out all the information in that row. The first command has many options, especially when taking the data from your sql command result and formatting it into its own table. It has options for column delimiters, row delimiters, how many rows to read, where to start, what to do with null characters, etc. It does not, however, deal well with extra spaces. The second command, however, handles the extra characters well with a Trim() command. It simply asks how many items are in our results list and then loops through each one, printing out the name and the value for that particular field.

Important things to know: The Connection String is going to be the one thing that EVERYONE has to customize. It contains information about the type of database you are connecting to as well as the filename of that database. For example, my "provider" is foxpro (which, by the way, requires the fox pro ole db to be able to access it), my location is C:\ab\name.dbf, and I've set other parameters (the ability to read/write to the DB, sharing of the file, etc.). The other important and customizable line is the command you will send. Here I've used SELECT * FROM name; - this means I'm selecting all (*) from the table "name" which happens to be in my database file, name.dbf. Your table name and .dbf file wont always be the same, and there may be more than one table in each .dbf file. Finally, I just used ActiveDocument.ActiveWindow.Selection.InsertAfter so i could print out the results to the open MSWord window, as this is all being written in VBA.