Refreshing the link on stored procedure

M

Michel Elie

I have a little problem that I can't resolve.

History:
I have built an interface on Access 2002 that is link
too a database on SQL 2000. I have a 2 copies of the same
database on two differents servers.
So, I have two structurally identical databases on
different server but the data inside the database are not
the same.
I have created a function that can change my User DSN
on the fly from server X to server Y or from server Y to
server X.

Problem:
When I change from server X to server Y all my tables,
views and queries see the change of server and I see the
data from server Y and that's perfect.
But my only little problem is that the stored procedure
don't see the change of server and I still see the data
from server X.
The only way I have found to refresh the change of
server for the stored procedure is to close the
application and Access and reopen it.

Question:
Who can I Refresh or tell my stored procedure that I
have made a change of server whitout closing everything.


Thanks!!!
 
D

Duane Hookom

Is this an MDB or ADP? How are you changing the DSN on the fly? Are you
changing the connection string of the linked tables? If so, you have to
change the same for any pass-through queries.
 
M

Michel Elie

It's an .MDB and the function change the DSN directly in
the registry on demand.
The DSN name and database name stay the same, I just
change the server name in the DSN. It work very well but
my only problem is those stored procedures, I have to
close and reopen Access to clear that little problem.
 
D

Duane Hookom

MDBs don't have stored procedures. Are you creating a connection in code or
using a pass-through query?
 
M

Michel Elie

Your right, I mix up the term here. I met a pass-throught
query. So my pass-trought queries don't seem to see the
change of server right away, but all my saved query and
linked table see the change of server.

WHY!!!!!!
 
D

Duane Hookom

Check the Connection String of the pass-through queries. They probably
reference the wrong SQL Server.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Resource Pool Project 2016 Standalone - how to remove reference to previously Shared, now deleted, files 0
ODBC error 100 when accessing Bookkeeper version 15 software (accounting software using Access dbase) 0
ODBC Error en Organigrama en Visio. 0
Copy and Paste Special in Excel #REF error 1
Create a dynamic master file 1
MS Query 7
how to code a button to export results of stored procedure to excel file 0
Synchronization problems while moving folders to new email 1

Top