Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    5

    Question Unanswered: VBA to import table without replacing destination table

    dear all,

    kindly need your help.

    I have some table like this:
    Click image for larger version. 

Name:	FtW6Cn.png 
Views:	3 
Size:	21.2 KB 
ID:	14153

    I need to import from other tables (in other DB) with the same columns but have different date values. (130205, 130206, etc..)
    Does anyone have code that I could use to do this without replacing the destination table?
    so that the destination table can be act like a "merged" table for whole date values.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    With:
    - Tbl_Destination being the destination table in the current database,
    - Tbl_Source being the source table in the External database,
    - D:\Path\External.mdb being the full path to the external database,
    - Column1, Column2, etc being the names of the columns in both tables (Tbl_Source and Tbl_Destination).
    Try:
    Code:
    Sub MergeExternalData()
    
        Const c_SQL As String = "INSERT INTO Tbl_Destination (Column1, Column2, Column3, Column4, Column5, Column6, Column7 ) " & _
                                "SELECT Column1, Column2, Column3, Column4, Column5, Column6, Column7 " & _
                                "FROM Tbl_Source IN 'D:\Path\External.mdb';"
                                
        CurrentDb.Execute c_SQL, dbFailOnError
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    5
    many thank's.

    I have changed your given code into my needs as follow:

    Code:
    Private Sub Command0_Click()
       
       Const c_SQL As String = "INSERT INTO GTP_DownlinkPacketsBuff_U_RNC_ROP_RAW (OBJECT_ID,DATE,PERIOD,EXCHID,PERLEN,GTP_DownlinkPacketsBuff_U,GTP_GtpuInDataOctIu,GTP_GtpuInDataPktIu,GTP_GtpuOutDataOctIu) " & _
                               "SELECT OBJECT_ID,DATE,PERIOD,EXCHID,PERLEN,GTP_DownlinkPacketsBuff_U,GTP_GtpuInDataOctIu,GTP_GtpuInDataPktIu,GTP_GtpuOutDataOctIu " & _
                               "FROM GTP_DownlinkPacketsBuff_U_RNC_ROP_RAW IN 'F:\100.PARSE\_result\temp\SGBDG1_20130205_nodepdc2.mdb';"
       CurrentDb.Execute c_SQL, dbFailOnError
       
    End Sub
    then when I execute the code it returns "Syntax error in insert into statement".

    is there any problem to execute INSERT INTO statement if both table names are exactly the same (GTP_DownlinkPacketsBuff_U_RNC_ROP_RAW), even if they're exist in different DB?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Split the the query to localize the problem. Create a new query, open it in SQL view then type (or paste from here):
    Code:
    SELECT OBJECT_ID,DATE,PERIOD,EXCHID,PERLEN,GTP_DownlinkPacketsBuff_U,GTP_GtpuInDataOctIu,GTP_GtpuInDataPktIu,GTP_GtpuOutDataOctIu 
      FROM GTP_DownlinkPacketsBuff_U_RNC_ROP_RAW IN 'F:\100.PARSE\_result\temp\SGBDG1_20130205_nodepdc2.mdb';
    Then try to switch to Datasheet view and see what happens.
    Have a nice day!

  5. #5
    Join Date
    Feb 2013
    Posts
    5
    I did some change in code snippet as below and it works fine:

    Code:
    Dim SQL As String
    
    SQL = "INSERT INTO table_name "
    SQL = SQL & "SELECT * "
    SQL = SQL & " FROM table_name" & " " & "IN 'G:\SGSN\Database.mdb'; "
    
    CurrentDb.Execute SQL
    anyway, thank's for giving me clue regarding to this case.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •