Using With and Do While

[marnieg]

Have an adp database connected to SQL Server. Trying to read one table to update another one. Getting error about "Object variable or With variable not set"

I'm used to dealing with mdb DAO connections and not ADO. I'm sure I'm just missing how to Open the Recordset.

Dim strSqlQuery As String
Dim rs As ADODB.Recordset
strSQLQuery = "Select F1 from donotcontact1"
rs.Open strSQLQuery, CurrentProject.BaseConnectionString, adOpenForwardOnly, adLockReadOnly
With rs
Do While Not rs.EOF And Not rs.BOF

delemail = rs.Fields("F1")

CurrentDb.Execute "Update Leads set EMailAddress = null where EMailAddress = '" & delemail & "'"
.MoveNext
Loop
.Close
End With

 

[boblarson]

See if this works instead:

Dim strSqlQuery As String
Dim rs As ADODB.Recordset
 
strSQLQuery = "Select F1 from donotcontact1"
 
rs.Open strSQLQuery, CurrentProject.BaseConnectionString, adOpenForwardOnly, adLockReadOnly
 
With rs
If rs.RecordCount > 0 Then
   Do Until .EOF 
 
   delemail = .Fields("F1")
 
    CurrentDb.Execute "Update Leads set EMailAddress = null where EMailAddress = '" & delemail & "'"
    .MoveNext
 
Loop
 
.Close
 
Set rs = Nothing
End With

 

[marnieg]

I figured out the ADODB code for any others interested.

Dim Sqltxt As String
Dim cmd As New ADODB.Command

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.ConnectionString = CurrentProject.BaseConnectionString
cnn.Open

cmd.ActiveConnection = cnn
cmd.CommandText = "UpdateEmail"
cmd.CommandType = adCmdStoredProc
Sqltxt = "SELECT F1 from donotcontact1"
rst.Open Sqltxt, cnn, adOpenForwardOnly, adLockReadOnly
rst.MoveFirst
DoCmd.Hourglass True

Do Until rst.EOF


cmd("@Email") = rst.Fields("F1")
cmd.Execute
rst.MoveNext
Loop

 

[boblarson]

You don't need the rst.MoveFirst. When opening a recordset it is at the first record. In fact, it will generate an error if there are zero records in the recordset.