Access VBA Error 3075 Syntax Error (missing operator)

Question

I seem to continually run in to problems when setting a string as and SQL statement I had pre-written in SSMS.

I have 2 questions which are both related. First, I only seem to run in to problems when dealing with a bit more complex queries (several joins, where conditions, correlated sub queries, and case statements) as a opposed to simple queries. Anyone have any good resources where I can read about syntax 'rules' when writing queries in SSMS to VBA?

Second, below is an example of a query I wrote in the AdventureWorks sample DB that gives me the error listed in the title of this question. My T-SQL statement is:

sSQL = "SELECT ba.BusinessEntityID,ba.AddressID
              ,(CASE WHEN ba.BusinessEntityID > 5
                THEN
                (SELECT pp.FirstName FROM [Person].[Person] pp
                WHERE pp.BusinessEntityID = ba.BusinessEntityID)
               Else 'AA' END) AS 'TEST'
        FROM Person.BusinessEntityAddress ba
        INNER JOIN person.AddressType at ON at.AddressTypeID = ba.AddressTypeID
        WHERE ba.BusinessEntityID < 11;"

Error occurs on line "Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)" which says:

"Run-Time Error 3075 Syntax Error (missing operator) in query expression '(CASE WHEN ba.BusinessEntityID > 5 THEN (SELECT pp.FirstName FROM [Person].[Person] pp WHERE pp.BusinessEntityID = ba.BusinessEntityID) Else 'AA' END) AS 'TEST'"

Utsav · Accepted Answer · 2015-11-17 06:14:12Z

0

Try this

sSQL = "SELECT ba.BusinessEntityID,ba.AddressID
          ,(CASE WHEN ba.BusinessEntityID > 5
            THEN
            (SELECT pp.FirstName FROM [Person].[Person] pp
            WHERE pp.BusinessEntityID = ba.BusinessEntityID)
           Else 'AA' END) AS TEST 
    FROM Person.BusinessEntityAddress ba
    INNER JOIN person.AddressType at ON at.AddressTypeID = ba.AddressTypeID
    WHERE ba.BusinessEntityID < 11;"

No need to using quotes in alias name. If you must, then there are other ways (like drop AS or use Double quotes) but it is not suggested.

answered Nov 17, 2015 at 6:14

Utsav

8,1632 gold badges22 silver badges40 bronze badges

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

2 Comments

xJustin8 Over a year ago

Thank you for the response, but that did not work. Still have the exact same error message.

Utsav Over a year ago

try replacing [Person].[Person] with Person.Person

Jericho Johnson · Accepted Answer · 2018-09-13 00:09:53Z

The issue here is that you are trying to use a TSQL statement where an Access SQL statement is expected. Access SQL does not support CASE WHEN, or many of the constructs that TSQL supports.

The OpenRecordset method expects a table name, saved query name, or Access SQL statement.

You can solve this by creating and saving a Pass Through Query in Microsoft Access. When you create a Pass Through Query, you can then use your TSQL statement, and you set the connection string to your SQL database in the properties of the Pass Through Query.

As the name implies, Access passes the parsing and execution of a Pass Through Query over to SQL Server.

Once you have saved your Pass Through Query and tested it, you can use your OpenRecordset command as-is, just replace the first parameter with the name of the Pass Through Query.

Collectives™ on Stack Overflow

Access VBA Error 3075 Syntax Error (missing operator)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related