ODBC dilemma

I have an Access 2010 front end application with a SQL Server back end with an ODBC connection.  The front end links to the back end tables and there are a few passthrough qeuries for inserts, updates, and deletes.  Each user ( around 5 in all) has a copy of the front end on their machine.  I am looking for the best way to configure the ODBC connection.  I am still trying to fully understand ODBC's.

My goal is to have a connection that allows me to make modification to the front end and post it without having to mess with the connection again.  Currently, I am using a file DSN with Windows authentication.  With the current connection, I had to reconfigure each machine for the front end.  I thought I could avoid this with the file DSN.  I don't know what will happen when I post a new version of the front end yet.

What is the best connection configuration?  Machine data source, file data source with Windows authentication, file data source with SQL authentication?  

Thanks for your time.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

I always create my DSNs dynamically with VBA, within the frontend.

See here:

Dale FyeOwner, Developing Solutions LLCCommented:
I generally go with a DSN-less connection to the server.

This allows be to relatively easily change the connection string from one database (development) to (production) and back.

Here is some sample code from one of the Access MVPs.

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
I must admit, DSN-less (although slightly more fiddly) is the best way to go for at least a handful of reasons I can think of. I find myself needing a direct connection for the odd thing here and there.

One example; when dealing with very long strings ODBC has a lower limit than SQL can actually hold.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ste5anSenior DeveloperCommented:
The best approach is a DSN-less connection. Thus you don't need to fiddle with the clients as long as the necessary driver is installed.

Just look into those threads.
Eric ShermanAccountant/DeveloperCommented:
Here is how I link tables using DSN-Less connections to MySQL.  Just change the ODBC Driver name to match your SQL Server driver and it will work the same.

1.) I use a local table in the front end named Linked_Server_Tbls to store the name and description of each linked MySQL table.
2.) I use another (1 Record ) local table in the front end named Setup_General to store all types of information about the app including the connection string parameters.  The VBA code will lookup those parameters on demand.  This table can only have one record.

You can also drop the last parameter Option = 35 .... This is specific to MySQL only.

Function LinkODBCTables()

Dim strMySQLServerHost As String
Dim strMySQLDatabase As String
Dim strMySQLLogin As String
Dim strMySQLPswd As String
Dim strMySQLConn As String
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM LINKED_SERVER_TBLS;", dbOpenDynaset)

'Get connection string parameters
strMySQLServerHost = DLookup("[MySQL_SERVERHOST]", "SetupGeneral")
strMySQLDatabase = DLookup("[MySQL_DATABASE]", "SetupGeneral")
strMySQLLogin = DLookup("[MySQL_LOGIN]", "SetupGeneral")
strMySQLPswd = DLookup("[MySQL_PSWD]", "SetupGeneral")

'Build the MySQL Server Connection String
strMySQLConn = "ODBC;Driver={MySQL ODBC 5.1 Driver};Server=" & strMySQLServerHost & ";Database=" & strMySQLDatabase & ";User=" & strMySQLLogin & ";Password=" & strMySQLPswd & ";Option=35;"

If rst.RecordCount > 0 Then
    Do Until rst.EOF
        If DCount("Name", "MSysObjects", "Name = '" & rst!LINKED_TBL_NAME & "' and Type = 4") <> 0 Then 'The table exist
            DoCmd.DeleteObject acTable, rst!LINKED_TBL_NAME
        End If
        Set tdf = db.CreateTableDef(rst!LINKED_TBL_NAME)
        tdf.SourceTableName = rst!LINKED_TBL_NAME
        tdf.Connect = strMySQLConn
        db.TableDefs.Append tdf
End If

If Not rst Is Nothing Then
        Set rst = Nothing
End If

MsgBox "Linked Tables Complete.", vbOKOnly, ""

End Function

Open in new window

When you refresh the links, you MUST also refresh pass-through queries and views.  

When using ODBC as the BE, Access will not automatically recognize table changes as it does with native Jet/ACE links.  So, to avoid problems, I always refresh the links as part of the startup code.  That way if I added a new column or changed an index, Access will "see" the change.  It only takes a few seconds.  If you don't do this, you will need to release a new FE if you make any BE changes just to ensure that everyone has a refreshed link to the BE.
Eric ShermanAccountant/DeveloperCommented:
Yes  PatHartman ... The function I posted should be run as a part of the app startup code ... usually as a part of the startup form's on open event.

Also, if you want to use that function as is then please add the following declartions.

Dim db As Database
Set db = CurrentDb()

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.