Broken link between MS Access and SQL Server 2

[Chew407]

I have an Access db with linked tables from SQL server. There is password security on the SQL db. Everytime the Access db is closed the link to the SQL db is broken. Upon reopening Access I receive the message "ODBC--connection to 'SQL Server W01' failed." Is there a way to get around this issue or deal with it within Access so that the user does not have to relink the tables and enter the SQL Server Login ID and Password everytime?

Thanks in advance for your help!

 

[jsteph]

When the tables are linked (if manually), check the Save password box.

If linking in code, use the .attach option dbAttachSavePwd.
--Jim

 

[Chew407]

The tables were linked by creating an ODBC through File>Get External Data>Link Tables and by selecting ODBC for the file type. There is no Save Password box in the dialog that pops up when required to enter the Login ID and Password to the SQL Server. Any other ideas?

 

[jsteph]

You might try linking in code, as in:

Dim db as dao.database,td as dao.tabledef
set db = currentdb
    Set td = db.CreateTableDef("tablename")
    td.Connect = "ODBC;DSN=somedsn;uid=user;pwd=pwd"
    td.Attributes = 131072 'dbAttachSavePWD
    td.SourceTableName = "servertablename"
    db.TableDefs.Append td

 

[Chew407]

If you don't mind, can you explain how this code works? Is it creating a new table linked to the SQL Server database? And is this new table storing Authentication information? Also, does this only work for a single table or any table within the SQL Server database?

Thanks again.

 

[Ray1127]

Look at the ODBC Data Manager for the ODBC Connection you are using. Make sure the Login and password are set correctly there.

 

[dhookom]

I use code that I pulled from Doug Steele's Using DSN-Less Connections

Function RelinkODBC()
    Dim td As DAO.TableDef
    Dim qd As DAO.QueryDef
    Dim db As DAO.Database
    Dim strConnect As String
    strConnect = "ODBC;Driver={SQL Server};Server=<server name>;Database=<db name.;Uid=<user>;Pwd=<password>"
    Set db = CurrentDb
    For Each td In db.TableDefs
        If Left(td.Connect, 4) = "ODBC" Then
            Debug.Print td.Name
            td.Connect = strConnect
            td.Attributes = DB_ATTACHSAVEPWD
            td.RefreshLink
        End If
    Next
    For Each qd In db.QueryDefs
        If Left(qd.Connect, 4) = "ODBC" Then
            Debug.Print qd.Name
            qd.Connect = strConnect
        End If
    Next
    Set td = Nothing
    Set qd = Nothing
    Set db = Nothing
    
End Function

Duane
Hook'D on Access
MS Access MVP

 

[jsteph]

Chew,
For the code I posted, it was for example. The full procedure would *delete* the linked table prior to the .CreateTableDef.

This has an advantage over re-linking because re-linking does not always work as well as I wish it would--Microsoft has some issues with Relink. So you're cleaner if you delete and re-create the linked tabledef.

And yes, it will work with any table in the server in which you have rights to under the given uid/pwd.

It is storing the auth information internally, as well as the password being in the string in the code itself. However, there are options to get around this, for example you could have Jet security on the Modules, it's not perfect but it would keep causual users from the pwd. You could also require a prompt and let the user type a password if you don't want to store the pwd in code.
--Jim

 

[Chew407]

Thank you jsteph! Thank you Ray! Thank you Duane!

I tried a couple different solutions but in the end I found that using the DNS-less connection worked best for what I was trying to achieve. I just thru the code into a module and voila! It worked like a charm! Thanks again all for your help!