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.
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

  • : 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.
    :


    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.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion