troubleshooting Question

How to avoid a pass-through query asking for DSN connection every time it's run

Avatar of fabi2004
fabi2004Flag for United States of America asked on
Microsoft Access
17 Comments1 Solution68 ViewsLast Modified:
I have Access linked to a SQL server db.  I'm trying to use a pass-through query to run a stored procedure.  Every time I  run the query, I get a pop-up window asking to "Select Data Source".  I have a DSN File Data Source.  After I select it, the query runs fine.  Next time I run it, same pop-up.  I've tried both a connection with Windows Authentication and one with a SQL Server login.  Same problem.

I assumed that since Access was connected to that SQL db via the linked tables, I wouldn't have to reconnect for a query.  Looking through some Internet posts, it seems that I need to specify the connection for each pass-through query. Is that right?

I'll need this query to run at start up and again via the on_click event on a form's button.

Two things I need help figuring out...

1. I need to either (a) provide the DSN file automatically for the pop-up or (b) avoid the pop-up prompt by having the connection already recognized

2. After the query runs, I need to refresh the affected table automatically / or all the tables if that's' easier, without needing to go to the Linked Table Manager.

I've played around with some code I found online for a couple of days, but I'm not getting it right.  Can someone help me figure this out?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros