Re: Switching ODBC Oracle databases

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.
--
Can't we all just get along?


"Lynn Trapp" wrote:

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" <phammond@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0203AD1C-4934-4664-A6DF-1F7DC54DD147@xxxxxxxxxxxxxxxx
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

--
Can't we all just get along?



.