Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    gstylianou is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    167

    Automatically import all objects from one database to other

    Hello everyone,




    Is there any way so that to open another access database through the existing which i use? If yes, can i do something more.... I just wanted via command button to open another database and the to import all the objects into the existing..


    I hope someone can enlighten me.


    Thank you very much

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Never tried but I expect it is possible but why would you need to? Complicated code.
    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
    gstylianou is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    167
    Hi June7,

    I need this for automatic updates. What i mean:

    I have a database with 3 users. This database is on developing mode since many month and its not completed. So i want to finish with developing but i don't want each time to delete the old file and add the new one on 3 different pc. So i will put the updated objects in a new access file and then to send it via email to my users asking from the to just pressing one command button so to import all the old objects into a new file.

    I thing is a good idea... Any other ideas is welcoming..

    Thanks in advanced man.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are these users on the same network with access to the same file server? These users need to share the data?
    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
    gstylianou is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    167
    No, are not in the same network. Thats why i must find a quick but stable solution

    Do you have any idea?

    Thanks again

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Importing the objects would require first deleting the existing objects.

    A split db design might be best. Then just send the user the frontend replacement. The trick here will be managing the table links to the backend. This can be done programmatically.
    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.

  7. #7
    gstylianou is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    167
    June7 thank you for your hekp. Can you help me to that?

    Is it possible for a sample?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Bing: Access split database

    Can use wizard as explained in http://office.microsoft.com/en-us/ac...010342026.aspx

    Or just make copy of database and delete tables from one and delete everything else from the other.

    Then will need code that will set the table links when your users get revised frontend. This will be simplified if user puts frontend and backend in the same folder.
    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.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Steve has some sample code here to update links via VBA.
    https://www.accessforums.net/import-...tml#post196237

  10. #10
    gstylianou is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    167
    Good morning to all and thank you very much for your help.

    The issue is:

    i must use the import / export method because of the followings issues :

    1. I have in mind to add some new fields into some of the existing tables. Moreover i must add new tables in the near future so the solution with the front end / back end i thing will be usefulness. Please note that, the database is "live" till now with 3 active users working with it. So i cannot stop them to use the file..

    2. I thing the idea with import / export the objects is the best for my case because:

    a) i will make changes on the development file and then i will import them into a new access file with different (but similar) objects name.

    b) I will send the new file via email to my users asking from them to save the file on C:/.....

    c) On the main form of the new file i will add 3 commands buttons. Step1: One for importing the old objects from the old file into a new - Step2: Second button will find and delete the old objects which now are updated with the new objects (which till now has a different name into a new file) and Step3: The last button just rename the new objects with the same name with the deleted updated objects

    So, i thing the above solution will be very helpful for my case because i will do my changes on under developing file, and the users will not stop their job. They must only follow my instructions on how can they make the update of their file (just pressing the above 3 buttons)

    Thats why i'm looking to find the code in order to import / export the objects from one to other access file. For the next steps i can use the following code in order to find the old updated objects and then to rename them:

    DoCmd.DeleteObject acTable, "Table1"
    DoCmd.CopyObject , "Table2", acTable, "Table1"
    DoCmd.Rename "Table1", acTable, "Table2"

    Waiting your ideas i would like to thank you once time again.

  11. #11
    gstylianou is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    167
    Guys i found this code but it doesn't work and don't know why..



    '---------------------------------------------------------------------------------------
    ' Procedure : ExpObj2ExtDb
    ' Author : Daniel Pineault, CARDA Consultants Inc.
    ' Website : http://www.cardaconsultants.com
    ' Purpose : Export all the database object to another database
    ' Copyright : The following may be altered and reused as you wish so long as the
    ' copyright notice is left unchanged (including Author, Website and
    ' Copyright). It may not be sold/resold or reposted on other sites (links
    ' back to this site are allowed).
    '
    ' Input Variables:
    ' ~~~~~~~~~~~~~~~~
    ' sExtDb : Fully qualified path and filename of the database to export the objects
    ' to.
    '
    ' Usage:
    ' ~~~~~~
    ' ExpObj2ExtDb "c:\databases\dbtest.accdb"
    '
    ' Revision History:
    ' Rev Date(yyyy/mm/dd) Description
    ' ************************************************** ************************************
    ' 1 2008-Sep-27 Initial Release
    '---------------------------------------------------------------------------------------
    Public Sub ExpObj2ExtDb(sExtDb As String)
    ' On Error GoTo Error_Handler
    Dim qdf As QueryDef
    Dim tdf As TableDef
    Dim obj As AccessObject

    ' Forms.
    For Each obj In CurrentProject.AllForms
    DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
    acForm, obj.Name, obj.Name, False
    Next obj

    ' Macros.
    For Each obj In CurrentProject.AllMacros
    DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
    acMacro, obj.Name, obj.Name, False
    Next obj

    ' Modules.
    For Each obj In CurrentProject.AllModules
    DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
    acModule, obj.Name, obj.Name, False
    Next obj

    ' Queries.
    For Each qdf In CurrentDb.QueryDefs
    If Left(qdf.Name, 1) <> "~" Then 'Ignore/Skip system generated queries
    DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
    acQuery, qdf.Name, qdf.Name, False
    End If
    Next qdf

    ' Reports.
    For Each obj In CurrentProject.AllReports
    DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
    acReport, obj.Name, obj.Name, False
    Next obj

    ' Tables.
    For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then 'Ignore/Skip system tables
    DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
    acTable, tdf.Name, tdf.Name, False
    End If
    Next tdf

    Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set tdf = Nothing
    Set obj = Nothing
    Exit Sub

    'Error_Handler:
    ' MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
    ' "Error Number: " & Err.Number & vbCrLf & _
    ' "Error Source: " & sModule4 & "/ExpObj2ExtDb" & vbCrLf & _
    ' "Error Description: " & Err.Description, _
    ' vbCritical, "An Error has Occured!"
    ' Resume Error_Handler_Exit
    End Sub

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by gstylianou View Post
    ...The issue is:...
    I do not think your situation is much different, if any different, than any developer working with Access. When I edit production DB's e.g., back end files, I do it after hours and when everybody is kicked off of the DB. I then open it in exclusive mode and keep it open in exclusive mode by opening an object in edit mode. Then, I will go to work on the tables referring to a list of changes from my notes. I am not aware of a better approach than this.

    I have done import procedures before where I move the data to empty shells etcetera, but I try to avoid this by making small changes to BE files.

    As for the FE files. There are ways to copy master copies to your client's local machine via external script. This is my preferred method.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If each user has their own independent set of tables and not sharing, doesn't matter if the db is split or all-in-one - if you edit the table structure, how do you expect to give them the new structure without losing their data?
    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.

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by June7 View Post
    If each user has their own independent set of tables and not sharing, doesn't matter if the db is split or all-in-one ..
    If many updates in a short period of time are expected because of an Alpha or Beta version, a split design where the FE and BE are local to the User and automation is implemented would be the best approach, I suspect.

  15. #15
    gstylianou is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    167
    June7 every end of the day the users export the critical data via email so about the issue which you refer, its ok.

    If there is a solution in order to import and export the objects i will be very happy. Have you check the code which i have already attached regarding this? I don't know whats wrong and it doesn't work..

    Thanks in advanced

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 43
    Last Post: 03-27-2014, 01:51 AM
  2. Replies: 5
    Last Post: 05-17-2012, 06:23 PM
  3. import excel files into access automatically
    By jstei012 in forum Import/Export Data
    Replies: 1
    Last Post: 12-19-2011, 04:12 PM
  4. Cannot import objects from secured database
    By focosi in forum Security
    Replies: 2
    Last Post: 09-10-2011, 02:33 AM
  5. IMPORT-ALL-OBJECTS is missing Tool
    By pacala_ba in forum Import/Export Data
    Replies: 0
    Last Post: 03-25-2009, 10:13 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