SQL server table connection

[sri via AccessMonster.com]

Hello,

I have a problem which i need some advice. I have a mdb connected to SQL
server 2000 and that server has 2 databases. I followed the below link to
create DSN for connecting those databases using a form in my mdb. When I
switch between those databases a seperate set of new tables are linked
everytime and they are numbered 1,2,3..so on. I tried goin to Linked table
manager in tools option and select the system DSN in machine datasource and
tried to link all tables to that particular database in sql server but they
wont change at one shot instead I have to repeat the same process for all
tables in the database. It will be very helpful if some have a clue for
this problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;316752

 

[Sylvain Lafontaine]

This newsgroup is about ADP and not about MDB.

You should ask your question for example in the m.p.access.internet
newsgroup; where someone will give you an example of this old piece of VBA
code used to relink all linked tables with a new connection string.

I would give it to you if I find it but no luck on my side.

 

[sri via AccessMonster.com]

Thanks for info. Does the newsgroup you mentioned is the same website as
this? I didnt know if this topic is about ADP. if a access application is
connected to SQL server will be called a ADP? Thanks for info.
Sri.

 

[Sylvain Lafontaine]

There are two kinds of Access application connected to a SQL-Server: a MDB
file with linked tables or an Active Data Project (ADP), with .ADP as the
suffix for the file name instead of MDB.

These two types have very different architectures.

m.p.access.inernet is simply an abbreviation for
microsoft.public.access.internet .

 

[sri via AccessMonster.com]

I appreciate for info. Now I know which group my application falls into. I
was confident that its not ADP as we only connect to linked tables from sql
server into this MDB. What I was facing issue past few weeks is trouble
connecting my tables to different databases in sql server. Eventually my
MDB has to connect to different databases in different servers. For now i
am tryin to figure out switching between databases in 1 server. if that
works i thought to move to 2 server addition. thanks.

 

[srikanth puppala via AccessMonster.com]

i got a great solution for this problem at a website below.
This one prefectly works if you are connecting to SQL server using access
and linking tables between databases and servers. awesome code.
http://www.accessmvp.com/djsteele/DSNLessLinks.html
thanks doug.