Excel VBA & ADO - retreiving data from Approach dBase IV .dbf files
 

Hi folks,
I'm trying to send persuade Excel to send queries (SQL) to an approac
dBase IV (.dbf) file.

I've got these connection methods (courtesy of one of the tips here
which allows me to connect to another .xls file in the same way, but m
attempts to modify it so that I can query the dbf have so far bee
unsuccessful. What's the equivalent code to achieve what I'm trying t
do?

Sub openCN(cn As ADODB.Connection)
' open connection and leave open
Set cn = New ADODB.Connection
cn.Open "DRIVER={Microsoft Excel Drive
(*.xls)};DriverId=790;ReadOnly=True;" & _
"DBQ=" & datafile & ";" ' DriverId=790: Excel 97/2000
End Sub

Sub openRS(strSQL As String, cn As ADODB.Connection, rs A
ADODB.Recordset)
'open recordset and leave cn,rs open

On Error GoTo errhand
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
Exit Sub

'error handling
errhand:
MsgBox "Error in openRS." & vbCr & vbCr & _
"SQL:" & strSQL
End Su

--
Message posted from http://www.ExcelForum.com

Excel VBA & ADO - retreiving data from Approach dBase IV .dbf files
 

You just need a different connection string

Sub openCN(cn As ADODB.Connection)
' open connection and leave open
Set cn = New ADODB.Connection
cn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=c:\somepath"

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"EddyT " wrote in message
...
Hi folks,
I'm trying to send persuade Excel to send queries (SQL) to an approach
dBase IV (.dbf) file.

I've got these connection methods (courtesy of one of the tips here)
which allows me to connect to another .xls file in the same way, but my
attempts to modify it so that I can query the dbf have so far been
unsuccessful. What's the equivalent code to achieve what I'm trying to
do?

Sub openCN(cn As ADODB.Connection)
' open connection and leave open
Set cn = New ADODB.Connection
cn.Open "DRIVER={Microsoft Excel Driver
(*.xls)};DriverId=790;ReadOnly=True;" & _
"DBQ=" & datafile & ";" ' DriverId=790: Excel 97/2000
End Sub

Sub openRS(strSQL As String, cn As ADODB.Connection, rs As
ADODB.Recordset)
'open recordset and leave cn,rs open

On Error GoTo errhand
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
Exit Sub

'error handling
errhand:
MsgBox "Error in openRS." & vbCr & vbCr & _
"SQL:" & strSQL
End Sub


---
Message posted from http://www.ExcelForum.com/