Results 1 to 5 of 5
  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82

    Copy table from 3rd access database to backend database via VBA in frontend

    I have a split database that a user has linked a third database with to pull data (wish they had called me first ) The third database has a query based on a local table and the tables linked to my backend database. the user now wants to turn this query into a standard report in my frontend. I cant just link the frontend to the third database because it is not available to all users and I cant link the backend because I cant link the frontend to a linked table in the backend. HOPE THIS MAKES SENCE

    I think the "fix" is to have the frontend copy the table from the third database to the actual backend when the specific user logs into the front end. This user has the third database on their local machine. while the data would not be completely current, it will be current enough for the purposes of the report.



    I just don't know how to make it happen using vba. I think docmd.copyobject would only copy the table to the users frontend database and I need the table to be placed in the backend database so other users can access the data.

    Any help is greatly appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Another possibility may be to programmatically set the link only if that user opens frontend. This way you don't have to play around with modifying the backend and possibly causing corruption.

    Wait, I just read 'so other users can access the data' - the data of this 3rd db?

    This 3rd db is on user's local machine - why not on the network? If you want to make this data available to everyone, why not just incorporate this one user's creation as part of the main db design?

    It is possible to programmatically manage design of backend. I have code in couple of dbs that do that. I can dig out the code if you want, later.

    Mostly it involves setting a connection in VBA to the other db and then creating tables or referencing existing tables and INSERT SELECT sql to replicate record from one db to other.
    Last edited by June7; 05-25-2017 at 03:24 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    the 3rd database (on local machine) has data in it that can not be placed on a server (or the office doesn't want it on the server even with password) I only need the a single table in their database. I am trying to avoid having them update both databases by pulling in the table.

    Just thinking and while far from ideal, I could set up the code to copy the table to the frontend as a temp local table then replace the data in a duplicate linked table to my backend. the code would only run when the specific person logs in. Don't like coding for a specific person but I cant think of a better way to handle it now that this 3rd db has been hacked together and is now "required" information for the user. if they had come to me to start with, I could have handled it a lot cleaner.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So you can't just incorporate that one table in your db and have that user set a link to it?

    One very simple approach is to have a permanent table in your backend with the same structure as the user's table. Then just delete records and repopulate. I recommend this over repeatedly deleting and creating table.

    The first INSERT SELECT SQL shown requires that the fields all be in the same order in the design of both tables (the display order in datasheet view is irrelevant), which is nice if you have a lot of fields to fill, otherwise have to explicitly reference the fields as shown in the alternate SQL.
    Code:
    Public Sub GetData()
    Dim strPathBackend
    Dim strPathOtherDB
    strPathBackend = "path\filename.accdb"
    strPathOtherDB = "path\filename.accdb"
    CurrentDb.Execute "DELETE FROM tablename IN '" & strPathBackend & "'"
    CurrentDb.Execute "INSERT INTO tablename IN '" & strPathBackend & "' SELECT * FROM tablename IN '" & strPathOtherDB & "';"
    'or this
    CurrentDb.Execute "INSERT INTO tablename(field1, field2) IN '" & strPathBackend & "' SELECT field1, field2 FROM tablename IN '" & strPathOtherDB & "';"
    End Sub
    However, if the table has attachment or multi-value or OLEObject fields, either have to not include those fields or code gets a lot more complicated.

    This is not the approach I was originally thinking of that involved setting OLEDB connection in VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    that should work and is much simpler than I was thinking. sometimes I just overlook the simple approach.

    thanks!

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

Similar Threads

  1. Replies: 11
    Last Post: 02-24-2017, 06:25 PM
  2. Replies: 4
    Last Post: 03-31-2015, 01:20 PM
  3. Replies: 2
    Last Post: 01-15-2015, 08:18 PM
  4. Frontend Backend Database Questions
    By data808 in forum Access
    Replies: 29
    Last Post: 03-26-2014, 11:58 PM
  5. Replies: 1
    Last Post: 11-25-2011, 11:16 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