Solved

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

Posted on 2014-12-11
11
47 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
0
Comment
Question by:kiwi_731
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 71 total points
Comment Utility
Access does not use  VARCHAR, COMPRESSION

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


for reference
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 142 total points
Comment Utility
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 142 total points
Comment Utility
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 145 total points
Comment Utility
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
 

Author Comment

by:kiwi_731
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Expert Comment

by:Nick67
Comment Utility
So what more input do you require?
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 142 total points
Comment Utility
@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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 142 total points
Comment Utility
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 145 total points
Comment Utility
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
 

Author Comment

by:kiwi_731
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now