[RESOLVED] ADODB/MS Access 97 conflicts

[manofsteel00]

ok I'm using an ADODB connection to connect VB6 to a MS Access 97 database. Now I converted the database from a MS Access 2003 database, and because of this I can not change the data in the database. My question is: can somone point me on how to set up an ODBC connection with VB to connect to a MS Access 2003 database so that I can manipulate the data?

[FONT=Lucida Console]'Create a new connection --
        Set adoConnection = New ADODB.Connection
        
        'Create a new recordset --
        Set adoRecordset = New ADODB.Recordset
        
        'Build our connection string to use when we open the connection --
        connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
                         & "Data Source=C:\Documents and Settings\P6B0438\Desktop\CycleCount.mdb"
        
        adoConnection.Open connectString
        
        adoRecordset.Open "SECURITY", adoConnection
        Do Until adoRecordset.EOF
            If (adoRecordset!UserName = txtUserName.Text) Then
                Dim str As String
                str = txtOldPassword.Text
                If (adoRecordset!Password = txtOldPassword.Text) Then
                     [B]'this line does not work
                     adoRecordset!Password = txtNewPassword1.Text[/B]
                End If
                MsgBox ("The password should have been changed from " & str & " to " & adoRecordset!Password)
                
            End If
            adoRecordset.MoveNext
        Loop[/FONT]

Edit: Added [vbcode][/vbcode] tags for clairty. - Hack

[Mark Gambo]

Take a look at the ADO/Access example in my signature

[Keithuk]

Where is DAO? And don't say its old. A pole should have all options.

[manofsteel00]

What is DAO?

[si_the_geek]

DAO is the predecessor to ADO, and I certainly cannot advise it's use - I believe support for it has ended, or will soon. It also has less functionality than ADO.

The trouble with your code above is with your "adoRecordset.Open " line, you have not specified enough parameters to be able to write to it. If you don't specify otherwise, you get a forward-only, read-only recordset.

Try this instead:

adoRecordset.Open "SECURITY", adoConnection, adOpenDynamic, adLockOptimistic

[Keithuk]

What is DAO?

DAO = Data Access Objects. The last version was 3.6 which will open an Access 2000 DB.

It maybe old but you can still download v3.6 from M$.