asked on

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.

Steve

jjnet123

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

See here:

http://www.devx.com/tips/Tip/12995

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jjnet123

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.

ste5an

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 Sherman

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;"

'MySQL SERVER ODBC LINKED TABLES PROCESSING
If rst.RecordCount > 0 Then
    rst.MoveFirst
    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
        db.TableDefs.Refresh
       
        rst.MoveNext
    Loop
End If

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

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

End Function

Open in new window

PatHartman

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 Sherman

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()

ET