SQL Relink Tables (1 Viewer)

Snowflake68

Registered User.
Local time
Today, 10:01
Joined
May 28, 2014
Messages
452
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:01
Joined
May 7, 2009
Messages
19,175
did you Rename the tables in MSSQL server also?
 

Snowflake68

Registered User.
Local time
Today, 10:01
Joined
May 28, 2014
Messages
452
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:01
Joined
May 7, 2009
Messages
19,175
do you always Need to do this, relink the tables?
 

Snowflake68

Registered User.
Local time
Today, 10:01
Joined
May 28, 2014
Messages
452
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

Registered User.
Local time
Today, 10:01
Joined
May 28, 2014
Messages
452
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

AWF VIP
Local time
Today, 10:01
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 10:01
Joined
May 28, 2014
Messages
452
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

Lifelong Learner
Local time
Today, 03:01
Joined
Mar 14, 2017
Messages
8,738
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:01
Joined
May 7, 2009
Messages
19,175
you then need to Delete the old tabledef and re-create the new one:
Code:
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
 

Users who are viewing this thread

Top Bottom