Link to home
Start Free TrialLog in
Avatar of fabi2004
fabi2004Flag for United States of America

asked on

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

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?
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fabi2004


Thank you Dale.  Taking a look at it now.  I'd tried setting up a DSN-less connection but I couldn't get it right.  Going to look at your article...
Dale your LTM Add-In is AMAZING.  Thank you so much for that.  I was able to specify the connection for the passthrough query and it keeps the information so I don't have to re-enter it every time.  The option to drop the schema name from the table names is a huge bonus for me because now I don't have to tweak every query, form and report that was originally built in Access.

Is there a way for me to refresh one or more table using your LTM  either through a macro or VBA?

It's my #2 from above:
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'll probably just add a button to a form to run the query and refresh the table is the last part is possible.
Glad you like it.

Not certain why you think you need to refresh the table after the query is run.  You might need to requery a form that is based upon the linked table that is updated by your query, but you would only need to do that if you are deleting records from or appending records to the table.  If you are merely updating values in that table, then you would want to refresh your form and would not need to requery it.
The stored procedure drops and recreates a table with updated data.  I need the "new" table linked so the form shows current data.  I don't think refreshing the form would work as it would still be pointing to a table that was deleted, even though it's recreated with the same table name.  

I've done some small testing and the new data doesn't show up until I refresh the table(s).
Why are you dropping the table instead of simply deleting and appending?
The only reason I can think of for dropping a table would be if the columns change from one query to another, as in a Pivot query.

Are you dropping it from SQL Server, or just deleting the linked table?
It's a long story, but the short of it is that I couldn't get an Access form to run in a reasonable time frame without converting the original query into a stored procedure that builds a table.

This form was the entire reason for moving to a SQL Server BE.  The form only runs well if I'm pulling the data from a table.  It's too slow from anything else, I tried an Access query, a SQL View and finally a table.  However, it is, at it's root, a query so that when information changes in other tables, the query needs to run to obtain current data.  The fact that it's dropping it into a table now requires the table link to to refreshed.

I hope what I wrote makes sense even if what I've done doesn't.  I tried lots of lots of other ways to get this form to work fast enough before going to this convoluted solution.

I'm just really hoping I don't have to tell the user that they have to go to the LTM to refresh the tables each time they modify data in the db to keep the form's information current.
I've been playing around with this code from for a couple of days while using the built in Access LTM.  Never got it to work right for me. But is there a way to modify it so it'll work with your LTM?

Public Function RefreshLinks(strFilename As String) As Boolean
' Refresh table links to a backend database - strFilename (full path)
' Returns True if successful. 
___Dim dbs As Database
___Dim tdf As TableDef
___' Loop through all tables in the database.
___Set dbs = CurrentDb
______For Each tdf In dbs.TableDefs
_________' If the table has a connect string, it's a linked table.
_________If Len(tdf.Connect) > 0 Then
____________tdf.Connect = ";DATABASE=" & strFilename
____________Err = 0
____________On Error Resume Next
____________tdf.RefreshLink ' Relink the table.
_______________ If Err <> 0 Then
__________________RefreshLinks = False
__________________Exit Function
_______________ End If
_________End If
______Next tdf
___RefreshLinks = True ' Relinking complete.
End Function

Open in new window

***I had to edit the code since I pasted it wrong
Still, there should be no reason to drop the table from SQL Server unless each time you run this stored procedure, it puts different columns in the table

Simply modify your stored procedure so that it truncates the table (deletes all records without logging) and then append the records to that table.  This way you won't have to drop the table and relink the table.

If it comes to it, I'll find the code I use to link tables from SQL Server, but you should not need to do this.

Right now I have
IF OBJECT_ID ('dbo.tblBookings') IS NOT NULL DROP TABLE tblBookings

INTO tblBookings...

Open in new window

You're saying I should use
TRUNCATE TABLE dbo.tblBookings

Open in new window

I would have to specify every column I am inserting into?
If I do that, how will Access know data has changed in that linked table?
or would i use an UPDATE instead of SELECT...INTO?
1. Yes,

Truncate yourTablename

Will delete all of the records from that table without creating records in your log file.

2.  As for inserting data back into that table, use:
INSERT INTO yourtablename (field list)
SELECT fieldlist FROM other table

You may be able to use:
INSERT INTO yourtablename SELECT * FROM othertable

but I doubt it. you will most likely need to include the field list.

INSERT INTO yourTablename (field1, field2, field3, ...)
SELECT Field1, field2, field3, ...
FROM other table

Did you know that if you create a new query in SQL Server, drill down on the table you want to import into and select the Columns node under the table, you can drag that node onto the query grid and it will list all of the columns for you?
I did get this to work.  

INSERT INTO tblBookings


Open in new window

I was worried because so many of the columns are calculated.  But it does work.  So, how does Access know data in the table has changed.  I can refresh the form, but will it see the new data in the table?
oh yeah, thanks for the tip about dragging the entire columns node over.  I was doing that one by one.
"how does Access know the data has changed"?

Well, what are you doing to call your stored procedure?  Whatever code is calling pass-thru query to run should also include a call to requery your form or subform if you are displaying the results in a subform.  

But as far as the table is concerned, you won't need to refresh the table.  You run the pass-thru query and the next time you open the table or create a recordset or open a form that uses that table, the new data will be displayed.
Thank you so much for all your help Dale.  Very much appreciated!
Happy to be of assistance