Querying and extracting data is relatively easy with the right connection
string.
Here's my function for returning an ADODB.Connection for a DBF (using ADO
and the Jet 4.0 OLEDB Provider):
Public Function GetDBFConnection(ByVal strHubFilename As String, Optional
ByVal strDBFUserID As String, Optional ByVal strDBFPwd As String, Optional
ByVal plngTimeoutSeconds As Long, Optional ByVal penumCursorLocation As
Integer = adUseClient) As ADODB.Connection
Dim connPrivate As ADODB.Connection
Dim strConn As String
Dim strSource As String
Dim plngCount As Long
Dim pstrErrMessage As String
'Create the connection object
Set connPrivate = New ADODB.Connection
Set GetDBFConnection = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strHubFilename & ";Persist Security Info=True;Extended Properties=dBase IV"
connPrivate.ConnectionString = strConn
connPrivate.CursorLocation = penumCursorLocation
If plngTimeoutSeconds > 0 Then connPrivate.ConnectionTimeout =
plngTimeoutSeconds
On Error GoTo Try_Again
Err.Clear
plngCount = 0
Try_Again:
plngCount = plngCount + 1
If plngCount > 5 Then GoTo Error_Occurred
connPrivate.Open
On Error GoTo 0
Set GetDBFConnection = connPrivate
Set connPrivate = Nothing
Exit Function
Error_Occurred:
pstrErrMessage = S_ERR_RSACCESS_CONNECTION & vbCrLf
pstrErrMessage = pstrErrMessage & "Connection String = '" & strConn &
"'" & vbCrLf
pstrErrMessage = pstrErrMessage & "Line " & Erl & " - " &
Err.Description & vbCrLf & "in RSFunctionsADODB.GetDBFConnection." & vbCrLf
Err.Clear
Err.Number = ERR_RSACCESS_CONNECTION
Err.Description = pstrErrMessage
Err.Raise Err.Number
End Function
Just to be clear, the folder that the DBF's reside in is considered the
'database' (or 'catalog') and each DBF file is treated as a table within
that database. So .. in the above .. strHubFilename is actually the folder
that the DBF is within and the SQL statement uses the name of the DBF (no
extension) as the table that it's querying.
Now, I usually get disconnected recordsets and update them by reconnecting
them and calling .UpdateBatch on the recordset. However, I've never
successfully managed to update a DBF (on it's own with no index file etc.)
so I'm not 100% certain how to achieve it. If I need to do that then I
usually convert the DBF to Access (using the pstrh component) and work on it
as a standard Access database. There's obviously an over head to this
though.
This is the function that I generally use to get a recordset for a DBF:
Public Function GetDBFRS(ByVal strQuery As String, ByVal strHubDBFFilename
As String, Optional strDBFUserID As String, Optional ByVal strDBFPwd As
String, Optional ByVal plngTimeoutSeconds As Long, Optional ByVal
penumCursorLocation As Integer = adUseClient) As ADODB.Recordset
Dim privateRS As ADODB.Recordset
Dim privateConn As ADODB.Connection
Dim strSource As String
Dim plngCount As Long
Dim pstrErrMessage As String
'Create the ADODB.Recordset
Set GetDBFRS = New ADODB.Recordset
Set privateRS = New ADODB.Recordset
Set privateConn = New ADODB.Connection
Set privateConn = GetDBFConnection(strHubDBFFilename, strDBFUserID,
strDBFPwd, plngTimeoutSeconds, penumCursorLocation)
If plngTimeoutSeconds > 0 Then privateConn.CommandTimeout =
plngTimeoutSeconds
Set privateRS.ActiveConnection = privateConn
On Error GoTo Try_Again
plngCount = 0
Try_Again:
Err.Clear
plngCount = plngCount + 1
If plngCount > 5 Then GoTo Error_Occurred
DoEvents
privateRS.Open strQuery, privateConn, adOpenStatic,
adLockBatchOptimistic
On Error GoTo 0
If privateConn.CursorLocation = adUseClient Then
Set privateRS.ActiveConnection = Nothing
End If
Set GetDBFRS = privateRS
Set privateRS = Nothing
Set privateConn = Nothing
Exit Function
Error_Occurred:
pstrErrMessage = S_ERR_RSACCESS_RECORDSET & vbCrLf
pstrErrMessage = pstrErrMessage & "Connection String = '" &
privateConn.ConnectionString & "'" & vbCrLf
pstrErrMessage = pstrErrMessage & "SQL Statement = '" & strQuery & "'" &
vbCrLf
pstrErrMessage = pstrErrMessage & "Line " & Erl & " - " &
Err.Description & vbCrLf & "in RSFunctionsADODB.GetDBFRS." & vbCrLf
Err.Clear
Err.Number = ERR_RSACCESS_RECORDSET
Err.Description = pstrErrMessage
Err.Raise Err.Number
End Function
Notice that it calls the first function to get the connection and then drops
the connection immediately afterward - this is the beauty of disconnected
recordsets - they do not consume database connection resources unless
actually doing something and they can also be persisted to disk or sent to a
remote application. There is enough infromation inside the recordset to
allow it to function 'disconnected' from the database.
It shouldn't be too hard to implement this in VBScript - just drop the
variable types and make sure that the constants are correct.
Hope this helps (a bit).
NB: If you want to see the entire DLL that contains this function (and those
for Access and SQL Server) then download it from:
http:\\ftp.belper.blue-canoe.net\RSAccess\RSAccess.zip
Cheers,
Chris.
"viviane lima - * - * -" <vi*********@ligbr.com.br> wrote in message
news:ed**************@TK2MSFTNGP12.phx.gbl...
Chris
Yes, i want queries, updates, delete records but in the tools
I can work this ?
thanks
Vivian
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> escreveu na mensagem
news:uR**************@TK2MSFTNGP12.phx.gbl...
Query or update? (update is harder - I've never got it to work
successfully without converting to an Access DB and then back again on the fly).
Check out
http://www.pstruh.cz/help/RSConv/database.asp
for the tool that does that (it's very good).
I have a VB class that will do reads and queries - it's easily ported to
VBScript if necessary (and indeed I may have already done it somewhere) if
you need it.
Chris.
"viviane lima - * - * -" <vi*********@ligbr.com.br> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Also idea ?
how to use ASP for working .DBF files ?
Please help me ..
thanks
Vivian
