VBA code for relinking 'new' tables from back end(.accdb) to front end database. (1 Viewer)

[Snuggle Walrus]

Hello all!

I'll start by saying although I've been using access and VBA now for a few years I'm still very new and this may be something super easy so I apologise if this is a very dumb question. I've been trying to find a solution to this for the last 2+ hours but can't quite find what I'm looking for online.

Basically, I'm working on a way of being able to update my front end files with new 'updates' to forms and such that I can push out a bit easier than giving just a new access file every time, although only a few people are using it I don't like the running around. Now I believe I've found a way around doing this with forms just fine but occasionally I will have updated the back end tables with either new columns or the back end file with additional tables. From my understanding, without relinking the file itself these new tables won't be accessible. Usually, I'd just go around and relink the tables manually but I'd really like to automate this with some VBA code either by removing the current link to the tables file (.accdb) and then relinking it and including ALL tables or just relinking and then including the new tables also.

I'm really struggling to get my head around this, basically, I just need a button that when it's clicked (it'll do the form stuff at the same time which is stored in a separate folder if there are any new forms) to either...

1. Clear the linked table file. (.accdb)
2. Link the table file(.accdb) again with ALL the tables included in the file. (this file location remains the same and is set it)

or

1. Relink the existing table file (.accdb) but include the new tables added to the file.

Is this possible at all? I'm sure there's a way. I'd really appreciate any help or guidance on how to perform this action. Below are some notes that may help on understanding? if not please ignore the below bullet points haha.

• The system is a work in progress so I create updates to add new forms to add new functionality.
• Front end system updates is on an isolated version 'copy' with its own copy of the back end system (.accdb)
• The back end system is one .accdb that stores currently 39 tables (live) and 43 tables in the 'update' version I'm about to add to.
• Actual table data is NOT changed in the updates only new columns and potential new tables for new areas of my system.

Thanks everyone have a great day.

 

[theDBguy]

Hi. Have you tried the Access Relinker code found here?

Developer Downloads - J Street Technology

These free developer downloads are for our fellow developers – we hope you find them useful! If you use a download for commercial purposes all we ask is that you keep the J Street Technology references in the code. Are you stuck on your project? Need help with development? For mentoring and...

jstreettech.com

 

[Snuggle Walrus]

Hi. Have you tried the Access Relinker code found here?

Developer Downloads - J Street Technology

These free developer downloads are for our fellow developers – we hope you find them useful! If you use a download for commercial purposes all we ask is that you keep the J Street Technology references in the code. Are you stuck on your project? Need help with development? For mentoring and...

jstreettech.com

Oooo not heard of that before taking a look now thankyou will update shortly once I've digested this info!

 

[Snuggle Walrus]

Hi. Have you tried the Access Relinker code found here?

Developer Downloads - J Street Technology

These free developer downloads are for our fellow developers – we hope you find them useful! If you use a download for commercial purposes all we ask is that you keep the J Street Technology references in the code. Are you stuck on your project? Need help with development? For mentoring and...

jstreettech.com

Ok i've followed the instructions here. Seems to be able to link the file again but doesn't seem to add any 'new' tables within the same file? tried testing it by making a new table and saving it with an obvious name and then loading the front end after saving and exiting but it won't add it to the table list. Perhaps I'm doing something wrong.

• First added the macros as requested along with the module.
• Added the line to my Auto Exec at the top (which seems to run on startup I can see it appear in the bottom right).
• added Microsoft Office 16.0 Object Library and Microsoft Access 16.0 Object Library etc to my references.
• running the second macro allows me to select the files confirming the relink but doesn't add the new tables.

 

[The_Doc_Man]

It is certainly possible. The question is whether it is cheaper in terms of your labor to build a complex auto-update feature into your Access FE or to just set up one of the many auto-download scripts available through this site that would pull down a new FE when you had one ready. In terms of time to execute, I would think the bulk launch-time auto-updater script, which only has to copy a single file, would be far faster and probably easier to maintain than to set up an auto-updater at the code level, which would have to build new relationship entries (if you wanted them) and would have to do piecemeal updates that (I think) would have to be customized for each different update.

It is, of course, your project and your environment, so if that won't work for you, then fine. However, I just think it is making more work than you need to make for yourself. One man's opinion.

 

[Eugene-LS]

I just need a button that when it's clicked (it'll do the form stuff at the same time which is stored in a separate folder if there are any new forms) to either...

Spoiler: Module : modConnection_DAO

'----------------------------------------------------------------------------------------
' Module    : modConnection_DAO
' Author    : es
' Date      : 29.01.2021 v003
' Purpose   : Table connection module (DAO)
'----------------------------------------------------------------------------------------

Public Function ConnectAllTablesDAO(strFilePath As String, Optional ByRef strPrefix As String = "") As Long
'Connects all tables of the specified MDB or ACCDB file (except hidden and system tables)
'Arguments:
'   strFilePath  = DB File path
'   strPrefix    = Prefix of the local table name (Optional)
'Function returns the error code if an error occurs
'--------------------------------------------------------------------
Dim strLink As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim lErr As Long, lCount&

On Error GoTo ConnectAllTables_DAO_Err
    Set db = DBEngine.OpenDatabase(strFilePath, dbReadOnly)
    strLink = ";DATABASE=" & strFilePath
    For Each tdf In db.TableDefs      'Retrieve all tables in the specified database
        If tdf.Attributes = 0 Then    'If table is not hidden or system
            lErr = AttachTableDAO(strLink, tdf.Name, strPrefix & tdf.Name)
            If lErr <> 0 Then 'if an error occurs - Exit!
                ConnectAllTablesDAO = lErr
                Exit For
            Else
                lCount = lCount + 1
            End If
         End If
    Next tdf
    
    db.Close
    
    If lCount > 0 Then 'Updating the list of tables of the current database
        Application.RefreshDatabaseWindow
    End If
    
ConnectAllTables_DAO_Bye:
    On Error Resume Next
    db.Close
    Set db = Nothing
    Err.Clear
    DoEvents
    Exit Function

ConnectAllTables_DAO_Err:
    ConnectAllTablesDAO = Err.Number
    Debug.Print Err.Description
    Resume ConnectAllTables_DAO_Bye
End Function


Private Function AttachTableDAO(sConnectString As String, _
                sSrsTableName As String, _
                Optional sLocalTableName As String = "", _
                Optional bMakeTableHidden As Boolean = False) As Long
'----------------------------------------------------------------------------------------
' The assistant function - connect one table by arraignments:
'--------------------------------------------------------------------
' sConnectString    = string to connect like: ';DATABASE=C:\DB.mdb
' sSrsTableName     = Original name of the table in the database
' bMakeTableHidden  = Make table hidden (Optional - default = False)
' sLocalTableName   = New Table Name (Optional - by default = original name)
' On error Function returns its code
'--------------------------------------------------------------------
Dim db As DAO.Database
Dim tdf As DAO.TableDef, sVal$
'--------------------------------------------------------------------
On Error GoTo AttachTableDAO_Err
    
'Table name in local database
    If sLocalTableName = "" Then sLocalTableName = sSrsTableName
    Set db = CurrentDb()
    
'Delation of old connected table (if present)
    If DCount("*", "MSysObjects", "[Name]='" & sLocalTableName & "' AND Type=6") > 0 Then
        'Db.TableDefs.Delete sLocalTableName
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable, sLocalTableName
        DoCmd.SetWarnings True
    End If

'Connection:
    Set tdf = db.CreateTableDef(sLocalTableName)
    tdf.Connect = sConnectString
    tdf.SourceTableName = sSrsTableName
    db.TableDefs.Append tdf

'Make table hidden (if bMakeTableHidden = True )
    If bMakeTableHidden = True Then
        Application.SetHiddenAttribute acTable, tdf.Name, True
    End If

AttachTableDAO_End:
    On Error Resume Next
    Set tdf = Nothing
    db.Close
    Set db = Nothing
    Err.Clear
    Exit Function

AttachTableDAO_Err:
    AttachTableDAO = Err.Number
    sVal = "Error " & Err.Number & " (" & Err.Description & ") in Function" & _
           "AttachTableDAO - modConnection_DAO."
    Debug.Print sVal
    'Debug.Print "AttachTableDAO_Line: " & Erl & "."
    Err.Clear
    Resume AttachTableDAO_End
End Function

Public Sub DelAttachedTables_DAO(Optional sPartOfConnectString As String = "")
'Deletion of all connected tables !
'----------------------------------------------------------------------------------------
' Removes all authenticated tables from the database with sPartOfConnectString
' in the connection string (.Connect property)
' Argument:
'   sPartOfConnectString = part of a connection string like: ODBC;DRIVER=...
'       If the parameter is not specified - all CONNECTED TABLES are deleted
'--------------------------------------------------------------------
On Error GoTo DelAttachedTables_DAO_Err

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect$, lCount&

    DoCmd.SetWarnings False
    Set db = CurrentDb()

    For Each tdf In db.TableDefs
        If tdf.Connect <> "" Then
            sConnect = tdf.Connect
            'Debug.Print sConnect
            If sPartOfConnectString = "" Or _
                    InStr(sConnect, sPartOfConnectString) > 0 Then
                DoCmd.DeleteObject acTable, tdf.Name
                lCount = lCount + 1
            End If
        End If
    Next
    If lCount > 0 Then
        'Updating the list of tables of the current database (optional)
        Application.RefreshDatabaseWindow
    End If

DelAttachedTables_DAO_End:
    On Error Resume Next
    DoCmd.SetWarnings True
    Set tdf = Nothing
    db.Close
    Set db = Nothing
    Err.Clear
    Exit Sub

DelAttachedTables_DAO_Err:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub" & _
           "DelAttachedTables_DAO - modConnection_DAO.", vbCritical, "Error!"
    'Debug.Print "DelAttachedTables_DAO_Line: " & Erl & "."
    Err.Clear
    Resume DelAttachedTables_DAO_End

End Sub

Example of use:

Private Sub CoonectionTest01()
Dim sDBPath As String
Dim lErr As Long
'   Clear the linked table file. (.accdb)
    DelAttachedTables_DAO
    
'   Link the table file(.accdb) again with ALL the tables included in the file.
'   (this file location remains the same and is set it)
'   or
'   Relink the existing table file (.accdb) but include the new tables added to the file.

    sDBPath = "d:\Temp\Database1.accdb"
    lErr = ConnectAllTablesDAO(sDBPath)
    If lErr = 0 Then
        MsgBox "All tables from:" & vbCrLf & sDBPath & vbCrLf & "Connected", vbInformation, "Connected"
    Else
        MsgBox "Error : " & lErr, vbExclamation, "Error : " & lErr
    End If
End Sub

 

[Pat Hartman]

I don't like the running around

Then you need to use your head instead of your feet. Unless you have nothing better to do and need the practice with complex coding, you might want to just use a batch file or one of the free downloader apps posted here that are used to distribute new FE's.

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

Create the .bat file and save it on the server in the folder with your master FE. Give each person a shortcut to run the .bat file. Each time they open the shortcut, a fresh copy of the FE is copied from the server. This doesn't add any perceptible time to the open process and it has advantages such as - you never have to worry about bloat or corruption in the FE. If you prefer, you can have the shortcut run one of the distribution apps that does essentially what the bat file does.