none
Appending from Excel into Access table RRS feed

  • Question

  • Hi all

    I'm trying to find something similar to the TransferSpreadSheet command to import and append records to an existing Access table.

    I don't want to link, I want to append.

    thanks

    Wednesday, February 22, 2012 3:24 PM

Answers

  • Hi Dorris,

    Ok, so if you want to run this in an Access Database, then below code will be sufficient:

    Private Sub cmdImportFile_Click()
    
       Dim dbs As DAO.Database
       Dim td As DAO.TableDef
          
       ' set dbs to current database
       Set dbs = CurrentDb
       
       ' loop through all tables in current database
       For Each td In dbs.TableDefs
       ' check if Temp table is still in current database
          If td.Name = "DLPTemp" Then
            ' it is, so drop it
             dbs.Execute "Drop Table DLPTemp;"
          End If
       Next
        
       ' import table
          DoCmd.TransferSpreadsheet acImport, , "DLPTemp", "C:\users\dantheman\documents\book1.xlsx", True
       
       ' execute the insert SQL from temp table to Export
         dbs.Execute "INSERT INTO DLPTExport (LastName, FirstName, [Test Location ID], [Start Test Date]," & _
          "[End Test Date], [Test Score]) " & _
          "SELECT [Last Name], [First Name], [Test Location ID], [Start Test Date], [End Test Date], [Test Score] " & _
          "FROM DLPTemp WHERE [Last Name] & [Start Test Date] Not In" & _
          "(SELECT LastName & [Start Test Date] FROM DLPTExport);"
       
       ' drop table
       dbs.Execute "Drop Table DLPTemp;"
       
      ' release objects
       
       Set dbs = Nothing
       Set td = Nothing
    
    End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    • Marked as answer by Dorris Beaird Friday, March 2, 2012 2:06 PM
    Thursday, March 1, 2012 11:49 PM
    Moderator

All replies

  • A line such as

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTest", "Book1.xlsx", True

    will create tblTest if it doesn't exist yet, and append records to the existing ones in tblTest if it already exists.

    Regards, Hans Vogelaar


    Wednesday, February 22, 2012 3:32 PM
  • Hans

    EDIT:  apparently when I made some changes to the spreadsheet, it got saved in a version of excel that the version of Access couldn't handle.  Resaved as an Excel 97 and it seemed to find it.  Now I'm getting a whole different error.

    The user only wants certain columns from the spreadsheet - which is how the table is set up - but apparently Access wants the fields to match.  Is there a way around this?

    Thanks

    Wednesday, February 22, 2012 3:57 PM
  • I've tested the code - it does append records to an existing table.

    Try solving the path problem first, or test with another path and file.


    Regards, Hans Vogelaar

    Wednesday, February 22, 2012 4:36 PM
  • If you are running 2003 or earlier change

    acSpreadsheetTypeExcel12Xml
    to say
    acSpreadsheetTypeExcel9  or intrinsic value 8

    Peter Thornton

    Wednesday, February 22, 2012 7:23 PM
    Moderator
  • Apparently when I made some data changes to the spreadsheet and saved it, it got saved in a version of excel that the version of Access couldn't handle.  Resaved as an Excel 97 and it seemed to find it.  Now I'm getting a whole different error.

    The user only wants certain columns from the spreadsheet - which is how the table is set up - but apparently Access wants the fields to match.  Is there a way around this?

    Thanks

    Wednesday, February 22, 2012 8:38 PM
  • You can link the spreadsheet to your Access database, and then do your stuff with it.

    Another approach is import the spreadsheet in a Temp table, append only the columns needed into the Table where you want the Data, then remove the Temp Table when you are done.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Wednesday, February 22, 2012 10:14 PM
    Moderator
  • Daniel

    What a difference the weekend makes.  Friday, when I left, it seemed to be working, but now - not so much.  I'm getting a "can't find table DLPTemp" when it created the file just two lines above.  Here's the code:

    Private Sub cmdImportFile_Click()
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "DLPTemp", "C:\Temp\DLPTExport.xls", True
      Dim dbs As Database
      Set dbs = OpenDatabase("c:\testaccesss\DLPT.mdb")
      dbs.Execute "INSERT INTO DLPTExport (LastName, FirstName, [Test Location ID], [Start Test Date]," & _
         "[End Test Date], [Test Score]) " & _
         "SELECT [Last Name], [First Name], [Test Location ID], [Start Test Date], [End Test Date], [Test Score] " & _
         "FROM DLPTemp WHERE [Last Name] & [Start Test Date] Not In" & _
         "(SELECT LastName & [Start Test Date] FROM DLPTExport);"
      dbs.Execute "Drop Table DLPTemp;"
      dbs.Close
    End Sub

    I know it's creating the table DLPTemp, the error occurs on the dbs.Execute line

     

    Tuesday, February 28, 2012 3:48 PM
  • It looks like you are importing the spreadsheet into the current database, while you are trying to use it another opened database DLPT.mdb.

    Therefore it's not able to find the Table.

    Try to change the code into this:

    Private Sub cmdImportFile_Click()
    
       Dim dbs As Database
       Set dbs = OpenDatabase("c:\testaccesss\DLPT.mdb")
       
       ' import the sheet in the new opened mdb
       dbs.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "DLPTemp", "C:\Temp\DLPTExport.xls", True
    
       dbs.Execute "INSERT INTO DLPTExport (LastName, FirstName, [Test Location ID], [Start Test Date]," & _
          "[End Test Date], [Test Score]) " & _
          "SELECT [Last Name], [First Name], [Test Location ID], [Start Test Date], [End Test Date], [Test Score] " & _
          "FROM DLPTemp WHERE [Last Name] & [Start Test Date] Not In" & _
          "(SELECT LastName & [Start Test Date] FROM DLPTExport);"
       dbs.Execute "Drop Table DLPTemp;"
       dbs.Close
     End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Tuesday, February 28, 2012 5:08 PM
    Moderator
  • Hi Dan

    Moved the database open to the top as you suggested, which makes sense, but I'm still getting the same error message.  Do I need to drop the temp table before i try running it? 

    Tuesday, February 28, 2012 9:51 PM
  • Hi Dorris,

    Do you want to import in an another Database, or just import into another TempTable in the Current Database?

    These are two different methods.

    If I understand you correctly it seems you want to do this in the Current database using a TempTable? Correct?

    In that case, you don't need to open another Database, and can run it in the current database.

    Let me know what exactly you need, so we can adjust the code to your needs.


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Tuesday, February 28, 2012 10:16 PM
    Moderator
  • Hi Dan,

    Sorry, been a wild couple of days here. 

    What I'm needing to do is import certain fields out of an Excel file into an existing table in the database - and Access is not my native language.  So, what I want to do is import the Excel file into a temp table, insert the appropriate fields into the table in the database - if they're not already there - and then get rid of the temp table.

    Dorris

    Thursday, March 1, 2012 2:16 PM
  • Hi Dorris,

    Ok, so if you want to run this in an Access Database, then below code will be sufficient:

    Private Sub cmdImportFile_Click()
    
       Dim dbs As DAO.Database
       Dim td As DAO.TableDef
          
       ' set dbs to current database
       Set dbs = CurrentDb
       
       ' loop through all tables in current database
       For Each td In dbs.TableDefs
       ' check if Temp table is still in current database
          If td.Name = "DLPTemp" Then
            ' it is, so drop it
             dbs.Execute "Drop Table DLPTemp;"
          End If
       Next
        
       ' import table
          DoCmd.TransferSpreadsheet acImport, , "DLPTemp", "C:\users\dantheman\documents\book1.xlsx", True
       
       ' execute the insert SQL from temp table to Export
         dbs.Execute "INSERT INTO DLPTExport (LastName, FirstName, [Test Location ID], [Start Test Date]," & _
          "[End Test Date], [Test Score]) " & _
          "SELECT [Last Name], [First Name], [Test Location ID], [Start Test Date], [End Test Date], [Test Score] " & _
          "FROM DLPTemp WHERE [Last Name] & [Start Test Date] Not In" & _
          "(SELECT LastName & [Start Test Date] FROM DLPTExport);"
       
       ' drop table
       dbs.Execute "Drop Table DLPTemp;"
       
      ' release objects
       
       Set dbs = Nothing
       Set td = Nothing
    
    End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    • Marked as answer by Dorris Beaird Friday, March 2, 2012 2:06 PM
    Thursday, March 1, 2012 11:49 PM
    Moderator
  • Dan

    That works exactly as I wanted it to.

    Thanks so much

    Dorris

    Friday, March 2, 2012 2:07 PM