brian_appliedcpu
asked on
5 SQL Tables 1 Access 2000 database: What is the best DSN solution?
We have 5 databases in SQL 2000 that are being referenced by a single Access 2003 database with link tables. What is the best way to do this with DSNs? Should we have 5 dsn's? Is there a better way of doing this besides vb and sql? We are kind of stuck with the access front end since there are tables directly stored in it.
Last Comment
ASKER
We must have over 200 tables that are linked and when connecting when using the link table manager, i have to point every singe one to the dsn and keep changing the table to use?
b
b
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks, this is great, i will give it a try.
b
b
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Dim Conn, dbPath
dbPath = "d:\myaccessfile.mdb"
Set Conn = Server.CreateObject("ADODB
Conn.Open "PROVIDER=MICROSOFT.JET.OL
Using a DSN less connection string is slightly faster then using a DSN.
If you are going to use a DSN, make sure they are system DSNs and not file or user.
There really is no best pratice approach to this. Personally I happen to like using DSN's because it makes it easy to trouble shoot outside of my app. I can use any ODBC compliant app (ie. Excel) to check the connection for problems. So I have code written in VB to setup the required environment on a station (DSNs and Shortcuts that I need) for an app.
JimD.