Results 1 to 9 of 9
  1. #1
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70

    Copy data from table in another database

    I need to copy the data from a table in one database to the same table in another database using VBA

    I cannot export the table using Access 2016 because that requires the deletion of Relationships.

    So, if I have tbl_Data in my source database (DbS) and tbl_data in the CurrentDb, I would like an SQL statement like

    INSERT INTO tbl_Data SELECT * FROM (C:\Temp\DbS\tbl_Data)

    Despite many attempts I have not managed to construct the FROM clause as shown

    Alternatively, I could set the DbS path as a String as strSource = "C:\Temp\Dbs" which could be incorporated into the SQL, however, I don't then know how to reference the tbl_Data.



    Any help gratefully received.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Attach the external table, then run a query.

  3. #3
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by ranman256 View Post
    Attach the external table, then run a query.
    Thanks for reply but...

    I have to copy data from 15 tables from an old Db to a New one so I was looking to create a loop structure to deal with these one at a time in VBA.
    The only piece I'm struggling for is how to reference the external table in the SQL FROM Clause.

    I don't know how to 'attach' an external table.

  4. #4
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    in the db you wish to have the data in, click on the External Data tab and then Access. Either Import or link your tables from the older db.

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can,
    make a table of the db paths.
    loop thru the db paths , and re-assign the link to the main table, and run import query.

    Code:
    dim tdf as tabledef
    set rst = currentdb.openrecordset("tDbPaths")
    with rst
       vPath = .fields("DbPath").value 
       set tdf = currentdb.tabledefs("tExtTable")
        tdf.Connect = ";DATABASE=" & vPath & ";"
        tdf.RefreshLink
    
       docmd.openquery "qaImportData" 
      
       .movenext
    end with
    set tdf = nothing
    set rst = nothing

  6. #6
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by alansidman View Post
    in the db you wish to have the data in, click on the External Data tab and then Access. Either Import or link your tables from the older db.
    Thanks for response, however, I'm not sure you read what I asked?

    1. Import doesn't work without deleting the table relationships
    2. I want the data 'in' the new database not a link to it from the new database.

  7. #7
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70
    Thank you, I'll give that a try

  8. #8
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    First I should to ask
    What is the path you wont to send the current db to sources (like: d:\dbfiles\dbs.accde)
    Second question: what is the pk for your tbl_data
    Or can you send ur db to me to figure it out,
    I use send data too
    From current db to source.
    May I help you


    Sent from my iPhone using Tapatalk

  9. #9
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70
    This has solved my problem. I was missing quite a bit from the SQL string

    Option Compare Database
    Option Explicit

    Private Sub TransferData()
    Dim strTargetDb As String
    strTargetDb = "C:\Temp\NewDb.accdb"
    'Set Database variables
    Dim dBCurrent As DAO.Database
    Dim strSQL As String
    Set dBCurrent = CurrentDb()
    '------------------------------------------------------------------------------------------
    'tbl_Members
    strSQL = "INSERT INTO tbl_Members (Membership_Number,….,….)” & _
    "IN " & "'" & strTargetDb & "'" & _
    “ SELECT Membership_Number, ….,….” & _
    "FROM tbl_Members"
    dBCurrent.Execute strSQL, dbFailOnError
    Debug.Print "tbl_Members data transferred"

    Thanks to those who offered help.

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

Similar Threads

  1. copy a table into the BE database
    By crowegreg in forum Database Design
    Replies: 8
    Last Post: 09-09-2013, 04:01 PM
  2. Copy part of database from table
    By samirmehta19 in forum Import/Export Data
    Replies: 1
    Last Post: 09-06-2013, 01:10 PM
  3. Move or Copy a table to another database
    By Harley Guy in forum Access
    Replies: 7
    Last Post: 11-08-2010, 11:49 PM
  4. Replies: 4
    Last Post: 08-12-2010, 08:38 AM
  5. Copy one table to another database
    By ashthebear in forum Access
    Replies: 2
    Last Post: 06-15-2010, 08:46 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