refresh pass-through query

[raj]

Hi experts,

I create SQL pass through queires via my code. But they are not refreshing
properly. i.e.

1st time i connect to 'A' database server and exit. 2nd time I relaunch the
application depending up the user's choice I connect to 'B' or 'C' db server.
But when i execute the pass through query via forms or design view, it always
shows me the previous server's data i.e. here server 'A'.

But in the property of the pass-through query in design view shows the
latest connection string associated with it.

e.g. though its connection string shows
ODBC;DSN=CAS;UID=CAS$SETUP;PWD=password;DBQ=CASB;

on executing the query the results are from
ODBC;DSN=CAS;UID=CAS$SETUP;PWD=password;DBQ=CASA; i.e. previous connection.

I have closed the application and reopened it, but still it always shows the
previous connected database's data.

pls help me to refresh.

 

[Stefan Hoffmann]

hi Raj,

I have closed the application and reopened it, but still it always shows the
previous connected database's data.

Closing the application before switching the connection is necessary. So
this good. After you relinked the new server, you need to "relink" the
passthrough queries. I use this simple piece of code:

Dim db As DAO.Database

Set db = CurrentDb

' for each passthrough:
db.QueryDefs.Item("passthoughQuery").Connect = _
db.TableDefs.Item("linkedTable").Connect

mfG
--> stefan <--

 

[raj]

Hi Stefan,

Thanks for your reply.

I do not have any linked table in this application. The sql pass through
queries are created in code.

In this case how to fill in this
db.QueryDefs.Item("passthoughQuery").Connect = _
db.TableDefs.Item("linkedTable").Connect

thanks, rajesh

 

[Stefan Hoffmann]

hi Rajesh,

I do not have any linked table in this application. The sql pass through
queries are created in code.

Then i have to ask some questions:

Are you using a .mdb?
How do you switch the server?
Are the passthrough queries the only one, who connect to the server?

I have to admit, that i have currently no clue about your setup.


mfG
--> stefan <--

 

[raj]

Hi Stefan,

1)Are you using a .mdb?
A: Yes, i am using mdb.
2)How do you switch the server?
I have login form which is called first. Here the user can select the
server, eg. test/production.
3) Are the passthrough queries the only one, who connect to the server?
A: No, apart from pass through queries, i make adodb connections via code.
This adodb connection is making correct connection to the user selected
server. Only the pass through query is connecting to previously used server.

Thanks, rajesh

 

[Stefan Hoffmann]

hi Rajesh,

3) Are the passthrough queries the only one, who connect to the server?
A: No, apart from pass through queries, i make adodb connections via code.
This adodb connection is making correct connection to the user selected
server. Only the pass through query is connecting to previously used server.

Okay. Then you have to set the Connect of each passthrough manually
using a connection string like www.connectionstrings.com:

Dim qdf As DAO.QueryDef

For Eache qdf In CurrentDb.QueryDefs
' if is passthrough then
qdf.Connect = ".."
' end if
Next qdf

In your OP you wrote

I have closed the application and reopened it, but still it always shows the
previous connected database's data.

You need to close Access, not only your .mdb. This is because DAO
sessions are cached, therefore the server change won't work.

mfG
--> stefan <--