VBA SQL problems *SOLVED*

[Tweak155]

I've researched this online and from what I've gathered, this is an actual bug with using ADO vs DAO, however this entire program is already written using ADO so I'm hoping there is a workaround.

Basically I have an UPDATE statement that uses a WHERE clause on the AutoNumber field (this brings the result to exactly one row in the table).

The SQL statement works when I execute it through an external SQL program connecting to the exact same database, so I know the SQL command is proper and formatted correctly. The correct line in the database updates correctly.

However when I execute this through the macro it gives me an error of:

Run-time error '-2147217900 (80040e14)':

Syntax error in UPDATE statement

This command worked previously as well, it is only when I added fields to the database that it caused problems. Actually now that I type through this I can probably create a new database and see if it works on a fresh build altogether. However, the fact that it works through Microsoft SQL when I copy and paste the exact same command leads me to believe it still wont work.

Ideas?

SOLUTION:

I figured it out. The problem is I named a field "OPEN". The insert function does not have a problem with this, but the update function does in VBA SQL. I have fixed the problem. Thanks for looking.

 

[KLin]

how are you executing the sql statement?

1. docmd.runsql?
2. using an ado connection object and running the execute method?

Have you tried setting a string variable, setting the value of the variable to the update statement, and evaluating the statement in the immediate window to ensure the syntax is correct?

 

[Tweak155]

how are you executing the sql statement?

1. docmd.runsql?
2. using an ado connection object and running the execute method?

Have you tried setting a string variable, setting the value of the variable to the update statement, and evaluating the statement in the immediate window to ensure the syntax is correct?

Option 2.

And yes. Since I'm using EXCEL, I have it output the string to a cell. I then copied that output to the external SQL program and execute and that is how I know it works.

 

[Tweak155]

The only thing I can think is I'm not opening the DB properly with sole read/write access (although this is in a multi-user environment) during the length of execution. But it worked until I added more fields. And regardless in this test environment I'm the only user accessing the DB. Here is how I open the connection:

Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset


'Path to the database.
stDB = DB_LOC

'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"

With cnt
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With

EDIT:

I figured it out. The problem is I named a field "OPEN". The insert function does not have a problem with this, but the update function does in VBA SQL. I have fixed the problem. Thanks for looking.

 

[KLin]

http://msdn.microsoft.com/en-us/library/ms189822.aspx

OPEN is a reserved word . Putting the fieldname in square brackets(i.e [OPEN]) probably would have fixed it as well.

 

[Tweak155]

http://msdn.microsoft.com/en-us/library/ms189822.aspx

OPEN is a reserved word . Putting the fieldname in square brackets(i.e [OPEN]) probably would have fixed it as well.

Yeah I would have been able to narrow it down a lot sooner had INSERT not worked, and then UPDATE working through MSSQL.

Thanks