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.
consider the case in which access database is having data. The aim is no duplicate values should be entered into the Database.
Please help me to solve this problem.
Comments
: 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.
: consider the case in which access database is having data. The aim is no duplicate values should be entered into the Database.
: Please help me to solve this problem.
:
a primary key or index w/ no dupe's would stop inserting dupe records but may raise an error message. another option is to insert all records then walk thru the records and delete the dupe's.