PDA

View Full Version : Searching contents of a Visual Foxpro Database from Excel



rangudu_2008
10-16-2011, 11:38 PM
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 (http://www.vbaexpress.com/forum/member.php?u=236) (Thanks Tommy!! :friends:) wherein i can search using SQL queries.

Querying workbook with SQL (http://www.vbaexpress.com/kb/getarticle.php?kb_id=88)

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
10-17-2011, 09:21 AM
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/

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

rangudu_2008
10-25-2011, 08:01 PM
Hi Ken,

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

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


Regards
Sarang

rangudu_2008
10-26-2011, 05:49 AM
Hi Ken,

I've one more question:


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

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
10-26-2011, 08:26 AM
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
10-27-2011, 06:23 PM
Hi Ken,

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



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




Regards

Sarang

Kenneth Hobs
10-27-2011, 06:43 PM
Try some SQL strings like that from: http://msdn.microsoft.com/en-us/library/aa976856%28v=vs.71%29.aspx