Choose Backend using VBA 1

TrekBiker · Jan 3, 2020

I asked this in another section so apologies for trying again here.

I want to be able to allow a user to switch between two backends with identical structures but different date ranges, so

\\BackendData\CurrentOrders.mdb
\\BackendData\ArchiveOrders.mdb

There are several posts on a similar theme but I haven't been able to adapt them.

Help much appreciated, as ever.

combo · Jan 3, 2020

Here is a code from MS site, it assumes that databases are in the same directory, has to be slightly modified.
TT thread about relinking tables: thread705-1776820
Search "access tables relink" for more info.

combo

TrekBiker · Jan 5, 2020

Splendid, thanks Combo, this allowed me to modify so I could open CurrentOrders normally but allow users to switch to ArchiveOrders with a single click.

TrekBiker · Jan 7, 2020

This is the modified code, used on one command button to switch to the Archive. Another button uses the same to code to connect to a CurrentData.mdb backend. Works a treat.

Code:

Private Sub cmdCurrentBackend_Click()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim LnkDataBase As String
Dim strTable As String
    
    LnkDataBase = "\\MOAKBBTERM16" & "\" & "Database" & "\" & "Archive.mdb "
    Set dbs = CurrentDb()

    For Each tdf In dbs.TableDefs
        If Len(tdf.Connect) > 1 Then 'Only relink linked tables
            If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
                If Left(tdf.Connect, 4) <> "ODBC" And Left(tdf.Connect, 5) <> "Excel" Then 'Don't want to relink any ODBC tables
                    strTable = tdf.Name
                    dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
                    dbs.TableDefs(strTable).RefreshLink
                End If
            End If
        End If
    Next tdf
    
End Sub

A supplementary question is how to stop people inadvertently using the Archive backend to create new orders, since the data would obviously not then appear in the Current backend. I'll ask about this in a separate post.

Thanks again for quick responses.

Andrzejek · Jan 7, 2020

Another button uses the same to code to connect to a CurrentData.mdb backend"
I hope you do not copy-paste this code just to change the name of your mdb [ponder]

---- Andy

There is a great need for a sarcasm font.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Choose Backend using VBA 1

TrekBiker

Technical User

combo

Technical User

TrekBiker

Technical User

TrekBiker

Technical User

Andrzejek

Programmer

Similar threads

Part and Inventory Search

Sponsor