We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
The same Access query text is valid in some places and invalid in others
Posted on 2014-12-11
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.
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
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.
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
[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
3
2
2- +2
11 Comments
Active today
Access does not use VARCHAR, COMPRESSION
see this link Microsoft Access: DDL Code ExamplesMicrosoft Access: DDL Code Examples
for reference
see this link Microsoft Access: DDL Code ExamplesMicrosoft Access: DDL Code Examples
for reference
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.
.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
It's all doable -- it just getting the syntax straight.
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.
.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
It's all doable -- it just getting the syntax straight.
Active 1 day ago
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.
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.
Active today
Using the CurrentProperty.Connection
http://msdn.microsoft.com/en-us/library/office/ff821478(v=office.15).aspx
http://msdn.microsoft.com/en-us/library/office/ff821478(v=office.15).aspx
.Connection implies that the string is being executed elsewhereNo, that's just the underlying connection Access has created to the database. You're still working on the tables, either linked or local.
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
@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
@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!
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!
Active 1 day ago
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.
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.
Active today
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,
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
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
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month13 days, 14 hours left to enroll
656 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.