Avatar of Mark Dalley
Mark DalleyFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

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

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
Microsoft AccessMicrosoft Development

Avatar of undefined
Last Comment
Mark Dalley
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Mark Dalley
Mark Dalley
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks for the responses, people.

@Rey Obrero
VARCHAR is an acceptable synonym for TEXT. (I just tested it). You are right about WITH COMPRESSION, though - that needs the new syntax enabled. Thanks for the link - Allen Browne is good.

@Nick67
Re your digression, I am needing to create a bunch of identically structured tables. I just decided to do it this way, but I have done it template-fashion a lot too. Perhaps it is because I am in the process of changing mental gears to think more like SQL Server TSQL.
I agree that the help has gone downhill since Access 2003. Shame. Thanks for the DAO passthrough CreateQueryDef sample.

@Pat Hartman
Point taken about SQL dialects and that they especially matter with pass-through queries. I use them because they are so much faster when you are pulling off half a million records at a time from a remote SQL Server DB.

@Scott McDaniel
I may be being extremely dense here, but if I am not using the ADO library anywhere, how can CurrentProject.Connection be an ADO connection?

Re this thing about upgrading databases to ANSI 92 SQL... it would seem from my testing that this only affects those parts of Access that relate to working in the GUI, such as the interactive query definition. It doesn't affect the DAO side, which always uses "Access" syntax, and it doesn't affect the Connection.Execute syntax, which always uses the SQL92 syntax, unless it is referring to a foreign database, in which case it *presumably* uses the foreign syntax. (I shall have to test this presumption with a SQL Server connection.)

We're getting there.

Hopeful KIwi
Avatar of Nick67
Nick67
Flag of Canada image

So what more input do you require?
SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Mark Dalley
Mark Dalley
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks people, for illuminating the various facets of the situation. I consider my question answered.

I propose to allocate point as follows:

200 points to Scott for the in-depth answer to what was nagging me the most.

100 points each to Rey, Nick and Pat for good input.

To passthru or not, that is the next question... I think I will mix and match. seeing how s-l-o-w some of my ODBC links are. Let SQL Server do the heavy lifting.

Cheers all

Hopeful Kiwi
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo