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?
LVL 1
fabi2004CIOAsked:
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.

Dale FyeOwner, Developing Solutions LLCCommented:
I have a new Linked Table Manager for Access and SQL Server add-in which is described and available for download in this article.

I would replace the DSN connections with DSN-less connections, which my add-in will do.
0

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
fabi2004CIOAuthor Commented:
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...
0
fabi2004CIOAuthor Commented:
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.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
fabi2004CIOAuthor Commented:
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).
0
Dale FyeOwner, Developing Solutions LLCCommented:
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?
0
fabi2004CIOAuthor Commented:
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.
0
fabi2004CIOAuthor Commented:
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
0
Dale FyeOwner, Developing Solutions LLCCommented:
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
0
fabi2004CIOAuthor Commented:
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?
0
fabi2004CIOAuthor Commented:
or would i use an UPDATE instead of SELECT...INTO?
0
Dale FyeOwner, Developing Solutions LLCCommented:
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?
0
fabi2004CIOAuthor Commented:
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?
0
fabi2004CIOAuthor Commented:
oh yeah, thanks for the tip about dragging the entire columns node over.  I was doing that one by one.
0
Dale FyeOwner, Developing Solutions LLCCommented:
"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.
0
fabi2004CIOAuthor Commented:
Thank you so much for all your help Dale.  Very much appreciated!
0
Dale FyeOwner, Developing Solutions LLCCommented:
Happy to be of assistance
0
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.