How to import data from Excel to Access Database through Vb Code?
I want to import data from Excel to Access Database through Vb Code.
For this I used the following code :
CommonDialog1.InitDir = ""
CommonDialog1.DialogTitle = "Select the Excel File to import from"
CommonDialog1.Filter = "Excel(*.xls)|*.xls"
CommonDialog1.ShowOpen
If CommonDialog1.FileName <> "" Then
If FSO.FileExists(CommonDialog1.FileName) = True Then
OpenTAConn 'This opens the database and objTAConn is the ADO recordset
sqlString = "INSERT INTO test SELECT * from [Excel 8.0;DATABASE=" & CommonDialog1.FileName & ";HDR=YES;IMEX=1].[Sheet1$];"
Else
MsgBox "File not exists"
Exit Sub
End If
CommonDialog1.FileName = ""
objTAConn.Execute sqlString
MsgBox "Successfully Imported"
objTAConn.Close
Set objTAConn = Nothing
This code is working fine but for each time When it is running, the data are inserted instead of appending.
The aim is no duplicate vaules should be entered.
Please help me to solve this problem.
Comments
When you create the file 'test' in Access, that you are importing to, create a master key field and disallow duplicates. You may get an error that you could probably handle in code about duplicate keys not being allowed, but it won't import the duplicate keys.
Either you can handle the error in code, or you can disregard errors for the import operation.
The order data is imported, appended or inserted is not significant, you re-order you data for the forms and/or reports you make from that data. The master key will stop the duplicates from importing.
Good Luck!
Refer to this sample: http://smartcoder.awardspace.com/viewer.php?id=85
Read the sample and you will have a good idea to solve your problem
KaBalweg
http://smartcoder.awardspace.com/
instaled net frame exe