Connect to more than 1 SQL DB (>1 SQL backends)

[Guest]

All:
I am wondering if one can connect to more than one SQL DB. I would like to
create a front end that connects to more than one SQL Server backend.

For instance, I would like to keep the main database separate from a postal
code database. Then the postal code DB can be used for other databases and
manages separately.

Is the database file type, MDB, file the only method or can an Access
project be used, ADP? Can this be done by linking tables, preferred Access
and time constraints, or will a ADO connection need to be programmed,
preferred by .NET or VB?

If this can be completed using linked tables, please provide or point to a
step-by-step guide for linking tables without using "My Data Connectons",
thank you.

Access 2003 / SQL Server 2000 / Win Server 2003

Thank you for all your help! -r

 

[Rick Wannall]

I use an MDB and DSN-less connections to connect to as many different SQL
Servers as I want at any given moment. (I don't think you can do that with
an ADP, but someone who uses those more may correct me on that.)

Use TransferDatabase command and a table of links (tablename + connect
string) to set your connection table-by-table. You can normalize another
level to be more efficient at storing connection strings, but that's a
challenge for another post.

If you look at these topics and still have trouble, post back here.

 

[Rick Wannall]

I use an MDB and DSN-less connections to connect to as many different SQL
Servers as I want at any given moment. (I don't think you can do that with
an ADP, but someone who uses those more may correct me on that.)

Use TransferDatabase command and a table of links (tablename + connect
string) to set your connection table-by-table. You can normalize another
level to be more efficient at storing connection strings, but that's a
challenge for another post.

If you look at these topics and still have trouble, post back here.

 

[Guest]

Thank you Rick, have a great week.

That is what i was afraid of... I had used something similar to
TransferDatabase to point the conneciton to the same folder. looping through
a table is a good idea / if i do this i might as well use FORTRAN (ROTFLMHO).
Since i will have more options with the front end.

hmmm i might be able to use about 93.5% of an application i programmed about
3 years ago.

 

[Guest]

Thank you Rick, have a great week.

That is what i was afraid of... I had used something similar to
TransferDatabase to point the conneciton to the same folder. looping through
a table is a good idea / if i do this i might as well use FORTRAN (ROTFLMHO).
Since i will have more options with the front end.

hmmm i might be able to use about 93.5% of an application i programmed about
3 years ago.

 

[Pieter Wijnen]

It is recommended to use CreateTableDef instead of DoCmd.TransferDatabase
Note: I think there's a bug in it's parameter clauses
ie
Set TDef = Db.CreateTabledef(TName)
TDef.ForeignTableName="Foo"
TDef.Connect="..."
Db.Tabledefs.Append TDef
'Will Work

whereas

Set TDef = Db.CreateTabledef(TName,,"Foo","....")
Db.Tabledefs.Append TDef
'will fail in my experience

Pieter

 

[Pieter Wijnen]

It is recommended to use CreateTableDef instead of DoCmd.TransferDatabase
Note: I think there's a bug in it's parameter clauses
ie
Set TDef = Db.CreateTabledef(TName)
TDef.ForeignTableName="Foo"
TDef.Connect="..."
Db.Tabledefs.Append TDef
'Will Work

whereas

Set TDef = Db.CreateTabledef(TName,,"Foo","....")
Db.Tabledefs.Append TDef
'will fail in my experience

Pieter

 

[Rick Wannall]

No argument on using CreateTableDef vs. TransferDatabase.

Main focus is that you can use a table of connections and set them at will.
The core of it along this line:

strConnect = "myconnectioninfo"
Set tdf = dbs.TableDefs("mytablename")
tdf.Connect = strConnect
tdf.RefreshLink

If you put this inside a loop that gets you a list of tables, and
additionally connect that list to a table of connection strings, you can
control put multiple connect strings for the same table in the list of
connections, and retrieve the one you want based on a setting that I call
Mode (one of "Dev", "Live", "Test", "Train")

 

[Rick Wannall]

No argument on using CreateTableDef vs. TransferDatabase.

Main focus is that you can use a table of connections and set them at will.
The core of it along this line:

strConnect = "myconnectioninfo"
Set tdf = dbs.TableDefs("mytablename")
tdf.Connect = strConnect
tdf.RefreshLink

If you put this inside a loop that gets you a list of tables, and
additionally connect that list to a table of connection strings, you can
control put multiple connect strings for the same table in the list of
connections, and retrieve the one you want based on a setting that I call
Mode (one of "Dev", "Live", "Test", "Train")