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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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).
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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

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
shachoAuthor Commented:
Dale - sorry - I didn't see your post until now.  If you request a point reallocation, I'll do so.
Dale FyeOwner, Developing Solutions LLCCommented:
you're good.  Gustav answered the question, I just piled on with a bit of additional info.
shachoAuthor Commented:
OK!  Thanks again.
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.