Pass Through Query and updating on the fly

[scottsanpedro]

Hi,
I need to be able to change the properties on a pass through query, in fact a load of them.
As you can see, I have the code and all pass through 's get updated correctly.

strConnect = "ODBC;DRIVER=SQL Server;SERVER=" & ComputerName & ";DATABASE=RUILS_Temp;Uid=" & strUID & ";Pwd=" & strPWD
 
Set db = CurrentDb
 
For Each q In db.QueryDefs
 If q.Type = dbQSQLPassThrough Then
    q.Connect = strConnect
 End If
Next

BUT it seems to require a re-start of the Access 2007 database to commit these, in fact in my case it requires 2 re-starts as I have a login process.
I did read somewhere that the ODBC properies of a pass through are written to cache when the DB is open.
Is this correct or is there a way around this?

I'm doing this as I have multi companies using the same database and need to run a function on SQL Server views using SUSER_SNAME()

Thanks

Scott

 

[smedvid]

I've not used MS Access in a couple of years; but as I recall in the past...

I actually deleted and rebuilt pass-through queries on the fly and had no problems. I did need a "Refresh" though. Look to see if there is a QueryDef Refresh or Database Refresh that will perform the commit for your purposes.

In the mean-time I will see if I can find my code; which may be on an old CD Archive somewhere...

Good Luck,


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...