Access 2007

[Peter Bellamy]

Has something changed in the way queries are written in 2007?
I am using it in 2000-2003 mode as I am working on someone else's database.

I contructed a simple query using design view to Select a record from a table and Access complains about a syntax error.
Syntax error in query expression'((Parameters.param_index)=5))'
As a check I used the Find Duplicate query wizard, and it too gets a syntax error?
I have done all this hundreds of times with Access 2000, what is going on,?

pnb

 

[vbaInet]

Are you running the database in 2007 or did you convert from 2000 to 2007?

Can you paste the SQL of the query?

 

[Peter Bellamy]

I am running it in 2007.

I wanted to look at the SQL as well but it won't let me, it just complains about the Syntax error when I try to open the SQL!

 

[boblarson]

You can get the SQL by using code:

Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = CurrentDb.QueryDefs("YourQueryNameHere")

strSQL = qdf.SQL

Debug.Print strSQL

qdf.Close

You can also use that to set the correct SQL so you can open it.

But, it might be corruption so if you import everything into a new, blank ACCDB shell, what happens?

 

[boblarson]

And just a note - some things which did work in previous versions do not in 2007 because 2007 did get a little more stringent on some things so if you have some "not so tight coding" you might find that it has a problem in 2007.

 

[Peter Bellamy]

Thanks Bob, I had read 2007 was stricter.

It will not even let me save the query to use your vba to debug print the code.
The database compacts and repairs ok so could it still be corruption?

I have tried copying again from the db from CD I was given, but same result.
I have even exported the Table to a text file, imported it and created the query again, just the same !!
It is just a simple, small table !
I am just trying to select the record where param_current is Yes.
I have attached the text file.

pnb

 

[boblarson]

The code I gave you was to extract the SQL into the Immediate Window so you could copy it here. If you modify it and want to save it back you would use:

Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = CurrentDb.QueryDefs("YourQueryNameHere")

strSQL = "Select Whatever, Whoever, Etc FROM TableName"

qdf.SQL = strSQL

qdf.Close

 

[Peter Bellamy]

I can't save the troublesome query so I can only manually write it as SQL and see if it still errors.

 

[boblarson]

I can't save the troublesome query so I can only manually write it as SQL and see if it still errors.

How have you tried saving it? Are you using my code?

 

[Peter Bellamy]

I created this on a button on a form
"StrQry = "SELECT Parameters.* INTO [CurrentParam] FROM [Parameters] WHERE [param_current] = Yes ;"
DoCmd.RunSQL StrQry"

And it worked fine ????
I am totally confused ??????

pnb

 

[vbaInet]

Which is why we would like to see the sql of your query.

 

[Peter Bellamy]

Happy to provide but if I cannot save the query and give it a name, how can I?

 

[vbaInet]

Are you able to post your db?

 

[datAdrenaline]

>> Happy to provide but if I cannot save the query and give it a name, how can I? <<

Create a new query cancel the add tables dialog, then switch to SQL View, then type out your intended SQL statement ... copy/paste and post ... but try to save it first to see if the manual creation of the statement works.

 

[Peter Bellamy]

The dB is too large to post. You can recreate my table from the text file posted

If I write in the query builder :
"SELECT [Parameters].*
FROM [Parameters]
WHERE [param_current] = Yes;"
It works.
I then checked it in design view and it is as expected.

If I then create the query in design view, copying the first working (SQL) one.
It doesn't ??
If I then go back to the first, SQL based one, it then also fails with the syntax error.

Totally bizare !!

 

[vbaInet]

Alright, lets get you to change the name of the TABLE to something else. The name "Parameters" is a reserved name and shouldn't be used. Maybe change it to Parameters_Test

 

[datAdrenaline]

Definately a poor choice for a table name, I was hoping that it was just a "sample" name for posting.

Changing the table name to something different will yeild success with out a doubt!

 

[datAdrenaline]

Note ...

If for some reason you can not change your table name, then you should alias the Parameters table in ALL of your Query objects and recordsources ...

SELECT p.*
FROM [Parameters] As p
WHERE [param_current] = Yes;

Or ... create a query of the Parameters table (alias it if you filter or sort it) and use that Query object instead of the table.

Just to let you know, that the query parser was modified in A2007 and the reason the save fails is because once the Access applications Query Object command parser/optimizer parses the SQL, your WHERE clause becomes:

WHERE (((Parameters.param_current)=Yes));

But if a policy of "always wrap in square brackets" were applied by the parser, you would get:

WHERE ((([Parameters].[param_current])=Yes));

Which would then allow Access to save the Query object without error since the square brakets would distinguish the table name from SQL syntax key word of PARAMETERS.

 

[Peter Bellamy]

Yes, that was it.
Renamed and it all worked as expected
Odd it worked with hand written SQL though?

Thanks

 

[datAdrenaline]

>> Odd it worked with hand written SQL though? <<

Well ... not really ... You can actually save your Query in its original form:

SELECT [Parameters].*
FROM [Parameters]
WHERE [param_current] = Yes;

IF you Save it in SQL View --- and never try to save from Design View.

The Design Grid is a UI the helps build the SQL statement, so if you go through the grid, you will feel the effects of the parser to a much greater extent since the parser has to convert the contents of the grid into a SQL statement/command, where as in the SQL View you are taking on that task yourself.