Switching ODBC Oracle databases

[Paul Hammond]

We have 10 or more Oracle db all structured the same way. I want to be able
to switch between them based on the value in a combo box. I've already set
up the ODBC connections. Here's the code I am using.

strDB = Me.cboPub.Column(2)
strConnect = "ODBC;DSN=" & strDB &
";UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" _
& "SERVER=" & strDB

DoCmd.DeleteObject acTable, "ADDR_VW_ADDRESS"

DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable,
"ADDR.VW_ADDRESS", "ADDR_VW_ADDRESS"

This repeats for several more tables.

This fails until I manually connect to each database once. After that I
can switch between all the databases using this code.

How do avoid this manual process. Am I missing something in my connection
string?

The code

 

[Lynn Trapp]

As an Oracle developer, I'm puzzled why you have "10 or more Oracle"
databases. Do you mean tables? If so, why do you need 10 or more Oracle
tables that are structured the same way? Did your Oracle DBA let you create
multiple tables with the same structure?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Paul Hammond]

In Brief here goes.
We are a newspaper publishing company with a papers grouped in 10 Regions,
hence 10 databases, not my plan or design. There are differences in the
code tables, Address, Phone, Subscriber, and Transaction tables are kept of
more manageable sizes. Of course, the disadvantage is I have to switch
betweeen databases to gather data for my projects.

Myself, I'm a read only kind a guy and couldn't create a table if I wanted to.

 

[Lynn Trapp]

I believe your DBA should be able to create an "alias" for 9 of the
databases in the 10th and then you would only have to create the single ODBC
connection to that one. That way the tables in the other databases would be
visible to you. I've never actually tried that but it's worth talking to
your DBA about. Another possibility is for your DBA to create materialized
views from the tables in each of the databases. You could then access them
without going through all the hoops you are having to go through. Another
way might also be to create a persistent connection to each database when
your database first opens.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Paul Hammond]

Not sure the DBA's have the time or inclination to do those things.

Persistent connection? How would I set that up?

Also is there something in the connection string I am using that causes ODBC
connection to fail. Seems like if I had this correctly, It should log in.
Same method works fine for a SQL Server connection I am using elsewhere.

Here's what I am using now.

ODBC;DRIVER={Microsoft ODBC for
Oracle};DSN=MyDSN;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;SERVER=MyServer;Type=User

Paul
--