Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Force Open Linked Table (Access 2000)

    I have a database created where I have linked to a table that sets on our server. The database I have created moves extremely slow because of this linked table. I have run the optimizer and it suggested that I force open the linked table. It gives very vague instructions on how to do this. I am not versed with OpenRecordset suggestion. Splitting the database that the table is linked from is not an option. I need someone to show me in very simple terms how to do this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Force Open Linked Table (Access 2000)

    You need to run some code when the database is opened. First, I'll try to explain how to create this code.
    1. <LI>Activate the Visual Basic Editor (Alt+F11)
      <LI>Select Insert/Module
      <LI>Select Tools/References...
      <LI>Make sure that Microsoft DAO ... Object Library is checked.
      <LI>Click OK to close the References window.
      <LI>Type or copy the following code into the module:

      Public dbs As DAO.Database
      Public rst As DAO.Recordset

      Public Function OpenLink()
      Set dbs = CurrentDb
      ' Substitute the name of your linked table for tblLinkedTable
      Set rst = dbs.OpenRecordset("tblLinkedTable")
      End Function

      <LI>Close and save the module; give it a name like basOpenLink.
      <LI>Return to Access itself.
    There are two ways to run this code when the database is opened:
    1. <LI>If you have a startup form (set in Tools/Startup...), you can run the code in the OnLoad event of the form:
      <UL><LI>Open the startup form in design view.
      <LI>Activate the Properties window.
      <LI>Click the Events tab.
      <LI>Click in the On Load event.
      <LI>Select Event Procedure from the dropdown list.
      <LI>Click the builder button (the ... to the right of the dropdown list).
      <LI>You are taken to the Visual Basic Editor, and the framework for the OnLoad event is ready for you to use.
      <LI>Type OpenLink into the procedure. It should look like this:

      Private Sub Form_Load()
      OpenLink
      End Sub

      <LI>Return to Access.
      <LI>Close and save the form.
    <LI>You can also run the code from a macro named AutoExec:
    <UL><LI>Create a new macro.
    <LI>In the Action column, select the RunProcedure action.
    <LI>In the Function Name box, type OpenLink()
    <LI>Close the macro and name it AutoExec.[/list][/list]The next time you open the database, the database with the linked database will be opened too. You can check this: a file with the same name as the database but with extension .ldb should exist as long as the frontend database is open.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Force Open Linked Table (Access 2000)

    In addition to holding a connection open, make sure you turn off subdatasheets for each of your tables and uncheck Name Autocorrect from Tools-->Options-->General. Subdatasheets especially slow linked tables and can be turned off by opening the table in design view, bringing up the properties dialog, and setting the Subdatasheet Name property to [None].
    Charlotte

  4. #4
    New Lounger
    Join Date
    Nov 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force Open Linked Table (Access 2000)

    Thank you for your help. Wow! what a difference. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •