Accessible archive

[TrekBiker]

A client's database is taking about 15 seconds to generate invoices. They draw data from an Orders table with about 50,000 records and Order Details with about 110,00 records.

If I use a backend with just the current year's sales the invoice creation time goes down to an acceptable 3 second.

The client wants historic data to be accessible when needed and there are lots of posts about archiving as a way of doing this, mostly using copies of the front and backends.

Ideally I'd like to use the same frontend and programmatically change the backend when needed. Does anyone have experience, or links, or even code for doing this?

 

[jedraw]

It's been years, but I have used a single front end (form with combo) to select and use tables in multiple back ends. The BEs were different Oracle environments (dev, test, prod, maint) .
See these link andLink for info.

 

[TrekBiker]

Thanks for response but struggling a bit with this.

I'd like the Switchboard to have a backend selector that defaults to 'Current Year' with an option for 'Earlier data'. Both backends would be in the same server location \\MOAKBBTERM16\Database\OrderTracker.

I've found this code for linking

 Dim dbCurr As Database
 Dim tdfTableLink As TableDef
 
 For Each tdfTableLink In dbCurr.TableDefs
   tdfTableLink.Connect = ";DATABASE=" & (Insert new file path)
   tdfTableLink.RefreshLink
 Next

But I'm not experienced enough to adapt it to what I need.

 

[jedraw]

See this link for an example.

 

[TrekBiker]

Thanks for the link, which I've tried to implement.

I have a frontend linked to a backend called OrderTracker.mdb and would eventually want use all the tables in a backend OT.mdb instead. The code in the link you referred me to transfers a single table only so I added a test table Admin to OT, and put a command button Command34 on the frontend switchboard.

I used the details as below, wanting a link to Admin to appear (as Admin) in the frontend

But I got
[li]Run-time error '3170'[/li]
[li]Could not find installable ISAM[/li]

Public Sub Command34_Click()
Call LinkTable("Admin", "Admin", "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=C:\Users\OT.mdb;Persist Security Info=False;")
End Sub

Private Function LinkTable(LinkedTableName As String, TableToLink As String, connectString As String) As Boolean
    Dim tdf As New dao.TableDef
    With CurrentDb
        .TableDefs.Refresh

        Set tdf = .CreateTableDef(LinkedTableName)
        tdf.Connect = connectString
        tdf.SourceTableName = TableToLink
        .TableDefs.Append tdf
        .TableDefs.Refresh
    End With

    Set tdf = Nothing
End Function