Results 1 to 8 of 8
  1. #1
    Wayan71 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    9

    VBA copy data from one database to another

    I have 2 different databases:




    1 Master Data
    2 Data Backup


    Please help to create a VBA command button to copy the result of the search in database called "Master Data" to Table called "BackupData" in another database called "Data Backup". See photos
    I use below VBA to deleted olddata after backup has been made to BackupData

    Code:
    Private Sub DeleteOlddata()
    Dim strPasswd
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        On Error GoTo Err_Command6_Click_Error
        strPasswd = InputBox("Enter Password", "Restricted Form")
    
        If strPasswd = "" Or strPasswd = Empty Then
            MsgBox "No Input Provided", vbInformation, "Required Data"
            Exit Sub
        End If
    
        If strPasswd = "12345" Then
        DoCmd.RunCommand acCmdSelectAllRecords
        DoCmd.RunCommand acCmdDelete
        Else
            MsgBox "Sorry, you do not have access to this form", vbOKOnly, _
                "Important Information"
            Exit Sub
        End If
    Exit_ErrorHandler:
      Exit Sub
    Err_Command6_Click_Error:
      If Err.Number = 2501 Then
        MsgBox "Delete action cancelled"
        Resume Exit_ErrorHandler
      Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & _
            ") in procedure Command6_Click of VBA Document Form_T2 at Line " & Erl
        Resume Exit_ErrorHandler
      End If
    
    
    
    End Sub
    
    
    thanks in advance
    Attached Thumbnails Attached Thumbnails Backup Data.jpg  

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Probably the easiest way is to link to the backup db and use an insert query.
    Or with a quick search
    https://www.google.com/search?q=impo...e-gws-wiz-serp

    First link seems to have your solution?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Wayan71 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    9
    Dear Welshgasman,

    please help me how to do, I try many vba but not lucky, if you have time please check my database on attacement
    I have use Quick search on my form date from en date to

    thank you in advance

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can't use Me. in general module - only behind forms and reports - so code in Module2 will not compile. Should fix that.

    Isn't necessary to set a link to Backup table, consider:

    CurrentDb.Execute "INSERT INTO [BackupData] IN 'C:\Users\Owner\Desktop\Backup Data.accdb' SELECT * FROM [Input Data] WHERE ID = " & Me!ID
    CurrentDb.Execute "DELETE FROM [Input Data] WHERE ID = " & Me!ID

    Why did you decide to archive records to another file? Could just have Backup table in Master and be sure to regularly back up file.

    However, simplest approach is to have a field in [Input Data] that can flag record as "Archived". Use a Yes/No field and just change its value or even better a date/time field for date of archive. Apply filter criteria to exclude records. Data is not moved nor deleted.

    Then save a backup of "Master Data" file.

    Whichever you use, be sure to regularly create backups of file(s), because, what if either file gets corrupted?

    Strongly advise not to use spaces in naming convention.
    Last edited by June7; 09-25-2022 at 10:40 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.

  5. #5
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by xps35 View Post
    Nicely spotted. I cannot see it, as no option to select cookies.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's why Wayan71 is on my ignore list. Old habits die hard.
    https://www.accessforums.net/showthread.php?t=84716

    see posts 3 and 4 there.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Now also crossposted at https://www.access-programmers.co.uk.../#post-1845853, so now on my list as well.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Copy data from one database to another ADOX
    By sisva18 in forum Programming
    Replies: 4
    Last Post: 09-23-2021, 07:35 PM
  2. Need to copy a lot of data from old to new database
    By ultrarunner2017 in forum Access
    Replies: 21
    Last Post: 02-10-2020, 07:52 AM
  3. A quick way to copy all tables from a database (data only)
    By earlcools in forum Import/Export Data
    Replies: 1
    Last Post: 10-13-2017, 02:48 AM
  4. Replies: 1
    Last Post: 05-16-2016, 05:58 AM
  5. Copy data from table in another database
    By jcc285 in forum Import/Export Data
    Replies: 8
    Last Post: 04-07-2016, 05:12 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