Export a range of data From Excel to Access Database and check for duplication

[LeoLee]

Hi Guys,

I would greatly appreciate if anyone could help me with my problems.

My project require me to export data from Excel to Access Database via VBA.

Here's an overview of the requirement:

A user will enter data into the Excel Template and afterwhich, through clicking a Buttom, it will automatically export the entered data in Excel to Access and check for duplication data in Access database. In the event that there is a duplication in the database, it will prompt the user that there is a duplication of data.



I am able to export data from Excel to Access through the code from

excelguru.ca/node/18

and these code do not check for any duplication of data in access.

Unfortunately, I do not have enough knowledge to understand and to modify these codes to check for duplication from the Access database. I would really appreciate if any1 could share some light on how to modify it or other useful resource to meet my requirement.
:banghead:
Here's a look at the code to export data from Excel to Access without checking of duplication:


Option Explicit
Sub DB_Insert_via_ADOSQL()
'Author : Ken Puls (excelguru)
'Macro purpose: To add record to Access database using ADO and SQL
'NOTE: Reference to Microsoft ActiveX Data Objects Libary required
Dim cnt As New ADODB.Connection, _
rst As New ADODB.Recordset, _
dbPath As String, _
tblName As String, _
rngColHeads As Range, _
rngTblRcds As Range, _
colHead As String, _
rcdDetail As String, _
ch As Integer, _
cl As Integer, _
notNull As Boolean
'Set the string to the path of your database as defined on the worksheet
dbPath = ActiveSheet.Range("B1").Value
tblName = ActiveSheet.Range("B2").Value
Set rngColHeads = ActiveSheet.Range("tblHeadings")
Set rngTblRcds = ActiveSheet.Range("tblRecords")
'Concatenate a string with the names of the column headings
colHead = " ("
For ch = 1 To rngColHeads.Count
colHead = colHead & rngColHeads.Columns(ch).Value
Select Case ch
Case Is = rngColHeads.Count
colHead = colHead & ")"
Case Else
colHead = colHead & ","
End Select
Next ch
'Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbPath & ";"
'Begin transaction processing
On Error GoTo EndUpdate
cnt.BeginTrans
'Insert records into database from worksheet table
For cl = 1 To rngTblRcds.Rows.Count
'Assume record is completely Null, and open record string for concatenation
notNull = False
rcdDetail = "('"
'Evaluate field in the record
For ch = 1 To rngColHeads.Count
Select Case rngTblRcds.Rows(cl).Columns(ch).Value
'if empty, append value of null to string
Case Is = Empty
Select Case ch
Case Is = rngColHeads.Count
rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
Case Else
rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
End Select
'if not empty, set notNull to true, and append value to string
Case Else
notNull = True
Select Case ch
Case Is = rngColHeads.Count
rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
Case Else
rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
End Select
End Select
Next ch
'If record consists of only Null values, do not insert it to table, otherwise
'insert the record
Select Case notNull
Case Is = True
rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
Case Is = False
'do not insert record
End Select
Next cl
EndUpdate:
'Check if error was encounted
If Err.Number <> 0 Then
'Error encountered. Rollback transaction and inform user
On Error Resume Next
cnt.RollbackTrans
MsgBox "There was an error. Update was not succesful!", vbCritical, "Error!"
Else
On Error Resume Next
cnt.CommitTrans
End If
'Close the ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
On Error GoTo 0
End Sub


Any1 know how to modify these codes to check for duplication in Access before the code is entered to the database?

[Bob Phillips]

You could do a read before the insert, but IMO the simplest way is to extract the uniques from the source data and load the extracted data.

[LeoLee]

I realised that we do not need to modify the above codes.

Perhads, we can create a validation code first to check the duplication before we record data into the database.

Any recommendation and suggestion?

[LeoLee]

Hi XLD,

Ya, I just realised that too and posted about the same time as you. haha.

I will extract the data to Excel to do comparison before inserting to the database.

Thanks.

[Bob Phillips]

Validating will modify the above, because you would need to do a read before the insert as I suggested. And I gave you an alternative.

[LeoLee]

Hi XLD,

I think your suggestion is really great. I will follow your suggestion.

Just a quick one, if I record Macro in my Excel and do a Import data from Access to Excel, will it be better than I go and research those specially customized such as the above importing codes provided by excelguru?

I always have this mindset that record Macro will always give us problems as compared to those codes coded by expert.

Thanks a million for your advice..

[Bob Phillips]

As you suspect, a recorded macro will always be less efficient, less maintainable than hand-crafted code. But ... if you get some basic code knocked up, the macro recorder will do fine, and then detail any changes you want, we can help you amend it to work as you need, and to be better code.

Go for it.

[LeoLee]

Hi XLD,

I am not sure if it is correct to continue this thread but the discussion is different from the thread titles. Please correct me if I am not doing this right.

Currently, I am following your suggestion and I have come up the steps require:

Step 1: Record Macro to import data to Excel from Access

Step 2: Create a Dynamic Array, let's say "CustIDDynamicArray" using OFFSET for the CustID Row

Step 3: Compare "CustIDDynamicArray" to the new data in the Excel
(Need your help in this)

How do I compare "CustIDDynamicArray" to a new data or to a new set of Dynamic Array?