×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Accessible archive

Accessible archive

Accessible archive

(OP)
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?

RE: Accessible archive

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.

RE: Accessible archive

(OP)
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

CODE -->

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.

RE: Accessible archive

See this link for an example.

RE: Accessible archive

(OP)
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
  • Run-time error '3170'
  • Could not find installable ISAM



  • CODE -->

    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 

    CODE -->

    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 

    Red Flag This Post

    Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

    Red Flag Submitted

    Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
    The Tek-Tips staff will check this out and take appropriate action.

    Reply To This Thread

    Posting in the Tek-Tips forums is a member-only feature.

    Click Here to join Tek-Tips and talk with other members! Already a Member? Login

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close