Importing Excel Data by Using TransferSpreadsheet 2

[JT3]

I am trying to import Excel data into Access tables. I have set up a macro to pull the data into the appropriate tables; however, we don't want to have empty records. There are occasions where the user won't be filling in all fields.

Someone suggested creating temporary tables and running a delete query prior to moving the data into the final tables. I would appreciate some help in coding this. If someone has an example I could follow, I'd greatly appreciate it.

 

[Schof]

Here's some code to help you along...

Dim tableName As String
Dim spreadsheet As String

tableName = "TEST"
spreadsheet = "C:\..\TEST.XLS"

' Create a copy of the table (structure) into which the spreadsheet data will be imported
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentProject.fullName, acTable, tableName, "TEMP_" & tableName, True

' Import the spreadsheet into the temp table
DoCmd.TransferSpreadsheet acImport, , "TEMP_" & tableName, spreadsheet, True

' Purge incomplete records from the temp table
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM TEMP_" & tableName & " WHERE COLUMN1='' AND COLUMN2=''"

' Move the remaining records from the temp table into the permanent table
DoCmd.RunSQL "INSERT INTO " & tableName & " SELECT * FROM TEMP_" & tableName

' Drop the temp table
DoCmd.RunSQL "DROP TABLE TEMP_" & tableName
DoCmd.SetWarnings True

 

[JT3]

Thanks for the assistance. I'm having some difficulty with one statement though.

I'm getting an incomplete query clause runtime error.
Do you see anything missing from:

DoCmd.RunSQL "Delete * FROM 'TEMP_" & tableName & "' WHERE 'FMIB Number' = 0,"

 

[Schof]

I am assuming you have more columns following the comma in the where clause...

Try replacing the single quotes around the table/column names with brackets (I.e. [FMIB Number]).

If that does not work...

Put a breakpoint at the statement and then add "Delete * FROM TEMP_" & tableName & " WHERE 'FMIB Number' = 0," to a watch. Copy the value and then create a new query in design view. Switch to SQL View, paste in the SQL statement (w/o the double quotes) and then run it. If it doesn't run then swtich back to the Query Design and see if it has re-created the query correctly. Normally you will be able to see the problem quite immediately if there is something wrong with it.

Let me know if you still have problems.

 

[JT3]

Thanks. The brackets did the trick...!!!!