troubleshooting Question

The same Access query text is valid in some places and invalid in others

Avatar of Mark Dalley
Mark DalleyFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessMicrosoft Development
10 Comments7 Solutions119 ViewsLast Modified:
Hello Experts

I am using Access 2010. I have the following DDL query:

CREATE TABLE Test (
   Dataset_Key VARCHAR(100) WITH COMPRESSION CONSTRAINT PrimaryKey PRIMARY KEY,
   StartDate DATETIME,
   EndDate DATETIME,
   PersonID VARCHAR(15) WITH COMPRESSION CONSTRAINT PersonID UNIQUE,
   PersonSurname VARCHAR(30) WITH COMPRESSION,
   PersonForename VARCHAR(30) WITH COMPRESSION,
   PersonMiddleNames VARCHAR(30) WITH COMPRESSION,
   PersonTitle VARCHAR(30) WITH COMPRESSION,
   PersonSex VARCHAR(1) WITH COMPRESSION,
   PersonDateOfBirth DATETIME,
   InstituteLocalCode VARCHAR(2) WITH COMPRESSION,
   AddressKey LONG,
   PersonPremises VARCHAR(30) WITH COMPRESSION,
   PersonStreet VARCHAR(30) WITH COMPRESSION,
   PersonLocality VARCHAR(30) WITH COMPRESSION,
   PersonPostTown VARCHAR(30) WITH COMPRESSION,
   PersonCounty VARCHAR(30) WITH COMPRESSION,
   Postcode VARCHAR(8) WITH COMPRESSION,
   PracticeCode VARCHAR(6) WITH COMPRESSION );

Sometimes it works, sometimes not, as follows:

1.  If I put it into a string and try to use it to create a query in DAO using the db.CreateQueryDef, it comes back with Error 3290: Syntax error in CREATE TABLE statement. This is true *whether or not* the database has been enabled for the ANSI 92 SQL syntax.
2a.  If I create a new query, paste it into the SQL text view, and click Run, it executes perfectly, *as long as* the database has been enabled for the ANSI 92 SQL syntax.
2b. If on the other hand, I haven't enabled the ANSI 92 SQL syntax, I get an error similar to case 1. The first WITH (before COMPRESSION) is highlighted as invalid.
3.  However, if I put the query into a string and try to run it using CurrentProject.Connection.Execute <string>, it ALWAYS works, *even if the ANSI 92 SQL syntax has NOT been enabled*!

On browsing the forums, I repeatedly come across the implication that Access needs ADO to understand the ANSI 92 syntax. However, that is definitely not the case here - I am just using the MS Office 14.0 Access database engine.

I'm confused. Can anyone out there shed any light on this?

My main object, by the way, is to create a routine that will just run any valid action query SQL string you throw at it and return the number of records affected etc. This includes queries like the one above and strings referring to SQL pass through queries, neither of which the DAO methods seem able to handle. Sure, I could rewrite it using a Connection object, but doesn't that mean I need to use ADO - at least if I am talking to external data sources?

Yours seeking enlightenment. Sorry for the long question - I hope it is clear.

Hopeful Kiwi
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 7 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 7 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros