Re: Switching ODBC Oracle databases

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

  • Re: Basic Scripts for Database Administration
    ... It is interesting finding out how people admin their databases as each ... DBA probably thinks that they have the best method. ... My recommendation is to stay away from anything Burleson related. ... servers and range in version from 9i, ...
    (comp.databases.oracle.server)
  • Re: Basic Scripts for Database Administration
    ... It is interesting finding out how people admin their databases as each ... DBA probably thinks that they have the best method. ... servers and range in version from 9i, ... tablespaces, log monitoring, load monitoring, statspack, Data Guard ...
    (comp.databases.oracle.server)
  • Re: Performance goals for DBAs
    ... I have as some stretch goals? ... I should preface this by saying that I'm not a DBA, ... manages a number of production databases. ... party apps, and if those apps make silly queries or have bad data ...
    (comp.databases.oracle.server)
  • Re: Mapped and memory based data files
    ... > I have researched a few SQL type database applications but can not ... being able to handle relational databases widens the domain ... happening, now or in the future, within the RDB packages. ... of the time had to wait for a DBA to do for them, ...
    (comp.lang.apl)
  • Re: Great Restaurant in Miami - next time you are in South Florida
    ... Zakariya, in front of databases ... cosmetic and immediate, fixs in connection with it, noticing ...
    (sci.crypt)