Searching contents of a Visual Foxpro Database from Excel

[rangudu_2008]

Hi,

I'm looking to build a VBA module thru which i can search contents of a Visual Foxpro Database (.DBF File) and return the search results to an excel sheet. The foxpro file contains about 7 columns and search is done using simple SQL queries with "=" or "LIKE" operators. The DBF file contains about 1.6 crore records.

The base data actually exists on a SQL Server, however my client (for whom i'm buliding this utility) doesn't have SQL installed in their machines and so deployment would be a problem if i code using VBA forms and ADO / DAO connectivity.

I came across the below KB submission by tommy bak (Thanks Tommy!! ) wherein i can search using SQL queries.

Querying workbook with SQL

The above KB fulfills my requirement, however instead of searching with the excel file, i need to search from the Foxpro database. Can someone guide / teach me how to do it?

Regards
Sarang

[Kenneth Hobs]

Here is how I do it for a Lotus Approach DBF. It is more difficult if you have joined DBF databases.

You will need to set some References: Microsoft ActiveX Data Objects 2.8 Library.

To use ADO, you may want to look at various connection strings. http://connectionstrings.com/

[VBA]Function ContractIDsToArray() As Variant
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim a() As Variant, i As Long, pathHMATracking As String
Dim strConn As String, strSQL As String
Dim v As Variant

pathHMATracking = "U:\Material\Approach\HMATracking"
'pathHMATracking = "e:\HMATracking"


Set conn = New ADODB.Connection
'Good
strConn = "DRIVER={Microsoft dBase Driver (*.dbf)};" & _
"Dbq=" & pathHMATracking & ";" & _
"Password=" & "ken" & ";"
conn.Open strConn
'Debug.Print conn.ConnectionString

Set rst = New ADODB.Recordset
rst.Open "Select ContractID From HMATracking.dbf Where ContractID>0 Order by ContractID", _
conn, adOpenStatic, adLockReadOnly, adCmdText
'Debug.Print rst.RecordCount '1361 records not null

ReDim a(1 To rst.RecordCount)
'Another method but a() is 2 dimensional.
'a() = WorksheetFunction.Transpose(rst.GetRows)
i = 0
Do Until rst.EOF
'Debug.Print rst.Fields("ContractID").Value
i = i + 1
a(i) = Format(rst.Fields("ContractID").value, "000000")
rst.MoveNext
Loop


rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
ContractIDsToArray = a()

'For Each v In a()
' MsgBox v
'Next v
End Function[/VBA]

[rangudu_2008]

Hi Ken,

How can we go about joining DBF database files through queries? Can you guide me more on this?

[vba] Set rst = New ADODB.Recordset
rst.Open "Select ContractID From HMATracking.dbf Where ContractID>0 Order by ContractID", _
conn, adOpenStatic, adLockReadOnly, adCmdText
[/vba]

Regards
Sarang

[rangudu_2008]

Hi Ken,

I've one more question:

[VBA]Set rst = New ADODB.Recordset
rst.Open "Select ContractID From HMATracking.dbf Where ContractID>0 Order by ContractID", _
conn, adOpenStatic, adLockReadOnly, adCmdText [/VBA]

In the above piece of code, is it possible to execute join queries which uses more than one DBF database files through queries? I would be happy if it works..


Regards
Sarang

[Kenneth Hobs]

Yes, if the other database does not have a password and it is in the same folder as the other. It is just a matter of getting the right string for SQL.

[rangudu_2008]

Hi Ken,

Can you give me a sample piece of code, the command - particularly, the RecordSet.Open line?


[vba]
Set rst = New ADODB.Recordset
rst.Open "Select ContractID From HMATracking.dbf Where ContractID>0 Order by ContractID", conn, adOpenStatic, adLockReadOnly, adCmdText
[/vba]



Regards

Sarang

[Kenneth Hobs]

Try some SQL strings like that from: http://msdn.microsoft.com/en-us/libr...=vs.71%29.aspx