Results 1 to 13 of 13
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Split DB questions

    I just started a split DB and I have lots to learn.
    I would like to know how to add a table to the FE (for ver # , misc info.).
    I found DoCmd.CopyObject to copy a table doesn't work. What would be the best way to copy a table? (used only for DB design )
    Working on linking tables.


    Thanks

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Import from wherever it is that you're trying to copy from. Ribbon > External Data >New Data Source
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    I would like to copy a table in VBA. How do I add a table to FE? Link table manager, i'm not sure how it works.
    Thanks!

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    google 'vba transferdatabase function'

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't know why you would want to do this, but maybe you can modify this code.
    At How To Use Access VBA To Copy A Table with DoCmd.CopyObject, there is this code
    Code:
    Private Sub btnMakeTable_Click()
        'loeblcomservices.com
        
        Dim strNewName As String
        
        'append the combo box selected value to the text
        strNewName = "Facility_" & Me.cboFacility
        
        DoCmd.CopyObject , strNewName, acTable, "tblFacilityBlank"
        
    End Sub
    The last parameter would be the full path (drive, PathToDatabase, table name) to the database you want to copy.




    Then there is Three ways to programmatically duplicate a table in MS Access by VBA.
    See #2 (CopyObject)
    Code:
    DoCmd.CopyObject , "Products_Copy", acTable, "Products"
    and #3 (DAO TransferDatabase method)
    Code:
    DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "Products", "Products_Copy", StructureOnly:=True

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    MadTom, when you say copy a table to FE then start mentioning linked tables and split dbs it starts to get foggy. What exactly do you want to do? Copy a table from the back-end to the front-end to use it as a local "settings" table (so each copy of the front-end could have their own settings such as user, local paths, etc.), link to a back-end table (regular split db scenario) so the FE gets access to the BE data? Why VBA? Docmd.TransferDatabase allows you to do both (import table so it becomes local in FE, link to BE table). Or you could simply open the BE, select the table press CTRL+C, switch to the open FE and do CTRL+V and select "Local table".
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    I can get this to work on my non split db, but can't get it to work on my split db?
    I did change db name.
    I can't just copy be to fe using CTRL+V, I get table not found.

    Code:
    DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Users\MadTo\Documents\Access Data\Mad-TomV1.6.4Split_fe.accdb", acTable, "tblParts", "tblPartsBcopy", False

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I do that all the time. You need to have both files (FE and BE) open, in the BE's Navigation Pane you select the table and press CTRL+C (or right clcik and Copy) then you switch to the front-end, click inside the navigation pane and press CTRL+V (or right click and Paste). You should see something like this, choose whatever you need:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	21 
Size:	12.2 KB 
ID:	43869
    In your transferdatabase code you seem to try to import the table from the front-end into the front-end, which you can't because the table is now in the Back-End....

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Ok, I'm new to split DB, learning as I go. I think I only had FE open and that was my problem. All seems to be good, DoCmd.CopyObject table, Copy and paste, and can add table to FE.
    Is there a way to open FE and BE together?
    Thanks for your help and
    patience!
    Mad-Tom

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry but what do you mean by "together"? In Windows explorer you navigate to the folder where you have the two files and double-click on each, they would open in separate Access instances in the task bar on the bottom or your screen, you would then choose there which one to make active.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Vlad, Sorry, I mean by one click to open both and have one active, just to save steps. Do you always need both open in your db?
    Thanks!

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    No, no, you do not need them both open to run the front-end, you only need to open the back-end if you want to edit its structure (add\delete\edit tables and fields). All data processing is done in the front-end via the linked tables. You will notice that if you open the front-end and look in the Windows Exploerer both files (FE AND BE) will have the associated locking files visible (Test_FE.accdb\Test_FE.laccdb and Test_BE.accdb\Test_BE.laccdb).
    Some reading:
    https://www.fmsinc.com/microsoftacce...abaseSplitter/
    https://support.microsoft.com/en-us/...3-51b1d73498cc
    https://www.yourofficeanywhere.co.uk...cess-database/

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Found my problem! I made Backup Copies of FE and BE as I was working with making changes. I had the wrong BE opened, so things like edits to structure (add\delete\edit tables) didn't work. I don't need backups to BE like I need to the FE and you need to know what BE is linked to FE.
    Now I can move on, more to learn.
    Thanks to all that helped!
    Mad-Tom

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

Similar Threads

  1. Split Dadatase Questions
    By sheusz in forum Database Design
    Replies: 4
    Last Post: 08-05-2020, 09:52 PM
  2. Replies: 3
    Last Post: 08-01-2017, 01:14 PM
  3. Replies: 3
    Last Post: 11-03-2015, 11:19 PM
  4. Replies: 3
    Last Post: 12-06-2012, 09:27 AM
  5. QBF Questions
    By brandonze in forum Forms
    Replies: 2
    Last Post: 08-04-2011, 01:49 PM

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