Operation must use an Updateable Query / SQL - VBA

Question

I am trying to create a very dynamic macro that will update different tables in a Database, depending on what the user chooses. Each table has of course distinct titles and information. I'm having a problem with the update (when the user adds new records to an old Table). This is part of the code, the problem is when it gets to the ".update", I get the "Operation must use an Updateable Query" error.

Dim DBCnn As ADODB.Connection
Dim RecSet As ADODB.Recordset
Dim sQRY As String
Dim FilePath, Titulo, Tabla As String
Dim LastRow, LastColumn, TotalRecords, Id As Long

Set DBCnn = New ADODB.Connection
Set RecSet = New ADODB.Recordset
DBCnn.Mode = adModeReadWrite
DBCnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & FilePath & ";"

sQRY = "SELECT * FROM Customers" & Tabla ' & " WHERE PopID = " & lngid

RecSet.CursorLocation = adUseClient
RecSet.Open _
    Source:=sQRY, _
    ActiveConnection:=DBCnn, _
    CursorType:=adOpenDynaset, _
    LockType:=adLockOptimistic


Do While Range("A" & LastRow).Value <> ""
' repeat until first empty cell in column A
With RecSet
    .AddNew
    .Fields("Id") = Range("A" & LastRow).Value
    .Fields("Name") = Range("B" & LastRow).Text
    .Fields("Age") = Range("C" & LastRow).Value
    .Update '(Here's my error)
End With
LastRow = LastRow + 1
Loop

What type is your database file, ".accdb"? isn't it open or protected? your code looks fine except CursorType:=adOpenDynaset which I don't recognise- shouldn't it be adOpenDynamic?? — Kazimierz Jawor
– Kazimierz Jawor, Commented Aug 5, 2013 at 12:35
as far as I know its not protected, and I'm running it while its closed. Also, I changed it to Dynamic and it still doesn't work :( — Cardonai
– Cardonai, Commented Aug 5, 2013 at 12:45
So, I use similar code except DBCnn.Mode and RecSet.CursorLocation which have default value in my situation. But I checked your settings with my code and it's seems that your code is correct. In my humble opinion there is something wrong with your database/table settings. — Kazimierz Jawor
– Kazimierz Jawor, Commented Aug 5, 2013 at 13:06

HansUp · Accepted Answer · 2013-08-07 20:42:50Z

1

Discard this line:

RecSet.CursorLocation = adUseClient

Or try it like this instead:

RecSet.CursorLocation = adUseServer

See the Remarks section at CursorLocation Property (ADO) on MSDN:

"If the CursorLocation property is set to adUseClient, the recordset will be accessible as read-only, and recordset updates to the host are not possible."

answered Aug 7, 2013 at 20:42

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jimmy Smith · Accepted Answer · 2013-08-07 20:27:03Z

0

You're concatenating a string here - "SELECT * FROM Customers" & Tabla , but I don't see where Tabla is being provided.

Have you tried running the query directly in Access?
Also, have you tried changing cursortype yet? http://www.w3schools.com/ado/prop_rs_cursortype.asp

answered Aug 7, 2013 at 20:27

Jimmy Smith

2,4511 gold badge16 silver badges19 bronze badges

Collectives™ on Stack Overflow

Operation must use an Updateable Query / SQL - VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related