Access Asking for Connection Parameters with Passthrough Query

I have created a link to a MySQL table in an Access DB.
I can run regular queries on the link with no problem.
If I try to run a passthrough query on it, however, Access asks me to select the data source (even though the connection parameters are stored in the link).
I'm not sure why and I don't think this is expected behavior for Access.
Can someone help me troubleshoot?

Mike

f
shachoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Your query needs a connection string. How should it otherwise know where to pass your query?

The property ODBCconnection has to hold a string starting with ODBC; as here:

ODBC;Driver=YourMySqlDriver;DATABASE=YourDatabase;SERVER=HostnameOrIpAddress;User=Username;
You can open one of your linked tables in design view and study the properties. You can probably copy and paste the full connection string from this to your query.

/gustav
0
shachoAuthor Commented:
Yes - it can be done this way.  But if I do it that way, then it is not referencing the linked table.  I could delete the link and the passthrough would still work.  In fact, this is what I'm doing now.  I expected that Access would use the linked table to make the connection, but I guess Access doesn't look at the query contents at all if it's a passthrough.  If so, then the behavior is expected.
0
Dale FyeCommented:
Pass-through queries are just that, they pass the query (or any valid SQL) from Access directly to you MySQL database, where the SQL is processed.  This requires the connection string because, as Gustav indicated, the query does not know anything about the tables involved.  I generally get the connection string by typing the following in the immediate window:

?Currentdb.Tabledefs("SomeLinkedTableName").Connect

I then paste this into the connection string in the Pass-Through query.  Don't forget to set the queries ReturnsRecords to the the appropriate value (Yes or No).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
> .. Access doesn't look at the query contents at all if it's a passthrough.

True. You could write plain nonsense and Access wouldn't care.

It is isolated server-side code, thus the server has no idea of what tables could exist or be linked in the calling Access application.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shachoAuthor Commented:
Makes sense.  Thanks Gustav!

Mike
0
shachoAuthor Commented:
Dale - sorry - I didn't see your post until now.  If you request a point reallocation, I'll do so.
0
Dale FyeCommented:
you're good.  Gustav answered the question, I just piled on with a bit of additional info.
0
shachoAuthor Commented:
OK!  Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.