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?