Results 1 to 6 of 6
  1. #1
    quicova is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    53

    open database always on specific backend file

    Hi everyone.

    I'm trying to make my database to always open on a specific backend file even if it was closed using another backend file.

    I know I need to create an autoExec macro, but I can't figure out the code to specify the backend file.
    can anyone give me a hand on this or point me in the right direction?



    Thanks so much

  2. #2
    quicova is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    53
    Hello everyone.

    I made some more research but I'm stuck at an error.
    Hope someone can help me out with this.

    I have this code to relink a backend on startup.
    I have a autoExec macro with the first comand run function open_template()

    Code:
    Function open_template()
    
    
    Dim dbsTemp As Database
       Dim strMenu As String
       Dim strInput As String
    
    
       ' Open a Microsoft Jet database to which you will link
       ' a table.
       Set dbsTemp = CurrentDb
    
    
       ' Call the ConnectOutput procedure. The third argument
       ' will be used as the Connect string, and the fourth
       ' argument will be used as the SourceTableName.
             ConnectOutput dbsTemp, _
                "Composite_Parts", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Composite_Parts"
             ConnectOutput dbsTemp, _
                "Deleted_Items", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Deleted_Items"
            ConnectOutput dbsTemp, _
                 Material_List", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Material_List"
            ConnectOutput dbsTemp, _
                "Part_Category_List", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Part_Category_List"
            ConnectOutput dbsTemp, _
                "Part_Database", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Part_Database"
            ConnectOutput dbsTemp, _
                "Part_History", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Part_History"
            ConnectOutput dbsTemp, _
                "Project", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Project"
            ConnectOutput dbsTemp, _
                "Weight_Cat", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Weight_Cat"
            ConnectOutput dbsTemp, _
                "Weight_Locations", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Weight_Locations"
            ConnectOutput dbsTemp, _
                "Weights_Centers", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Weights_Centers"
                
    End Function
    
    
    Sub ConnectOutput(dbsTemp As Database, _
       strTable As String, strConnect As String, _
       strSourceTable As String)
    
    
       Dim tdfLinked As TableDef
    
    
       ' Create a new TableDef, set its Connect and
       ' SourceTableName properties based on the passed
       ' arguments, and append it to the TableDefs collection.
       Set tdfLinked = dbsTemp.CreateTableDef(strTable)
    
    
       tdfLinked.Connect = strConnect
       tdfLinked.SourceTableName = strSourceTable
       dbsTemp.TableDefs.Append tdfLinked
    
    
    End Sub
    first question, is there a way to loop through the table names so I don't have to input them manually in the code?
    I'm repeating this part of the code for every table:

    Code:
    ConnectOutput dbsTemp, _
                "Part_Database", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Part_Database"
            ConnectOutput dbsTemp, _
                "Part_History", _
                ";DATABASE=C:\Users\quico\Desktop\Weight_Estimate_Start_Template.accdb", _
                "Part_History"

    and them I get an error saying the table already exists... should I delete all the tables at startup before running this code?

    Thanks so much for all your help!

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Maybe this link will be helpful: http://allenbrowne.com/ser-13.html

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    The system is correct - You are adding a new tabledef for each of those tables each time you run. Not what you are tyring to achieve, I think.

    You can use this method to refresh the links instead. http://blogs.office.com/b/microsoft-...s-tables-.aspx

    Yes, you can loop through the table names, for example using a recordset/cursor. However, you do have to get the required table names into the database somehow first.

  5. #5
    quicova is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    53
    I figure it out, maybe not the best way but it works.
    I'm deleting all the linked tables firsts, them running the code to link everything again from a new file.

    It is working.
    The code doesn't look that good but it works.
    haven't figure out how to loop through the names though.

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    The trivial answer is, put the names in a table, then loop through the table using a recordset.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. button to link to specific backend file
    By quicova in forum Import/Export Data
    Replies: 5
    Last Post: 11-04-2013, 01:39 PM
  2. database does not want to open an .mda file
    By Henriette50 in forum Access
    Replies: 3
    Last Post: 07-28-2013, 03:15 PM
  3. Replies: 7
    Last Post: 10-27-2012, 07:19 AM
  4. Replies: 6
    Last Post: 09-14-2012, 11:58 AM
  5. Replies: 6
    Last Post: 04-23-2010, 06:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums