Setting up DSN/ODBC connection on user machines

I need to distribute an access front end, using MySQL (or Sql Server perhaps) as a back end.  How can I automate the process of establishing the DSN connection on each user machine (more than 300 spread over multiple parts of the a huge company with different networks).  It appears I need to go to Adminstrative tools etc and actually enter a userid/password for the user.
Anyone have specific expertise with distributing Access Front end across many users without having to go and configure each user workstation (I understand we're stuck with installing the ODBC driver).
(I can use either MySQL or SQL Server as the back end - If SQl Server, I must rent a virtual machine and install/configure myself - MYSQL already configured and running).

Thanks
LVL 4
Keyboard CowboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
I use DSN-less connections, which avoid the issues associated with setting up DSN.

I maintain a table of the names of the SQL Server tables and the local name that I want to give each of those tables.  Then I loop through that list of table names and refresh the links or create the links to them using code similar to:
strDriver = "SQL Server Native Client 11.0"
cnn = "ODBC;DRIVER=" & strDriver _
       & ";SERVER=YourServerNameGoesHereTrusted_Connection=Yes;APP=SSMA" _
       & ";DATABASE=YourDatabaseNameGoesHere"

Dim strSQL as string 
Dim rs as DAO.Recordset
Dim tdf as DAO.Tabledef
strSQL = "SELECT DISTINCT [NAME] FROM mSysObjects WHERE Connect <> ''"
set rs = currentdb.OpenRecordset(strSQL, dbOpenSnapshot, dbFailonError)
While not rs.eof
    set tdf = Currentdb.Tabledefs(rs!Name
    tdf.Connect = cnn
    tdf.RefreshLink
    rs.MoveNext
Wend
rs.close
set rs = Nothing

Open in new window

With code similar to this, you only need to setup the connection once, when you create the application.  You might want to add a button to refresh these links on the Admin page of your application, and provide a method to change the server or database name, in case you move the backend, but if I did that, I'd probably do the updates myself and push out a new version of the application to all of my users.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keyboard CowboyAuthor Commented:
Oh man. GREAT idea.  I'll try it. Thanks.
Does this avoid users having to cI figure via administrative tools.  New at this. Thanks.
0
Keyboard CowboyAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Keyboard Cowboy's comment #a40680419

for the following reason:

none
0
Dale FyeCommented:
I think you accepted the wrong solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.