Uziel
asked on
ODBC and Union Queries
Ok, I have 3 tables 1) New, 2) Current, and 3) History. All 3 have identical data structures. I am trying to do a Union query in Access, but I get the following error:
ODBC--call failed.
[Timberline][Timberline ODBC Dri(#0)[Timberline][Timber line ODBC Driver](#0)
The funny thing is I can get the query to work fine in Crystal Reports SQL Query Designer. I have tried to restart everything, kicked everyone out of the DB, Refreshed the links to the Timberline tables, yet nothing works. Any ideas? Below is my code, notice how terribly basic it is...
SELECT *
FROM NEW_GLT_TRANSACTION
UNION SELECT *
FROM HISTORY_GLT_TRANSACTION
ODBC--call failed.
[Timberline][Timberline ODBC Dri(#0)[Timberline][Timber
The funny thing is I can get the query to work fine in Crystal Reports SQL Query Designer. I have tried to restart everything, kicked everyone out of the DB, Refreshed the links to the Timberline tables, yet nothing works. Any ideas? Below is my code, notice how terribly basic it is...
SELECT *
FROM NEW_GLT_TRANSACTION
UNION SELECT *
FROM HISTORY_GLT_TRANSACTION
ASKER
Yes, I started with just one field name and reverted to the * hoping it would alleviate any errors.
What is weird is that it DID work...once. Then I started changing it to exactly what I wanted and stopped working. It makes no sense. Also, it does work in Crystal SQL, just not access.
What is weird is that it DID work...once. Then I started changing it to exactly what I wanted and stopped working. It makes no sense. Also, it does work in Crystal SQL, just not access.
ASKER
Yes, I started with just one field name and reverted to the * hoping it would alleviate any errors.
What is weird is that it DID work...once. Then I started changing it to exactly what I wanted and stopped working. It makes no sense. Also, it does work in Crystal SQL, just not access.
What is weird is that it DID work...once. Then I started changing it to exactly what I wanted and stopped working. It makes no sense. Also, it does work in Crystal SQL, just not access.
ok. we'll put it to the test then! :)
i am assuming that your query is based off of "linked" tables, right? instead, use a passthrough query. passthrough queries are run DIRECTLY against the server with no (very little) Access manipulation.
if you need help with creating a passthrough query, let me know. otherwise, try that and see what happens.
dovholuk
i am assuming that your query is based off of "linked" tables, right? instead, use a passthrough query. passthrough queries are run DIRECTLY against the server with no (very little) Access manipulation.
if you need help with creating a passthrough query, let me know. otherwise, try that and see what happens.
dovholuk
ASKER
I've never done a pass through, and the documentation is not very good. Any chance I could get an example to run off? I'm assuming this will allow me to plug the information where I want it, but will it let me do a union as well?
Btw, thanks so much for the help.
Btw, thanks so much for the help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
devtha is mostly correct. you need to be careful with your syntax when running a passthrough query as you will need to use ansi sql as opposed to access sql. so getting the syntax just right might take effort.
but to try it out, you create a new query. go to the query menu, choose "Sql specific" and then choose "pass-through". you will be forced to TYPE in your sql, so as a trial try something like "SELECT * FROM SomeTableName" and then run the query to see if it works.
oh yes, union is an ansi sql command, so that should work fine...
good luck, let us know if you need any more help.
dovholuk
but to try it out, you create a new query. go to the query menu, choose "Sql specific" and then choose "pass-through". you will be forced to TYPE in your sql, so as a trial try something like "SELECT * FROM SomeTableName" and then run the query to see if it works.
oh yes, union is an ansi sql command, so that should work fine...
good luck, let us know if you need any more help.
dovholuk
Uziel:
You have several open questions:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20155619
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20113753
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20104730
To assist you in your cleanup, I'm providing the following guidelines:
1. Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.
2. Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.
3. When grading, be sure to read:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.
4. Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.
5. If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.
Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!
thanks!
amp
community support moderator
1/7
You have several open questions:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20155619
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20113753
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20104730
To assist you in your cleanup, I'm providing the following guidelines:
1. Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.
2. Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.
3. When grading, be sure to read:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.
4. Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.
5. If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.
Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!
thanks!
amp
community support moderator
1/7
Per recommendation, force-accepted by
Netminder
CS Moderator
dovholuk: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20290223
Netminder
CS Moderator
dovholuk: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20290223
-i assume the ODBC driver is the "latest", correct?
just some thoughts...
dovholuk