SQL Relink Tables

[Snowflake68]

I have a function that is called when my application is launched, and it relinks my SQL tables. Which was working perfectly well until I move some local Access tables into SQL and then had to renamed them in order for the application to continue working.

For example I had a local backend Acccess table called 'tbl_Conv_ColumnQty' which is now in SQL but when I linked the table into my application is is prefixed with dbo_ and so the table name is dbo_tbl_Conv_ColumnQty. I have renamed the table to remove the dbo_ so that my application continues to work.

However the function no longer works because I have renamed the table and i now get a message saying that it cannot find the linked table 'tbl_Conv_ColumnQty'

I did not write this function myself so I dont know if it can be amended to add the dbo_ part so that it looks for the correct table name. Can anyone help with amending the function please?

Function RelinkSQLTablesAndViews() 'Re-links any tables and views using the active connections strings in the tblConnections table Dim db As Database Set db = CurrentDb Dim tdef As TableDef Dim indexSQL As String Dim fld As Field Dim constr As Variant Dim HasIndex As Boolean constr = DLookup("ConnectionString", "tblConnections", "Active = True") For Each tdef In db.TableDefs If InStr(tdef.Connect, "ODBC") Then HasIndex = False If tdef.Indexes.Count = 1 Then ' only interested in objects with 1 index indexSQL = "CREATE INDEX " & tdef.Indexes(0).Name & " ON [" & tdef.Name & "](" & tdef.Indexes(0).Fields & ")" ' convert field list from (+fld1;+fld2) to (fld1,fld2) indexSQL = Replace(indexSQL, "+", "") indexSQL = Replace(indexSQL, ";", ",") HasIndex = True End If tdef.Connect = constr tdef.RefreshLink If HasIndex And tdef.Indexes.Count = 0 Then ' if index now removed then re-create it CurrentDb.Execute indexSQL End If End If Next Call ShowSplashScreen("frmSplash", 3) Exit Function Continue: DoCmd.Close acForm, "frmRefreshTables" DoCmd.OpenForm "frmSplash" DoCmd.Close acForm, "frmMain" End Function

The table called 'tblConnections' contains this as the active ConnectionString.

ODBC;DSN=SysproCompanyT;Description=SysproCompanyT;Trusted_Connection=Yes

 

[arnelgp]

did you Rename the tables in MSSQL server also?

 

[Snowflake68]

did you Rename the tables in MSSQL server also?

No I have only renamed in Access so the linked table is still named with the dbo_ part but I have removed that in Access as you can call the linked tables whatever you like which is what I did so that my existing application continues to work otherwise I would have to change a load of queries and code.

 

[arnelgp]

do you always Need to do this, relink the tables?

 

[Snowflake68]

do you always Need to do this, relink the tables?

Yes because when there are changes made to the sql tables they are not reflected in the Access application until the tables are refreshed. There will be several people using their own copy of the app that so it makes sense to automatically do it everytime the app opens otherwise I will need to go to everyones application and refresh the links manually which is not practical.

 

[Snowflake68]

I think I have found the issue but not sorted it out. The reason for the error is that I have two SQL databases linked to the application and I need to check tables from both SQL databases. I am not sure how I can do this so if anyone has any suggestions I will be most grateful.

Thanks

 

[Minty]

Create a local table tbTableMappings of the table names - a minimum of three fields LocalTableName , ServerTableName, DatabaseName

You can then simply loop through it to relink then tables from the database you need and call them what you like.

 

[Snowflake68]

Create a local table tbTableMappings of the table names - a minimum of three fields LocalTableName , ServerTableName, DatabaseName

You can then simply loop through it to relink then tables from the database you need and call them what you like.

Great idea but I do not have the skills to write the code to do that. Are you able to help me please?

 

[Isaac]

with reference to your original problem and why the code doesn't work:

what if you go to the vba project, view immediate window, type currentdb.TableDefs.Refresh (and hit Enter) in the immediate window, then run the code. any difference?

second question, which line of code specifically does the debugger break on when the code errs?

 

[arnelgp]

you then need to Delete the old tabledef and re-create the new one:

Function RelinkSQLTablesAndViews()

    'Re-links any tables and views using the active connections strings in the tblConnections table
    Dim db As Database
    Set db = CurrentDb
    Dim tdef As TableDef
    Dim indexSQL As String
    Dim fld As Field
    Dim constr As Variant
    Dim HasIndex As Boolean
    
    'arnelgp
    Dim tdName      As New Collection
    Dim tdSource    As New Collection
    Dim tdIdx       As New Collection
    Dim i           As Integer
    
    constr = DLookup("ConnectionString", "tblConnections", "Active = True")
    For Each tdef In db.TableDefs
        
        
        If InStr(tdef.Connect, "ODBC") Then
            i = i + 1
            tdName.Add tdef.Name, i & ""
            tdSource.Add tdef.SourceTableName, i & ""
            
            HasIndex = False
            If tdef.Indexes.Count = 1 Then
                ' only interested in objects with 1 index
                indexSQL = "CREATE INDEX " & tdef.Indexes(0).Name & " ON [" & tdef.Name & "](" & tdef.Indexes(0).Fields & ")"
                ' convert field list from (+fld1;+fld2) to (fld1,fld2)
                indexSQL = Replace(indexSQL, "+", "")
                indexSQL = Replace(indexSQL, ";", ",")
                HasIndex = True
                tdIdx.Add indexSQL, "idx" & i
            End If

            'tdef.Connect = constr
            'tdef.RefreshLink
            'If HasIndex And tdef.Indexes.Count = 0 Then
            '    ' if index now removed then re-create it
            '    CurrentDb.Execute indexSQL
            'End If

        End If
    Next
    
    ' delete the old link tables
    For i = 1 To tdName.Count
        db.TableDefs.Delete tdName(i)
    Next
    db.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    
    ' re-create the link tables
    For i = 1 To tdName.Count
        indexSQL = ""
        Set tdef = db.CreateTableDef(tdName(i))
        tdef.SourceTableName = tdSource(i)
        db.TableDefs.Append tdef
        db.TableDefs.Refresh
        Application.RefreshDatabaseWindow
        
        On Error Resume Next
        indexSQL = tdIdx("idx" & i)
        If Err.Number <> 0 Then
            'error means there is not index for this table
        Else
            db.Execute indexSQL
        End If
        Err.Clear
        On Error GoTo 0
    Next
    Call ShowSplashScreen("frmSplash", 3)

    Exit Function

Continue:
    DoCmd.Close acForm, "frmRefreshTables"
    DoCmd.OpenForm "frmSplash"
    DoCmd.Close acForm, "frmMain"
End Function