Man nice. Thank you Chris.
--
George Hester
__________________________________
"Chris Barber" <chris@blue-canoe.co.uk.NOSPAM> wrote in message news:u0nQNISWDHA.1204@TK2MSFTNGP12.phx.gbl...[color=blue]
> 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 - * - * -" <vivianelima@ligbr.com.br> wrote in message
> news:edfpa$QWDHA.2248@TK2MSFTNGP12.phx.gbl...
> Chris
>
> Yes, i want queries, updates, delete records but in the tools
> I can work this ?
>
> thanks
>
> Vivian
>
> "Chris Barber" <chris@blue-canoe.co.uk.NOSPAM> escreveu na mensagem
> news:uRle4tIWDHA.1872@TK2MSFTNGP12.phx.gbl...[color=green]
> > Query or update? (update is harder - I've never got it to work[/color]
> successfully[color=green]
> > 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 - * - * -" <vivianelima@ligbr.com.br> wrote in message
> > news:%231TZioIWDHA.2120@TK2MSFTNGP10.phx.gbl...
> > Also idea ?
> >
> > how to use ASP for working .DBF files ?
> >
> > Please help me ..
> >
> > thanks
> >
> > Vivian
> >
> >
> >[/color]
>
>
>[/color]
