By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,743 Members | 1,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,743 IT Pros & Developers. It's quick & easy.

script to change passthrough connection string

P: 2
Hellow,

i am quite new to access, but i am searching for a visual basic script to change the connection string for all the passthrough queries which are in my access database. These passthrough queries have an ODBC datasource to an oracle database.

I have about 80 passthrough queries, so i do not want to change one by one, therefor i need the script.

Thanks in advance for your reactions...
Nov 4 '07 #1
Share this Question
Share on Google+
2 Replies


Jim Doherty
Expert 100+
P: 897
Hellow,

i am quite new to access, but i am searching for a visual basic script to change the connection string for all the passthrough queries which are in my access database. These passthrough queries have an ODBC datasource to an oracle database.

I have about 80 passthrough queries, so i do not want to change one by one, therefor i need the script.

Thanks in advance for your reactions...

Paste this sub procedure into a new module in Access and save the module and then in the immediate window (menubar...view...immedate window) simply type changedsn and hit enter.

You will see a list of your existing DSN strings derived from the Connect property of the QueryDefs collection. You will have to uncomment the relevant line below when you have entered the Oracle DSN you intend to use in order to invoke any change obviously!

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub ChangeDSN()
  3. Dim db As DAO.Database
  4. Dim qry As DAO.QueryDef
  5. Set db = CurrentDb
  6. For Each qry In db.QueryDefs
  7.     If Left(qry.Connect, 4) = "ODBC" Then
  8.     'Print the name of the existing DSN string
  9.     Debug.Print "Existing DSN is " & qry.Connect
  10.  
  11.     'Enter your new dsn in the next line and uncomment it to run
  12.     'qry.Connect = "ODBC;DSN=dseOracle;UID=demo;PWD=demo;"
  13.  
  14.     'Print what it is after the amendment
  15.     Debug.Print "Changed DSN to " & qry.Connect
  16.  
  17.     End If
  18. Next qry
  19. End Sub
  20.  

Regards

Jim :)
Nov 5 '07 #2

P: 2
TjS
Thanks!

I had already a piece of code, but this completes it totally.
Nov 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.