Tip - Avoid Duplicate Errors in Bulk INSERTS

This pertains to the Jet 4.0 OLEDB Provider and Access Tables.

First, I have a situation where I will be moving data from Excel worksheets into a larger Access Database. The 'method' is to use the OLEDB Provider, Select the Excel Tables into a Temp Access Table, Insert from the Temp Table into the Main Table, drop the temp table, move to next Excel worksheet.

Although fast and efficient, there will be duplicates. While contemplating error handling, or having to slow things down by doing a row by row insert instead of the bulk INSERT INTO.... I ran into this little gem

simply include

Jet OLEDB:Global Partial Bulk Ops=1

[or in my case I used]

cConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%cMDB%;Jet OLEDB:Global Partial Bulk Ops=1;"

oDB = CreateObject("ADODB.Connection")
oDB.Open(cConn)

and when I issue

:bulkins
cSQL = "INSERT INTO SalesData SELECT * FROM Temp;"
oDB.Execute(cSQL)

duplicates are ignored. One Caveat: SalesData [or whatever your primary table is should you try this] must have a primary key.

P.S. - of course you have to drop the temp table prior to creating it with SELECT INTO. You could enumerate the tables and check if it exists, but just as easy have your error handler do something like

:WBERRORHANDLER
IntControl(73,1,0,0,0)
If StrIndex(StrUpper(wberroradditionalinfo),"DOES NOT EXIST",0,@FWDSCAN) Then goto bulkins

Article ID:   W16572

File Created: 2005:02:18:12:21:30

Last Updated: 2005:02:18:12:21:30

Database Search

TechHome

Tip - Avoid Duplicate Errors in Bulk INSERTS