Completely OT: Access linking to SQL Server

[Kinvig]

Apols in advance! Long shot but....

Been a "while" since I last used MS Access, & I@ve forgotten everything.

Got an Access db that links to a table in a sql server.

In the sql server, I've created permissions for a windows user group to access the table in the database.

It all works fine if a user conects to the database if I create a File DSN (using TCP/IP).

The problem with a file DSN is that I then have to copy that file to every users machine that wants to acces the table...and that's not good if they move to another machine or have one of the weekly rebuilds that we seem to be doing.

So, this (finally) is the question:

Can I store all the odbc connection information in a module in the Access database? Or am I going to be grumpy going round every machine copying the File DSN & making sure that we're using TCP/IP as opposed to the default Named Pipes?


Dirk (stroking lucky rabbits paw)

[mysteryjimbo]

You can create a dynamic DSN on every machine that can be used for every access DB you ever create. Then store the connection details within the access file.

You can also hard code in vb some connection details but that defeats the object of rapid development in Access.

Im not the expert on Access mind.

[Kinvig]

Yeah, my options of limited use appear to be:

1. Use the File DSN as it is
2. Convert to adp...
3. See of I can do something similar to an asp connecting to a db table then importing the results into an access table
4. A DTS that imports the data into an access table periodically. (Possibly not good if there's a user in the db at the time).

As it's OLAP functionality that's required, I think Option 4 is the winner....

Of course, Option 5. Get a new job, my replacement will figure it out. ;o)


Thanks for your help!