Import and Append Data (1 Viewer)

Dear all,

I am trying to import an excel sheet (with the exactly same format / structure) into access database by using one button created in Form. Not sure how should I go about it, if i want to

1. Import and add the records into tables without deleted the previous records.
2. Replace Field1 if the new records matched.

The following is the code I am using...

Private Sub Command11_Click()

Dim myfile As String, sql As String
Dim db As Database, rs As Recordset, rs_target As Recordset

Set db = DBEngine(0)(0)

myfile = OpenFile("c:\", "xls", "*.xls", 0)
If myfile = "" Then
End
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from Tbl_Import"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_Import", myfile ', , "Importfile!"

Set rs = db.OpenRecordset("Tbl_Import")


DoCmd.SetWarnings True


MsgBox "File has been imported "

End Sub

leongkeat said:

Private Sub Command11_Click()

Click to expand...

Dont use default names, always rename the button to a 'proper' name

Use [ code] and [/code ] tags ( without the spaces) around any code you post on the forum

Dim db As Database, rs As Recordset, rs_target As Recordset
You will want to disambiguate these, by adding DAO. in front of each. Moreso you dont use them at all, why declare them??

Now for your question, Use a temporary table to import your data, then use Append and Update queries to add/update the data in your target table.

Thanks! Most of the time I was just reading all the message and try to learn from there...

Hardly post anything here..so was unaware of the rules...

Is there any quick fixed to solve this? I was thinking to change the DoCmd.Run SQL delete into some other command that can directly avoid using append / update queries? Possible?

Thanks for helping~

Instead of docmd. you can use Currentdb.Execute

In both situations though you can instead of "query name" you can use "Select * fr...." to directly execute the query.