cannot do rs.AddNew after setting rs=cn.Execute(strsql)

[peace]

I have opened my recordset rs with adLockOptimistic which set LockType to 3. However after I Set rs = cn.Execute(strSQL) which executes my SQL string, the LockType got reset to 1, which is read-only and I cannot do an rs.AddNew to the recordset.

Code:

Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseServer rs.Open Source:="Customer", _ ActiveConnection:=cn, _ CursorType:=adOpenDynamic, _ LockType:=adLockOptimistic, _ Options:=adCmdTable Dim strSQL As String 'New CustId Range("A2:A100").Select For Each id In Selection NewCustId = id.Value If id.Value <> "" Then strSQL = "SELECT * FROM Customer " & _ "WHERE CustId = '" & NewId & "' " & ";" Debug.Print "LT Before:", rs.LockType Set rs = cn.Execute(strSQL) Debug.Print "LT After:", rs.LockType If (rs.BOF And rs.EOF) Then rs.AddNew rs.Fields("CustomerID").Value = CustId rs.Fields("Addr").Value = id.Offset(0, 1).Value ... more customer info rs.Update Else Debug.Print "Already existed: ", id.Value End If End If Next id

Using an Execute on my connection, how do I do .AddNew ... .Update on the recordset - make it not for read-only. Please help!

<tbody>
</tbody>

 

[xenou]

Do you have these ID's in an Excel spreadsheet? What's the aim of your process. It looks like you are adding customers. Are the customer ID unique (Cannot be duplicated in the table)? How much information are you adding when you say "...more customer info".

Offhand it looks vaguely wrong to select based on a criteria and then add to the same recordset. Plus your recordset type is declared as a table type, but you aren't doing that. I'd probably go about this differently. Not sure. And you normally don't "execute" select queries - though that might work here anyway.

 

[Norie]

Instead of Execute why not use Open?

 

[peace]

Thanks for the quick reply. Yes, CustId is unique and is in a spreadsheet. I just want to store the Custumer table in Access. I do a Select and if the CustId does not exist, I would do an AddNew followed by an update.

 

[peace]

Thanks! I already did an Open in the beginning. I'll try to use another Open in the loop as well.

 

[Norie]

Or you could use an actual UPDATE/INSERT query.

 

[xenou]

Or you could use an actual UPDATE/INSERT query.

Honestly, that would be my preference too.

 

[peace]

Thank you all. Will try this out. Appreciate the suggestions.

 

[xenou]

If you want to you can just insert all the records. If they already exist, they just fail on insertion. I'd probably write this from the access side but in principle it's the same if you do it from the excel side.

From Access:
Import all the records to a temp table.
Run an append query to append (duplicates will be rejected)

From Excel:
Insert each record to the table
Duplicates will error (catch the error)

Or to build on your current code:
For each record, check if it exists (open a recordset and check for EOF, or run a count() query)
If the record doesn't, exist, either 1) open a recordset and do the .AddNew stuff, or 2) just create an insert query and run it against the database - which personally I find easier, if you are know how to work with strings in VBA anyway.