Avatar of fabi2004
fabi2004
Flag 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?
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
fabi2004

ASKER
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...
fabi2004

ASKER
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.
Dale Fye

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
fabi2004

ASKER
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).
Dale Fye

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?
fabi2004

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
fabi2004

ASKER
I've been playing around with this code from aadconsulting.com 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
Dale Fye

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.

Dale
fabi2004

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

SELECT...
INTO tblBookings...

Open in new window


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

Open in new window


I would have to specify every column I am inserting into?
Also
If I do that, how will Access know data has changed in that linked table?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
fabi2004

ASKER
or would i use an UPDATE instead of SELECT...INTO?
Dale Fye

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?
fabi2004

ASKER
I did get this to work.  
TRUNCATE TABLE tblBookings

INSERT INTO tblBookings

SELECT...
FROM...

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
fabi2004

ASKER
oh yeah, thanks for the tip about dragging the entire columns node over.  I was doing that one by one.
Dale Fye

"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.
fabi2004

ASKER
Thank you so much for all your help Dale.  Very much appreciated!
Your help has saved me hundreds of hours of internet surfing.
fblack61
Dale Fye

Happy to be of assistance