import excel record to access

[xyhecho]

hello every one in excel i have very much record to import to access,how can i cortrol not to import duplicate record before updata?

Sub newadd()
Dim CNN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim pthStr As String
Dim SQL As String
Dim ws As Worksheet
Dim i As Long
B1 = [a65536].End(xlUp).Row
pthStr = "C:\Documents and Settings\xyh\11\aa.mdb"
CNN.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & pthStr
SQL = "select * from talbe1"
RS.Open SQL, CNN, adOpenKeyset, adLockOptimistic, adCmdText


For i = 2 To B1
If Range("B" & i).Value <> "" Then
RS.AddNew
RS.Fields("period").Value = IIf(Range("A" & i).Value = "", Null, Range("A" & i).Value)
RS.Fields("cus").Value = IIf(Range("B" & i).Value = "", Null, Range("B" & i).Value)
RS.Fields("producer").Value = IIf(Range("C" & i).Value = "", Null, Range("C" & i).Value)
RS.Fields("sale").Value = IIf(Range("D" & i).Value = "", Null, Range("D" & i).Value)
RS.Update
End If
Next i
Set RS = Nothing
CNN.Close
End Sub

[stanl]

If your Excel worksheet is set up with columns to resemble the Access table, you can add

Jet OLEDB:Global Partial Bulk Ops=1

to your connect string, then just use a Connection Object's execute() method to SELECT INTO...FROM; otherwise do a find on a primary key before addnew(); otherwise create a temporary table then call and Access append query that does not allow duplicates.

There may be other,better suggestions. Stan

[OBP]

As Stanl says, if the layout of the excel sheet is the same as an Excel table then you can just import the whole worksheet and run an Append Query all done from Access.
This only takes a few lines of code in Access against all those lines of code in Excel.

This is one that I use to put the Excel data in to a Temporary Table called "tbl_Temp"
Dim excelName As String
Dim AppExcel As New Excel.Application
Dim Wkb As Workbook
Dim Wksh As Worksheet
strNewTableName = "tbl_Temp"
Set Wkb = AppExcel.Workbooks.Open(excelName)
For Each Wksh In Wkb.Worksheets
Set xlRng = Wksh.UsedRange
DoCmd.TransferSpreadsheet acImport, , "tbl_Temp", Me.selFileName, True, Range:=Wksh.name & "!a1:co500"
Next Wksh
Wkb.Close
AppExcel.Quit
Set Wkb = Nothing
Set AppExcel = Nothing
DoCmd.OpenQuery "tbl_Temp Query"