SQL back end for Access

[Rod]

I've set up an Access Database with an SQL back end using SQL server on SBS
2003. My clients cannot connect unless I manually set up a dsn on each
machine. Is there a way to rollout a dsn on every machine without setting it
up on each client OR should I have configured my back end connection
differently?

 

[Jeff Boyce]

Rod

One approach would be to create a DSN on the network they all share, then
use THAT DSN when you build your distributable front-end copy.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rod]

I know how to create the DSN on the server but how do I make it accessible
for them to share?

 

[Albert D. Kallal]

Use a dsn less conneciton....

that way, you can your code conneciton, and set things up...

here is how:

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html

 

[Jeff Boyce]

Rod

Albert offers a pointer to a DSN-less approach. This is a common
alternative to using a DSN connection.

The one down-side I can see is that each FE (front-end) might need to be
updated if any of the specifics in the code changed (e.g., the network gurus
changed servernames, etc.).

If you create a DSN on the server, you can:
* open a FE and link to the BE tables, using the server's DSN (use the File
DSN tab, select the Entire Network in the Data Sources drop-down, navigate
to the server, select the DSN) This uses the UNC path rather than a
named-share path -- not everyone might have the same sharename!

Note that if there's a change in the particulars (those pesky network
folks!), you can replace the single DSN on the server and all FEs should
automatically have the new connection (assuming, of course, that you keep
the same name for the DSN).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rod]

Thanks for your reply, I've read through the code examples but I don't
exactly know where to enter the code in access to make the link. Perhaps this
is over my head?

 

[Albert D. Kallal]

Thanks for your reply, I've read through the code examples but I don't
exactly know where to enter the code in access to make the link. Perhaps
this
is over my head?

It is not too difficult. It is assumed that you do have some coding
abilities (I suspect you do, since it sounds like you have a successful
application, and they usually do involve some code).

The sample code in that link should be placed in a standard code module.
(call the module MyReLinkCode). Make sure you save it..and for sure before
you save..do a debug->compile in the code menu.

You can then run the code by running the code. to quote that link:

<quote>
If you've added this code in a module, you can invoke it from the Debug
Window (Ctrl-G) by typing:

FixConnections "MyServer", "MyDatabase"
and hitting Enter (where, of course, you replace MyServer and MyDatabase by
whatever's appropriate).

</quote>

So, in place of typing the above in, you would create some start-up code
that get called from your first form, or the "main menu" form that
loads...it could to the re-linking to sql server for you.

 

[A a r o n K e m p f]

you need A c c e s s D a t a P r o j e c t s