updating a .dbf file using ADO


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: updating a .dbf file using ADO

  1. #1
    vbWorm Guest

    updating a .dbf file using ADO


    I was not able to find a way to update a .dbf table (recordset) from within
    vb6 yet. The following code does change the recordset but does not update
    the underlying file. Any kind of help would be appreciated. Thank you.

    Option Explicit

    Private Sub Command1_Click()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim sDBFPath As String
    Dim sCnnStr As String
    Dim sSQL As String

    sDBFPath = "C:\DBFfiles"

    sCnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
    sCnnStr = sCnnStr & "Data Source=" & sDBFPath & ";"
    sCnnStr = sCnnStr & "Extended Properties=dBASE IV;"

    Set cnn = New ADODB.Connection
    cnn.CursorLocation = adUseClient
    cnn.ConnectionString = sCnnStr
    cnn.Open

    sSQL = "select * from MyFile.dbf"
    Set rst = New ADODB.Recordset
    rst.Open sSQL, cnn, adOpenDynamic, adLockBatchOptimistic

    If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do While Not rst.EOF
    Debug.Print rst("AFIELD")
    rst("AFIELD").Value = "3.1415926535"
    rst.Update
    rst.MoveNext
    Loop
    End If

    rst.Close
    Set rst = Nothing

    cnn.Close
    Set cnn = Nothing

    End Sub


  2. #2
    Keith Guest

    Re: updating a .dbf file using ADO



    I believe that since you opened the recordset in batch mode, you must use
    the recordset method updatebatch to save your changes. Hope that helps.



    "vbWorm" <a_chekhlov@thorcapital.com> wrote:
    >
    >I was not able to find a way to update a .dbf table (recordset) from within
    >vb6 yet. The following code does change the recordset but does not update
    >the underlying file. Any kind of help would be appreciated. Thank you.
    >
    >Option Explicit
    >
    >Private Sub Command1_Click()
    > Dim cnn As ADODB.Connection
    > Dim rst As ADODB.Recordset
    > Dim sDBFPath As String
    > Dim sCnnStr As String
    > Dim sSQL As String
    >
    > sDBFPath = "C:\DBFfiles"
    >
    > sCnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
    > sCnnStr = sCnnStr & "Data Source=" & sDBFPath & ";"
    > sCnnStr = sCnnStr & "Extended Properties=dBASE IV;"
    >
    > Set cnn = New ADODB.Connection
    > cnn.CursorLocation = adUseClient
    > cnn.ConnectionString = sCnnStr
    > cnn.Open
    >
    > sSQL = "select * from MyFile.dbf"
    > Set rst = New ADODB.Recordset
    > rst.Open sSQL, cnn, adOpenDynamic, adLockBatchOptimistic
    >
    > If rst.RecordCount > 0 Then
    > rst.MoveFirst
    > Do While Not rst.EOF
    > Debug.Print rst("AFIELD")
    > rst("AFIELD").Value = "3.1415926535"
    > rst.Update
    > rst.MoveNext
    > Loop
    > End If
    >
    > rst.Close
    > Set rst = Nothing
    >
    > cnn.Close
    > Set cnn = Nothing
    >
    >End Sub
    >



  3. #3
    Paul Clement Guest

    Re: updating a .dbf file using ADO

    On 21 Aug 2000 18:49:51 -0700, "vbWorm" <a_chekhlov@thorcapital.com> wrote:


    I was not able to find a way to update a .dbf table (recordset) from within
    vb6 yet. The following code does change the recordset but does not update
    the underlying file. Any kind of help would be appreciated. Thank you.

    The Jet 4.0 OLEDB provider does not support the ability to update a dBase database without the
    installation of the BDE (Borland Database Engine) 4.0 or greater. You must revert to the Jet 3.51
    provider if you don't want to use BDE.

    Here is some additional info:

    http://support.microsoft.com/support.../Q263/5/61.ASP


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  4. #4
    Bill Hershey Guest

    Re: updating a .dbf file using ADO


    "vbWorm" <a_chekhlov@thorcapital.com> wrote:
    >
    >I was not able to find a way to update a .dbf table (recordset) from within
    >vb6 yet. The following code does change the recordset but does not update
    >the underlying file. Any kind of help would be appreciated. Thank you.
    >
    >Option Explicit
    >
    >Private Sub Command1_Click()
    > Dim cnn As ADODB.Connection
    > Dim rst As ADODB.Recordset
    > Dim sDBFPath As String
    > Dim sCnnStr As String
    > Dim sSQL As String
    >
    > sDBFPath = "C:\DBFfiles"
    >
    > sCnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
    > sCnnStr = sCnnStr & "Data Source=" & sDBFPath & ";"
    > sCnnStr = sCnnStr & "Extended Properties=dBASE IV;"
    >
    > Set cnn = New ADODB.Connection
    > cnn.CursorLocation = adUseClient
    > cnn.ConnectionString = sCnnStr
    > cnn.Open
    >
    > sSQL = "select * from MyFile.dbf"
    > Set rst = New ADODB.Recordset
    > rst.Open sSQL, cnn, adOpenDynamic, adLockBatchOptimistic
    >
    > If rst.RecordCount > 0 Then
    > rst.MoveFirst
    > Do While Not rst.EOF
    > Debug.Print rst("AFIELD")
    > rst("AFIELD").Value = "3.1415926535"
    > rst.Update
    > rst.MoveNext
    > Loop
    > End If
    >
    > rst.Close
    > Set rst = Nothing
    >
    > cnn.Close
    > Set cnn = Nothing
    >
    >End Sub
    >


    You are using adLockBatchOptimistic but are not doing a batch update, try
    using adLockOptimistic, also Jet 4.0 does not support updates to ISAM dbase
    databases, you can switch to 3.51, it can co-exist with 4.0 on the same machine.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center