Converting Access DAO to ADO

Question

Post reply

Converting Access DAO to ADO

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
February 25, 2005 at 11:57 am

#66577

I was following an MS article on converting the DAO code to ADO in an Access DB after upsizing the database. I converted the following:
Private Sub Remove_Click()
On Error GoTo Err_Remove_Click
    Dim db As Database
    Set db = CurrentDb
    Dim rst As DAO.Recordset
    Set rst = db.OpenRecordset("2004 UIL Prospects Appointments", dbOpenDynaset)
    rst.FindFirst "[Agency] = '" & Me.Agency_Name & "' and [Address] = '" & Me.Street & "'"
    If rst.NoMatch = False Then
        rst.Edit
        rst.Fields("Added") = False
        rst.Update
    End If
    rst.Close
   Me.[Branch ID] = -1
   Me.Requery
Exit_Remove_Click:
    Exit Sub
Err_Remove_Click:
    MsgBox Err.Description
    Resume Exit_Remove_Click
End Sub
To:
Private Sub Remove_Click()
On Error GoTo Err_Remove_Click
    Dim db As ADODB.Connection
    db.Open "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Agency ControlSQL;Data Source=servername", "userid" "password"
    Dim rst As ADODB.Recordset
    SQL = "Select * from [] where Agency='" & Me.Agency_Name & "' and Address='" & Me.Street & "'"
    rst.Open SQL, db, adOpenDynamic, adLockOptimistic

    If Not (rst.EOF) Then
        rst.Fields("Added") = False
        rst.Update
    End If
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
   Me.[Branch ID] = -1
   Me.Requery
Exit_Remove_Click:
    Exit Sub
Err_Remove_Click:
    MsgBox Err.Description
    Resume Exit_Remove_Click
End Sub
I also added references to the Microsoft ADO 2.7 Library and ADO 2.7 Recordset Library. When I click the button that runs the code I get "Object variable or With block variable not set" on the db.Open statement. I've tried multiple connection string formats, db.open formats, no luck. Am I missing something obvious?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Dim db As ADODB.Connection

'add this

set db = new adodb.connection

db.Open "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Agency ControlSQL;Data Source=servername", "userid" "password"

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

That did the trick. I knew there was something missing...

Now I have a problem with Access complaining that there are concurrent updates to one of the tables, and I'm the only one accessing the database. Oh well, there's always Monday...

Thanks.