Runtime Error 2501 on docmd.runSQL

[schwachmama]

Hi,


The code below works fine on my work computer but an error code pops up on my co-workers computer.

Dim strTname As String
strTname = "'tbl_Res_Analytical_S'"
DoCmd.RunSQL ("update tbl_Tablefields set inImpList = true where tname in (" & strTname & ")")

If its changed to execute it works fine e.g.

dbA.Execute ("update tbl_Tablefields set inImpList = true where tname in (" & strTname & ")")

I believe its a docmd error and there are a lot of other docmd code through out the batch and I don't want to have to go through and change each one. Any ideas why this would happen?

tbl_Tablefields = Table
inImpList = field in tbl_Tablefields, true/false
tname = field in tbl_Tablefields, text (255)

thanks.

[NoellaG]

Hi,

if it works on your computer and your collegue is getting an application/object defined error, it could be a missing reference. Check the window VBA -> Tools -> References for missing references on her computer.

[schwachmama]

Thanks NoellaG but sadly our object libraries don't look different. We have the exact same 5 objects defined.
·
Visual Basic For Applications

· Microsoft Access 14.0 Object Library

· OLE Automation
· Microsoft Office 14.0 Access database engine object library
· Microsoft Office 14.0 Object Library

But I know that I have previously installed other libraries..I just can't remember what or when.

[June7]

What is the exact error message?

I use Execute and don't have to bother with turning off/on warning messages with SetWarnings.

[schwachmama]

Thank June7...I re-looked at ALL the error codes that were popping up and realized that the reason for the error was actually happening earlier in the code. Below was the original code and the error that popped up was "about to update 0 row(s)". If I clicked yes, it would get stuck in a never ending loop. If I clicked no, I would get a runtime error 2501.

Dim j, k  As Integer
For j = 0 To dbA.TableDefs.Count - 1
 For k = 0 To dbA.TableDefs(j).Fields.Count - 1
     dbA.Execute ("insert into tbl_Tablefields (tname, fname, ftype) values ('" & dbA.TableDefs(j).Name & "','" & _
     dbA.TableDefs(j).Fields(k).Name & "','" & dbA.TableDefs(j).Fields(k).Type & "')")
  Next k

Dim strTname As String
strTname = "'tbl_Res_Analytical_S'"
DoCmd.RunSQL ("update tbl_Tablefields set inImpList = true where tname in (" & strTname & ")")

Next j

When I move next j right after next k and then run my docmd all works. I'm fine with the change in logic, but now my co-workers computer wants to confirm every action. I've added the code below and it stops asking for the confirmations but I guess now my issue has changed slightly to why does my co-workers computer have to confirm actions but mine does not. And why did the original code work on mine and not my co-workers? But I guess that's a question for another thread.

Application.SetOption "Confirm Action Queries", 0

[June7]

Don't know why one gets warnings and other doesn't. Execute should not be accompanied with warning message.

But I don't quite understand this code - how can the INSERT work when there is only one VALUES parameter?

Are you saying the posted code is not the final version?