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
Mark DalleyInformation AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
Access does not use  VARCHAR, COMPRESSION

see this link Microsoft Access: DDL Code ExamplesMicrosoft Access: DDL Code Examples


for reference
0
Nick67Commented:
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.

And yet, your example is a Create Table -- which, unless you are creating brand-new files from scratch with scripts -- is not something any Access guy would be likely to code.  Tables are very much something you create once.  And an Access guy coding to create tables in a scripted way is far more likely to use the TableDefs collection and copy from an existing master than use DDL.  But I digrees.

db.CreateQueryDef is going to create a local query to create a local object -- and as Rey has pointed out, Access doesn't use the syntax you've got, hence the BANG!

paste it into the SQL text view, and click Run
Here you are not saving the syntax, so that Access doesn't care that going forward it'll be NFG.  It passes the SQL to the engine, which if conditions are right, will execute it.

CurrentProject.Connection.Execute <string>, it ALWAYS works
.Connection implies that the string is being executed elsewhere  I suspect that if .Connection implied Oracle and you had some  T-SQL specific statements in there things might not be so happy.

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.  You can create passthroughs in the Query Editor just fine, and the editor picks up the necessary ODBC connection information to make them work when you either call them in code or open the object.  In bare VBA though, that vital link is missing.  DAO QueryDef objects have a .Connect property -- and it is a valid DSN to whatever backend you want the Query to target.  No ADO required.

The last good help was in A2003
Here's a nice DAO passthrough CreateQueryDef sample from it

Sub ClientServerX1()

   Dim dbsCurrent As Database
   Dim qdfPassThrough As QueryDef
   Dim qdfLocal As QueryDef
   Dim rstTopFive As Recordset
   Dim strMessage As String

   ' Open a database from which QueryDef objects can be 
   ' created.
   Set dbsCurrent = OpenDatabase("DB1.mdb")

   ' Create a pass-through query to retrieve data from
   ' a Microsoft SQL Server database.
   Set qdfPassThrough = _
      dbsCurrent.CreateQueryDef("AllTitles")
   ' Note: The DSN referenced below must be set to 
   '       use Microsoft Windows NT Authentication Mode to 
   '       authorize user access to the Microsoft SQL Server.
   qdfPassThrough.Connect = _
       "ODBC;DATABASE=pubs;DSN=Publishers"
   qdfPassThrough.SQL = "SELECT * FROM titles " & _
      "ORDER BY ytd_sales DESC"
   qdfPassThrough.ReturnsRecords = True

   ' Create a temporary QueryDef object to retrieve
   ' data from the pass-through query.
   Set qdfLocal = dbsCurrent.CreateQueryDef("")
   qdfLocal.SQL = "SELECT TOP 5 title FROM AllTitles"

   Set rstTopFive = qdfLocal.OpenRecordset()

   ' Display results of queries.
   With rstTopFive
      strMessage = _
         "Our top 5 best-selling books are:" & vbCr

      Do While Not .EOF
         strMessage = strMessage & "  " & !Title & _
            vbCr
         .MoveNext
      Loop

      If .RecordCount > 5 Then
         strMessage = strMessage & _
            "(There was a tie, resulting in " & _
            vbCr & .RecordCount & _
            " books in the list.)"
      End If

      MsgBox strMessage
      .Close
   End With

   ' Delete new pass-through query because this is a
   ' demonstration.
   dbsCurrent.QueryDefs.Delete "AllTitles"
   dbsCurrent.Close

End Sub

Open in new window


It's all doable -- it just getting the syntax straight.
0
PatHartmanCommented:
You need to decide what syntax you want to use since as you have experienced, Access syntax is different from SQL Server Syntax.

If you set the database property to use ANSI 92 SQL syntax, THAT is the syntax you will have to use ALWAYS.

If you use some Access queries and some pass-through queries, you will have a mix of syntax.  The pass-through queries will ALWAYS need to be the syntax of the target database so if the database is Oracle, you MUST use Oracle syntax.  If it is DB2, you MUST use DB2 syntax, etc.  Syntax among all the major players is very similar but not identical so you need to know what you are doing if you are using pass-through queries.

Unless you have some specific requirement to create pass-through queries, stick with standard Access Syntax using linked tables.  Access does its best to "pass-through" EVERY query so although you minimize the overhead by coding specific pass-through queries, you don't gain enough advantage to justify having to live in both worlds.  Typically, in my apps, the ONLY queries that are pass-through are bulk updates.  And, the reason for that is because Access is too nice to us and wraps all action queries in a transaction so it can back out the update at the last minute if we want to do that.  That process is a huge resource hog and if your table is more than a few thousand rows, you'll see the difference.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

FREE DOWNLOAD
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Using the CurrentProperty.Connection creates an ADO connection, so that would explain why that always works.

http://msdn.microsoft.com/en-us/library/office/ff821478(v=office.15).aspx

.Connection implies that the string is being executed elsewhere
No, that's just the underlying connection Access has created to the database. You're still working on the tables, either linked or local.
0
Mark DalleyInformation AnalystAuthor Commented:
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
0
Nick67Commented:
So what more input do you require?
0
Nick67Commented:
@PatHartman
I never wind up doing bulk updates but...
Typically, in my apps, the ONLY queries that are pass-through are bulk updates.
In my app, I'll have a look at anything that only requires a read-only recordset as a candidate for a passthrough.
In practice, this means that any complex report recordsets get driven by a passthrough.
That way, I can rest assured that what Access is requesting for data is NOT going to accidentally lead to a bunch of table downloads for processing client-side--because, well it's getting done on the server!
0
PatHartmanCommented:
You only have to be aware of the things that cause a problem such as VBA or UDF functions in certain clauses.  Or joining heterogeneous tables.  Those ALWAYS bring down the non-local table.  The functions sometimes cause ACE to want to process the recordset locally.  If you find something that takes too long, you can always turn on the analyzer to see what the ODBC driver is requesting from the server.

I've had more than one database switch from SQL Server to something else such as Oracle or DB2.  I'm always glad I use Access SQL and so my only task is relinking the tables.  It's not so easy if you use a lot of pass-through queries or ADO or stored procedures.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
Access does quite a lot behind the scenes, and you don't have much control over that. Your references may not show an ADO connection, but you can be assured it is. Just check out the Properties and Attributes you see in Intellisense when you use CurrentProject.Connection versus those you see when you use Currentdb.Connection (a DAO connection). For example, CP has an "Open" method, whereas CDB does not - it has an OpenRecordset method, which is a DAO convention. CP has other attributes like CursorLocation, CommandTimeout, etc etc - all of which are methods/properties of an ADO connection, not a DAO connection.

The CDB connection has items like CreateQueryDef, Database, etc - all of which are methods/properties of a DAO Database connection.

DAO.Connection Properties: http://msdn.microsoft.com/en-us/library/office/dn124934(v=office.15).aspx
ADO.Connection Properties: http://msdn.microsoft.com/en-us/library/windows/desktop/ms681546(v=vs.85).aspx

So essentially when you use CurrentPRoject.Connection, you're using an ADO connection, which will always behave a bit differently than a DAO connection. If I had to guess, I'd say your installation of MDAC (which includes your ADO components) is current enough for ADO to "understand" the Compression syntax - hence the reason why your #3 works, even though it really won't have an impact on the JET/ACE database. DAO hasn't been updated in a while, so unless you tell DAO to use the correct syntax (by checking the ANSI box), it would not recognize that syntax, and will choke.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark DalleyInformation AnalystAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.