By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Duplicate values in Ms Excel while uploading to Ms Access

P: 31
I am uploading MS Excel data to Ms access as detailed below.

Ms. Excel

A(Column) B(Column) C(Column)
------------------------------------------------------------------------------------
1 | Apple
2 | Mango
3 | Banana

Ms Access

Name_of_Fruits
-----------------------
Apple
Grapes

While uploading, I want a message box that Column A and Row 1 ('Apple') in Excel already Exist in Access Database.

Kindly help me with an Example code or Deatiled code for the same. Iam a beginer in Ms. Access.

Regards,
Ananth
Sep 17 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Ananth.

How do you perform Excel to Access data import?

Regards,
Fish
Sep 17 '08 #2

P: 31
Hello, Ananth.

How do you perform Excel to Access data import?

Regards,
Fish
I am using the below code to upload an excel file to Access. Kindly help me where should i define the code for Duplication.

Private Sub Bulk_Upload()

On Error GoTo Err_myError

Dim strmsg As String
Dim mypath As String
Dim rst As DAO.Recordset
Dim DB As Database
Dim r As Long
Dim wkbk As Workbook
Dim strDupName As String, strSaveName As String
Dim objRange1 As Range
Dim objRange2 As Range
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
Dim stDocName As String
Dim strSQL As String

DoCmd.SetWarnings False

Set DB = CurrentDb()

If Len(Nz(Me.Location, "")) = 0 Then
Me.Status.Value = "Select the Excel file to upload...........!"
Me.Location.SetFocus
Exit Sub
End If

Set wkbk = Workbooks.Open(Me.Location, True)

If Range("A1").Value <> "StrInward_No" _
Or Range("B1").Value <> "Inward_Date" _
Or Range("C1").Value <> "Hospital_Name" _
Or Range("D1").Value <> "Date_of_Admin" _
Or Range("E1").Value <> "Date_of_Discharge" _
Or Range("F1").Value <> "Claim_Amt" _
Or Range("G1").Value <> "Policy_No" _
Or Range("H1").Value <> "Policy_Name" _
Or Range("I1").Value <> "UHID_Fast_Track" _
Or Range("J1").Value <> "Name_of_Patient" _
Or Range("K1").Value <> "Claim_Rec_Date" _
Or Range("L1").Value <> "Claim_Type" _
Or Range("M1").Value <> "Claim_Status" _
Or Range("N1").Value <> "Claim_No" _
Or Range("O1").Value <> "Reason_Description" _
Or Range("P1").Value <> "Final_Query_Status" _
Or Range("Q1").Value <> "Actual_Query_Date" _
Then

ActiveWorkbook.Close
Me.Status.Value = "The process cannot access the file" & " " & Me.Location & " " & "...Column Numbers does not match the required definition"
Me.Location = ""

Exit Sub
End If

Set rst = DB.OpenRecordset("Claim_Status", dbOpenDynaset)

r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0 ' repeat until first empty cell in column A
'
With rst
.AddNew ' create a new record

.Fields("StrInward_No") = Range("A" & r).Value ' add values to each field in the record
.Fields("Inward_Date") = Range("B" & r).Value
.Fields("Hospital_Name") = Range("C" & r).Value
.Fields("Date_of_Admin") = Range("D" & r).Value
.Fields("Date_of_Discharge") = Range("E" & r).Value
.Fields("Claim_Amt") = Range("F" & r).Value
.Fields("Policy_No") = Range("G" & r).Value
.Fields("Policy_Name") = Range("H" & r).Value
.Fields("UHID_Fast_Track") = Range("I" & r).Value
.Fields("Name_of_Patient") = Range("J" & r).Value
.Fields("Claim_Rec_Date") = Range("K" & r).Value
.Fields("Claim_Type") = Range("L" & r).Value
.Fields("Claim_Status") = Range("M" & r).Value
.Fields("Claim_No") = Range("N" & r).Value
.Fields("Reason_Description") = Range("O" & r).Value
.Fields("Final_Query_Status") = Range("P" & r).Value
.Fields("Actual_Query_Date") = Range("Q" & r).Value
.Update ' stores the new record
End With

r = r + 1 ' next row
Loop

rst.Close

Set rst = Nothing
Set DB = Nothing

Excel.Application.DisplayAlerts = False
ActiveWorkbook.Close


DoCmd.SetWarnings True


Exit_myError:
Exit Sub
Err_myError:

MsgBox Err.Description

Resume Exit_myError

End Sub
Sep 18 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Do you want to restrict duplicates absolutely or do you want to warn about duplicate value and allow/reject record according to user response?

Also post please what field(s) / combination(s) of fields is(are) expected to be unique.

Regards,
Fish
Sep 18 '08 #4

P: 31
Do you want to restrict duplicates absolutely or do you want to warn about duplicate value and allow/reject record according to user response?

Also post please what field(s) / combination(s) of fields is(are) expected to be unique.

Regards,
Fish
Dear Fish,

I want to restrict duplicates while uploading the excel data and to inform the user that the perticular cell in excel contains duplicate value which is already exist in access and the user cannot upload the data to access with duplicate values. Kindly remove the duplicate fields and re-upload.

The unique no is StrInward_No where the duplicate has to be restricted.

The output of Duplicate field should be shown in Me.Status.Value = "StrInward_No.12345 in Excel Row A5 is already exist in Ms Access StrInward_No Field. Kindly remove the duplicate field and re-upload"

Regards,
Ananth
Sep 19 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Hello, Ananth.

Let me outline the whole situation as I see it.
  • With the code you already have you can:
    • Check whether a value going to be imported already exists in the table. DCount() or DLookUp() function will help you here. I guess it will reduce code performance significantly, but it is by its very nature not that fast - I suppose we are talking of dozens or hundreds (in a worst case) records to be imported.
    • You should make a unique index on [StrInward_No] field or declare it as PK, or maybe you've already done this. If so, then trying to add record with duplicate [StrInward_No] value will generate error which could be trapped by
      Expand|Select|Wrap|Line Numbers
      1. On Error Goto ...
      2.  
      statement. When trapped, user will be warned and the record will be cancelled.
      This is expected to perform faster than the previous one.
  • As I've already said the whole code is not a best performing one. It will be better to link Excel sheet dynamically as table and perform import with append query. As long as [StrInward_No] field is indexed as unique irrelevant records will be omitted automatically.

Regards,
Fish
Sep 19 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.