Query cannot be updated because the FROM clause is not a single simple table name

Question

We just moved our SQL 2000 databases to a new SQL 2008 box. After the move, we bound the IP address of the SQL 2000 box to the new SQL 2008 box. This works, except in a VB6 application running on a Windows 2000 SP4 box where we are getting the error:

"Query cannot be updated because the FROM clause is not a single simple table name"

View the actual error message screenshot below: http://screencast.com/t/MTViNDBh

Doing some searching, I find that this is an ODBC error-- not sure how to fix? This app has been working flawlessly until we moved all db's to SQL 2008 (which all work well, except this one app!).

Edit:

Looking into his code, it does not appear to be using ODBC:

sEncCn = "PROVIDER=" & strEncProvider & "Driver=" & strEncDriver & "Server=" & strEncServer & "UID=" & sUID & "PWD=" & sPWD & "Database=" & strEncDb

strEncProvider is "MSDASQL" Driver is "SQL Server"

Any help!?

Answer 1

My guess is that your VB6 app is attempting to open an write-able recordset (rather than a read-only recordset) and because of something in your FROM clause, SQL Server cannot make this write-able.

That being said, help us help you by including:

1. the code that's failing in VB6 along with relevant "setup" code (i.e. the code used to create your connection and your recordset object variables, etc.)
2. the SQL statement you are trying to execute

Answer 2

Consider setting the compatibility mode for the database to SQL Server 2000. The option is available from Database properties in SQL Server Management Studio.

Answer 3

Take a look at the ODBC data source in Administrative Tools\Data Sources (ODBC) -- if you are positive this is what the application is using. Can you test the connection from there? Is it using the IP address or maybe a server name?