globally linking a sql server 2000 table

[karatelung]

i have a small access 2000 mdb on a server. a combo box in a form populates its list with a field in a table in a sql server 2000 database.

if i go through the steps and link it on one machine, the connection works, but it won't work from another workstation. i get:

ODBC -- connection to 'HFKDBSQL' failed.

when i click the arrow on the combo box.

is there any way i can make a global connection rather than just one for each individual workstation?

it would help out a lot if this is possible.

thanks.

 

[Kevin_S]

You can link the table through ODBC so that the table is in each FE that you distribute. Files - Get External Data - Link Tables will get you what you want...

HTH,
Kev

 

[karatelung]

i'm not sure how to reference the sql server database in the "DSN Name" box. the DB is called HFKDB, and it's resides on the server called SGI2.

that's where i'm stuck for now.

 

[Kevin_S]

i'm not sure how to reference the sql server database in the "DSN Name" box

DSN Name box asks you simply what you want to call the DSN, not what the name of the Database is (if I'm on the same screen as you are - if I'm not please detail each step you have taken and I can tell you where your problem is). To tell it which SQL Server database you have to first select the appropriate server from the drop-down box - after you select the server the next screen has another drop-down with a choice of SQL Server database available on the server.... is that where your having problems?

Also - make sure your creating a 'System' DSN and not a 'User' DSN as this is probably why you can't share the db with linked tables with other users....

HTH,
Kev

 

[karatelung]

first, thanks for your help.

here's everything. the mdb file is not split. it's a small db that isn't heavily used like our main one. the mdb file resides on the server. i'm opening the mdb file in access 2000 on my machine and trying to set up the odbc link.

i go to "get external data" then "link tables." then i choose ODBC as the file type and the box appears with two tabs -- file data source and machine data source.

nothing is listed in the data sources folder when i'm on the "file data source" tab. a file source is what i want, right? so that everyone accessing from their own machines can use the odbc link.

if i set up the "machine data source" odbc link, others can't automatically link, only from my machine. there's no wizard for the "file data source" like there is with "machine data source."

do i have to do this from the server on which the mdb file resides? set up the machine data source (system)?

 

[Kevin_S]

karatelung -

OK - a few more questions: I am assuming from this discussion that you application is client/server (BE in SQL Server/ FE in A'2000) If this is not the case please let me know.

Now - How many users are we talking about here and do you have access to their machines? I ask this because the best method is to use a machine ODBC to link to SQL Server. To do this an ODBC has to be set up on each machine. Now - if you only have a few users and they are local then walking them through the steps (or doing it yourself) to set up the ODBC talks about 2 minutes and could eliminate this hassle - if you have a large number of users and/or they are not local then this is not an option.

If you don't want to/ or dont have the ability to manually create the DSN then you can set up your Access database to check for the DSN on open and, if not found, create the DSN programmically on the fly! I am posting the link to the thread explaining how to accomplish this here.

(This code is really cool in that when you db main form/ splash screen/ switchboard opens the db will check for the DSN, if its there it will exit the code and resume regular operation, if the user does not have the DSN (like the first time they open the app) then the code will automatically create it... All you have to do is change the code to meet your needs by passing the DSN Name, Server Name, and Database Name in the call arguements...

HTH,
Kev

 

[karatelung]

thank you. thank you. thank you. you saved me the hassle of manually setting up the connection on 40 machines. thanks to RichardJ for posting the code.

our main db is an access FE / SQL Server BE. the upgrade wizard in access handled the connection to the sql backend for that.

the one that i was concerned with is an access mdb that is not split. workstations just access a shortcut on their desktops. it's not used heavily like the main one, so this works fine. there is one combo box on a form that got its list from a query that queried a table in our sql BE database. it only worked on my machine until now.

thanks again.

 

[Kevin_S]

no prob...

good luck the rest of the way...

Kev