Re: Switching ODBC Oracle databases

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
--

"Lynn Trapp" wrote:

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" <phammond@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8ACB3830-C7EF-4709-908E-2C2B3F5A7A37@xxxxxxxxxxxxxxxx
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?






.

Relevant Pages