none
get data from a different database RRS feed

  • Question

  • Hi all,

    I have a database with the FE on the user PC's and the back end on the server.

    One of the functions is that the user views and edits  some data and this amended data is then stored in the back end with the name "BOReporting_" [and the system date] as part of the table name. Then I use the "TransferDatabase" to create a link to the front end.

    What I want to do, is when a user opens the database, the program must go and check the back end and if there are tables with the name "BOReporting_" that has not been linked to the front end, it must create the link in the front end.

    I have put in some code to do this, but only the front end is checked, not the back end.

    How can I change my code to check the back end tables?

    Code that I am using below:

    For Each accObject In CurrentData.AllTables
        If Left(accObject.FullName, 12) = "BOReporting_" Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", "Y:\PL Chemserve\NEW PL System Backend\Packing List System NEW_be.accdb", acTable, _
            accObject.FullName, accObject.FullName, False
        End If
    Next

    Thanks

    Deon

    Friday, July 14, 2017 7:26 AM

Answers

  • You need to open the back-end database in DBEngine object. Then you can enumerate its tables.

    But I prefer a simpler solution in such cases:

    Just create a table and store the created table names there. Then link this table permanently. Now you can loop thru this table and link new tables.

    Side-Effect: You have your own "linked table manager" for relinking tables when necessary.

    • Marked as answer by Deon SA Monday, July 17, 2017 8:07 AM
    Friday, July 14, 2017 7:43 AM

All replies

  • You need to open the back-end database in DBEngine object. Then you can enumerate its tables.

    But I prefer a simpler solution in such cases:

    Just create a table and store the created table names there. Then link this table permanently. Now you can loop thru this table and link new tables.

    Side-Effect: You have your own "linked table manager" for relinking tables when necessary.

    • Marked as answer by Deon SA Monday, July 17, 2017 8:07 AM
    Friday, July 14, 2017 7:43 AM
  • Alternatively, you can query the back-end database directly for the names of the tables it contains.  Here's an example of the SQL of such a query:

    SELECT MSysObjects.Name
    FROM [C:\Your\Path\To\YourBackend.accdb].MSysObjects
    WHERE (MSysObjects.Name Not Like "MSys*") AND (MSysObjects.Type=1)
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, July 14, 2017 7:32 PM