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

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10091-connect-more-than-1-sql-db.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091

Thank you for all your help! -r

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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.)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091

Use TransferDatabase command and a table of links (tablename + connect
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091
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.

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).
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091
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.

"Rick Wannall" wrote:

> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091
>
>
>

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" wrote in message
newsIocg.75531$_S7.63910@newssvr14.news.prodigy. com...
>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.)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091
>
> 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.
>
>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10091
connections, and retrieve the one you want based on a setting that I call
Mode (one of "Dev", "Live", "Test", "Train")