My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Append Records From Xls File
|
|
 Mar 22 2012, 01:11 PM
Post
#1
|
|
|
UtterAccess Guru Posts: 568  |
Here is my code:
CODE Private Sub btnAppendClientGapData_Click() 'YES Dim strsql As String Dim strFilter As String Dim strInputFileName As String Dim lngFlags As Long Dim tname As String strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS") strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", "*.MDA;*.MDB") strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF") strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT") strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*") strInputFileName = ahtCommonFileOpenSave( _ Filter:=strFilter, OpenFile:=True, _ DialogTitle:="Please select an input file...", _ Flags:=ahtOFN_HIDEREADONLY) 'TRAP FOR ANY ERRORS On Error Resume Next tname = " tblParticipant_Client_Data_ORIGINAL " 'CLOSE TABLES IF OPEN DoCmd.Close acTable, "tblParticipant_Client_Data_ORIGINAL", acSaveYes DoCmd.Close acTable, "tblParticipant_Client_Data_MAPPED", acSaveYes DoCmd.Close acTable, "tblParticipant_EV_Data", acSaveYes 'CLEAR RECORDS FROM STATIC TABLES DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM tblParticipant_Client_Data_MAPPED" DoCmd.RunSQL "DELETE * FROM tblParticipant_EV_Data" DoCmd.SetWarnings True 'SELECT CLIENT GAP DATA FILE -- THIS APPEND RECORDS TO EXISTING DATA!! strsql = "INSERT INTO tblParticipant_Client_Data_Original " & _ "SELECT *" & " FROM " & strInputFileName End Sub This is the part I am having a problem with: CODE 'SELECT CLIENT GAP DATA FILE -- THIS APPEND RECORDS TO EXISTING DATA!! strsql = "INSERT INTO tblParticipant_Client_Data_Original " & _ "SELECT *" & " FROM " & strInputFileName In the Immediate window it produces this: CODE ?strsql INSERT INTO tblParticipant_Client_Data_Original SELECT * FROM C:\Documents and Settings\corrieann.gillen\Desktop\Test Participant Data2.xls But it does not append the data to the existing table: tblParticipant_Client_Data_ORIGINAL WHY? |
 |
 
|
|
Mar 22 2012, 02:28 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 17,797 From: Don Mills, ON (Canada) |
You cannot run SQL against a file name like that.
You'll need to use TransferSpreadsheet to create a linked table pointing to the spreadsheet, then use that linked table in the SQL statement. |
|
|
|
|
Mar 22 2012, 02:52 PM
Post
#3
|
|
|
UtterAccess Guru Posts: 568 |
Ok, so I did this:
CODE 'SELECT CLIENT GAP DATA FILE -- THIS CREATES A NEW TABLE TO STORE GAP DATA If Len(strInputFileName) > 0 Then DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel51, "tblParticipant_Client_Data_GAP", strInputFileName, True Else MsgBox "No File Selected" End If 'APPEND CLIENT GAP DATA FILE TO CLIENT DATA ORIGIANL-- THIS APPENDS RECORDS TO EXISTING CLIENT DATA!! strsql = "INSERT INTO tblParticipant_Client_Data_Original SELECT * FROM tblParticipant_Client_Data_GAP" db.Execute strsql End Sub The table to hold the GAP data successfully creates. But I STILL cannot get the data to move from there into tblParticipant_Client_Data_ORIGINAL. CODE 'APPEND CLIENT GAP DATA FILE TO CLIENT DATA ORIGIANL-- THIS APPENDS RECORDS TO EXISTING CLIENT DATA!!
strsql = "INSERT INTO tblParticipant_Client_Data_Original SELECT * FROM tblParticipant_Client_Data_GAP" db.Execute strsql |
|
|
|
|
Mar 22 2012, 03:44 PM
Post
#4
|
|
|
UtterAccess Guru Posts: 568 |
Got it:
CODE 'APPEND CLIENT GAP DATA FILE TO CLIENT DATA ORIGIANL-- THIS APPENDS RECORDS TO EXISTING CLIENT DATA!!
DoCmd.SetWarnings False DoCmd.RunSQL "INSERT INTO tblParticipant_Client_Data_Original SELECT * FROM tblParticipant_Client_Data_GAP" DoCmd.SetWarnings True |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 18th June 2013 - 07:14 PM |