Missing operator when running SQL from command button

[detrie]

MS Access front end SQL 2008 backend

This runs fine in SQL Server query but throws a Run-Time 3075 syntax missing operator error when executed from a form command button

Dim strCountryCd As String
strCountryCd = "UPDATE contact set tblContact.mailingcountry = tblIMT_IOT_Country_name.CountryCode from tblIMT_IOT_Country_name where tblcontact.mailingcountry = tblIMT_IOT_Country_name.CountryName;"
DoCmd.RunSQL (strCountryCd)

Am I missing something simple?

 

[boblarson]

You need to change this:
UPDATE contact

to this:

UPDATE tblContact

 

[boblarson]

Or, if your table is named Contact then you would need to change these parts:

tblContact.mailingcountry

to

Contact.mailingcountry

 

[detrie]

Thanks for your reply Bob.
Good catch .. it should be tblContact.
After changing it, I still get the same error

 

[boblarson]

I don't see anything else wrong with the SQL string. But I don't think you want the brackets for the DoCmd.RunSQL (but I would suggest using the syntax:

CurrentDb.Execute strCountryCd, dbFailOnError

instead).

 

[detrie]

Tried CurrentDb.Execute strCountryCd, dbFailOnError

I get dbFailOnError = 128 .. Does that mean anything to you?

 

[boblarson]

Forgot to ask - does your code wrap like you have shown above or does your string assignment just go all the way out? If you want it to wrap, you will need to do this:

Private Sub YourCommandButtonNameHere_Click()
   Dim strCountryCd As String
      strCountryCd = "UPDATE contact set tblContact.mailingcountry = tblIMT_IOT_Country_name.CountryCode " & _
          "FROM tblIMT_IOT_Country_name where tblcontact.mailingcountry = tblIMT_IOT_Country_name.CountryName;"
 
   CurrentDb.Execute strCountryCd, dbFailOnError
End Sub

If it is a SQL Server Backend then you would need this instead:

Private Sub YourCommandButtonNameHere_Click()
   Dim strCountryCd As String
      strCountryCd = "UPDATE contact set tblContact.mailingcountry = tblIMT_IOT_Country_name.CountryCode " & _
          "FROM tblIMT_IOT_Country_name where tblcontact.mailingcountry = tblIMT_IOT_Country_name.CountryName;"
 
   CurrentDb.Execute strCountryCd, [B]dbSeeChanges[/B]
End Sub

 

[detrie]

It is SQL backend (2008) so I tried the latter.

dbSeeChanges = 512

 

[boblarson]

It is SQL backend (2008) so I tried the latter.

dbSeeChanges = 512

Post the FULL EXACT code for the button's click event that you are now trying to use.

 

[detrie]

Private Sub cmd1_Click()
Dim strCountryCd As String
strCountryCd = "UPDATE tblContact set tblContact.mailingcountry = tblIMT_IOT_Country_name.CountryCode " & _
"FROM tblIMT_IOT_Country_name where tbContact.mailingcountry = tblIMT_IOT_Country_name.CountryName;"

CurrentDb.Execute strCountryCd, dbSeeChanges
End Sub

the exact error...

Run-time error '3075':

Syntax error (missing operator) in query expression
'tblIMT_IOTCountry_Name.CountryCode FROM
tblIMT_IOT_County_Name'.

 

[boblarson]

Does your table name (tblIMT_IOT_Country_name) have the underscores or are there really spaces there?

 

[detrie]

they are in fact underscores

The tables were upsized from Access to SQL . In the Access navigation pane, they appear as tblContact and tblIMT_IOT_Country_Name. When I hover of the table name in the naviagtion pane, the table = dbo.tblContact and dbo.tblIMT_IOT_Country_Name. The same is true of the table names in SQL.

 

[detrie]

When I use the query designer in SQL Express 2008 I get


UPDATE tblContact
SET mailingcountry = tblIMT_IOT_Country_Name.CountryCode
FROM tblIMT_IOT_tblCountry_Name INNER JOIN
tblContact ON tblIMT_IOT_Country_Name.CountryName = tblContact.mailingcountry

This executes fine from SQL. When I put it behind a command button (below), Access 2007 still complains with 3075 (missing operator)

Dim strCountryCd as string
strCountryCd = "UPDATE tblContact " & _
"Set mailingcountry = tblIMT_IOT_Country_Name.CountryCode " & _
"FROM tblIMT_IOT_Country_Name INNER JOIN " & _
"tblContact ON tblIMT_IOT_Country_Name.CountryName = tblContact.mailingcountry;"

CurrentDb.Execute strCountryCd, dbSeeChanges
End Sub


Any other ideas?

Available References (in priority order)=
Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine object

Should I have something else checked?

 

[boblarson]

So are these tables linked from SQL Server and do they by chance show in the Navigation Pane like:

dbo_tblContact
dbo_tblIMT_IOT_Country_Name.CountryName

 

[detrie]

No Bob.. It does not. tblContact

I'm thinking the issue may be tied to SQL Express 2008. I have uninstalled it and just finishes installing SQL 2005.

I will try it and report back

 

[boblarson]

No Bob.. It does not. tblContact

I'm thinking the issue may be tied to SQL Express 2008. I have uninstalled it and just finishes installing SQL 2005.

I will try it and report back

Also, make sure you use the correct DRIVER to connect with SQL Server. You should be using the SQL Native Client for 2005 (if on WinXP and SQL Server if on Vista or 7). For 2008 you should have used SQL Native Client 10 (unless on Win7 which then SQL Server would be fine).

 

[detrie]

Re: Missing operator when running SQL from command button (resolved)

Hey Bob,

I don't think SQL Express 2008 was the issue but going to SQL Express 2005 helped me start from scratch.

In doing so, I found that I did not have a primary key set in tblIMT_IOT_Country_Name. After setting a PK in the table (and making sure all of my tables are properly set up with a PK), everything is running great.

I guess sometimes it pays to go back to the basics.

Thank you for sticking with me and I'm sorry to have wasted your time

Detrie

 

[boblarson]

Ouch - yep that will do it every time. Access and SQL Server only play nice with each other (meaning writing back to SQL Server from Access) if the SQL Server table has a primary key defined.

No worries on the time, but it is nice to know that we weren't looking at the right problem. I gotta remember to ask about the primary keys when we're talking Access and SQL not working together.