ODBC and Union Queries

chrisamott · November 17, 2010 9:11AM

I have created an Access database and the results I am looking for appear in a union query which merges three other queries using SQL Union All Select The union query works perfectly well in Access but I cannot link the query to an Omniscope file. I receive the following error message;

""Please make sure that the columns specified in the SQL query exist in the table and you are using correct SQL syntax.

"error while attempting to read from database:[Miscrosoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.""

I have imported the results of one query and then completed a merge for the other two so I have the file I expect to end up with but I would prefer to link to a single query for refresh capabilities.

mohamed · November 17, 2010 9:17AM

Could you please post the union query or whisper it if you do not want others to see it.

mohamed · November 18, 2010 12:00PM

I have reproduced your exact SQL union query and it works. I have reproduced the same error as above "Please make sure that the columns specified in the SQL query exist in the table and you are using correct SQL syntax" When I deleted one of the fields in one of the ACCESS tables. Please make sure that all the columns that you have specified exist in the database. You could find out which field is causing this issue by breaking down the SQL statement into smaller chunks (divide and conquer) or separating out the union statement to include only two tables so you could narrow down which table has the missing field(s).

mohamed · November 19, 2010 5:53PM

I hope my suggestion has helped resolving this issue.

chrisamott · November 23, 2010 1:54PM

mohamed, I sent a reply in response to the email notification I received from your post. I was still having difficulty and no amount of checking and deleting helped I'm afraid, until this morning when I had an epiphany. I wondered if the title/header I had given the first column, "Query" was causing some kind of reserved word conflict. I changed it to "QueryType" and while the upload hasn't completed yet, the error didn't present itself as it had in the past.

FYI I am using Office 2003 and Omniscope 2.5b

Thanks

mohamed · November 24, 2010 10:45AM

I am glad to hear that you managed to get to the bottom of the problem.

I was using Microsoft ACCESS 2010 and Omniscope 2.6. I did not get any errors when running the statement containing "Query" as the column name, it is most likely it is the Office version that is causing this different results.

Welcome!

Categories

Ideas Parade