Solved

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

Posted on 2014-12-11

7 solutions

Medium Priority

66 Views

Last Modified: 2016-07-12

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

Comment

Question by:kiwi_731

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

11 Comments

LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)

Rey Obrero (Capricorn1) earned 284 total points

ID: 404947652014-12-11

Access does not use VARCHAR, COMPRESSION

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

for reference

LVL 26

Assisted Solution

by:Nick67

Nick67 earned 568 total points

ID: 404948722014-12-11

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.

LVL 38

Assisted Solution

by:PatHartman

PatHartman earned 568 total points

ID: 404950692014-12-11

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.

LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 580 total points

ID: 404959062014-12-12

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.

Author Comment

by:kiwi_731

ID: 404964792014-12-12

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

LVL 26

Expert Comment

by:Nick67

ID: 404964872014-12-12

So what more input do you require?

LVL 26

Assisted Solution

by:Nick67

Nick67 earned 568 total points

ID: 404965052014-12-12

@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!

LVL 38

Assisted Solution

by:PatHartman

PatHartman earned 568 total points

ID: 404968642014-12-12

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.

LVL 85

Accepted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 580 total points

ID: 404978752014-12-13

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.

Author Comment

by:kiwi_731

ID: 404999482014-12-15

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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Delivering a Word Template for Use in Merging Data from Access

Article by: Helen

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…

Create Windows Phone Colour Palette and Selector using WithEvents

Article by: Gustav

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.

Using a Criteria Form with an Access Report

Video by: TechMommy

Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

Polish Reports in Access

Video by: crystal

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…