relink table thru DSN

[sophia03]

Hello.

Does anyone have code that would link SQL table thru DSN from Access? Any help is greatly appreciated. Thanks.

 

[PHV]

Have a lok here:

http://www.mvps.org/access/tables/tbl0009.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sophia03]

PH,

I checked the code that you suggested and it can’t help me. I think that I need to explain my problem. I build the database that have Access front and SQL backend. “Every Monday” users are getting ODBC connection error when they try to make update into the SQL table. They can browse the data but they get an error when they try to make update. I fix the problem by deleting the linked table from Access and then relink the table using DSN and the problem goes away until next Monday. Something happens over the weekend during backups that cause loss of the connection. By having the code to relink, I would putt it on the scheduler to execute on Monday morning before any of the users get in to the database and my problem would go away. PH, if you have something that would help me I would greatly appreciate.

 

[CompuSolve]

Have you considered youing DNSLess connections? I think this is probably the better way to go about fixing this problem.

***fMsgBox is a formatted MsgBox - you can repace this with MsgBox

tableIn is a table that contains 1 record. A field in that record is SQLServerConnectString.

tblTableListSQLServer contains 2 fields, SQLServerTableName and AccessTableName.

You puit this code in a macro and you can double click the macro to re-connect the tables (tableIn is a variable so you can have different macros for test and production). You can also incluse a menu item in your application that re-links the tables. Although, by using a DNSLess connection I think your problems will just disappear.

This was done in access 2003 - not sure if it works in earlier versions.

Function ConnectSQLServer(tableIn As String)
    
    'this module drops all tables and reconnects them to the SQL server defined in the settings table
    
    Dim tdfLinked As TableDef
    Dim db As Database
    Dim rstSettings As Recordset, rstTables As Recordset
    Dim strSQLServer As String
    
    On Error GoTo errHan
            
    Set db = CurrentDb
    
    Set rstSettings = db.OpenRecordset(tableIn, dbOpenDynaset, dbSeeChanges)

    If rstSettings.EOF Then
        fMsgBox "Critical Error.  Settings not found.", vbCritical, "Critical Error!"
        Exit Function
    End If

    If IsNull(rstSettings!SQLServerConnectString) Then
        fMsgBox "Critical Error.  SQL Server connect string not found.", vbCritical, "Critical Error!"
        Exit Function
    End If
    
    strSQLServer = rstSettings!SQLServerConnectString
    
    Set rstTables = db.OpenRecordset("tblTableListSQLServer")
    
    With rstTables
        Do Until .EOF
            
            If IsNull(!AccessTableName) Then
                fMsgBox "Critical Error.  Access Table Name for " & !SQLServerTableName & _
                    " not defined.", vbCritical, "Critical Error!"
                Exit Function
            End If
            
            Call SysCmd(acSysCmdSetStatus, "Refreshing " & !AccessTableName & "...")
            
            On Error Resume Next
                db.TableDefs.Delete !AccessTableName
            On Error GoTo errHan
            
            Set tdfLinked = db.CreateTableDef(!AccessTableName)
            
            tdfLinked.Connect = strSQLServer
            tdfLinked.SourceTableName = !SQLServerTableName
            
            db.TableDefs.Append tdfLinked
            
            .MoveNext
            
        Loop
    End With
    
    Call SysCmd(acSysCmdClearStatus)

Exit Function

errHan:
   fMsgBox Err.Number & " " & Err.Description
   Call SysCmd(acSysCmdClearStatus)
   Exit Function
End Function