Need VB code to check if value from Excel exists in Access before importing vaule

[mxtreme]

******** type=text/javascript><!--google_ad_client = "pub-2906984753051413";/* 468x60, created 5/23/10 */google_ad_slot = "7175652920";google_ad_width = 468;google_ad_height = 60;//-->*********>******** type=text/javascript src="http://pagead2.googlesyndication.com/pagead/show_ads.js">*********>******** ******************** src="http://ads.informationactive.com/ad.cgi?p=ad&b=180x60-2&a=218">*********>

Need VB code to check if value from Excel exists in Access before importing vaule to Access.
I have data from Excel bing imported into Access from Excel.
Before the data is to be imported into Access I need a safeguard in place.
This would check to see if the data from the excel file(book1) in worksheet (sheet1) exists in the access file (db1) and table (table_1).
What it also needs to do is look for a line match
So what I mean is I need it to look for a row match not just to see if 1 item matches
​

 

[ChrisM]

The correct way for this type of safeguard is actually done from Access, not Excel. Setup the primary key of your table such that it won't allow these types of duplicates. It will let you run your Excel query but nothing will transfer over to Access when duplicates occur.

 

[mxtreme]

Thanks Chris
I appreciate your reply - sadly this will not work in this case. I t really does have to see if a row in excel (the 3 criteria) matches in the same combination in Access.
Thanks again

 

[ChrisM]

You have two options, use an embedded query or use an Ado query. How are you uploading the data to access?

Do you know how to setup a query from the Data menu? Once you have the query setup, it is possible to use excel cells as the query parameters. If the query returns zero records, then no match.

Post back on how you are doing this and let's go from there.

 

[mxtreme]

Thanks Chris
I am novice would you mind giving examples or more details.
I really do thank you

 

[ChrisM]

I was going to give you more detail once you answered my two questions. I'm not going to spend 10 minutes explaining how to upload data to Access using ADO queries if you are never going to use them.

So, how are you uploading the Excel data into Access?

Have you ever used the Excel query tools under menu Data\Import External Data?

 

[mxtreme]

Hi Chris I am thankful for your time and help
This is what I have so far but I need it to do not just field validation but a combo of the three



Private Sub CommandButton2_Click()
Dim db As Database, rs As DAO.Recordset, r As Long
Dim PolicyNum As Variant
Dim bFound As Boolean
Dim vBook As Variant
Dim rCell As Range
Dim otherWb As Workbook
'set your database and recordset--------------------------------------------------------------------------------------------------
10 Set db = _
OpenDatabase("C:\Documents and Settings\123.mdb")
20 Set rs = db.OpenRecordset("Info_Weekly", dbOpenTable)
'set the variable-----------------------------------------------------------------------------------------------------------------
30 bFound = False
'go to the first line of the rs---------------------------------------------------------------------------------------------------
40 rs.MoveFirst
50 Do
'Criteria-------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------------------------------------------------
60 If ThisWorkbook.Worksheets("Info_Weekly").Range("C23").Value = _
rs.Fields("Value") Then
'---------------------------------------------------------------------------------------------------------------------------------
'set the variable ----------------------------------------------------------------------------------------------------------------
70 bFound = True
80 End If
'move to the next line of the rs -------------------------------------------------------------------------------------------------
90 rs.MoveNext
'go through the rest of the file until the end of file or till item is found------------------------------------------------------
100 Loop Until rs.EOF Or bFound = True
110 rs.Close
120 Set rs = Nothing
130 db.Close
140 Set db = Nothing
'Msg boxs ------------------------------------------------------------------------------------------------------------------------
150 If bFound Then
160 MsgBox "This metric already exists in Access ", vbCritical, "F1 Canada"
170 Else
180 MsgBox "Record not found.", vbOKOnly, "F1 Canada"
190 End If
'---------------------------------------------------------------------------------------------------------------------------------
End Sub

 

[ChrisM]

Again, the best way to do this is simply to setup your primary key in Access to include the three fields in question. Then you won't need any code at all, those Excel rows will just be ignored.

But, if you can't do that, then try to replace this:

60 If ThisWorkbook.Worksheets("Info_Weekly").Range("C23").Value = _
rs.Fields("Value") Then
'---------------------------------------------------------------------------------------------------------------------------------
'set the variable ----------------------------------------------------------------------------------------------------------------
70 bFound = True
80 End If

With something like this:

Dim ExcelRecord as String
Dim AccessRecord as String

With ThisWorkbook.Worksheets("Info_Weekly")
     ExcelRecord = .Range("C23").Value & .Range("D23") & .Range("E23")
End With

AccessRecord = rs.Fields("Value") & rs.Fields("Value2") & rs.Fields("Value3")

If ExcelRecord = AccessRecord Then
   bfound = True
Else
  bfound = False
End If

 

[mxtreme]

I really must really thank you for sticking with this and helping me.
Thank you very much and I hope to repay the favor to you one day.
Thanks again

 

[mxtreme]

Hi Chris hopefully you can help me out here a bit more
The previous developer basically took the metrics from the last row in Excel and used SQL to identify what should be
- date
- metric ref #
- value
in the Access table
Is there something I can do where I can grab that newly created record created by using SQL and then say

Dim ExcelRecord as String
Dim AccessRecord as String



If ExcelRecord = NEWLY CREATED Access Record from using sql Then
bfound = True
Else
bfound = False
End If






MSQL = _
" SELECT Metrics.Metric, Reporting_H.Level_1, Metrics_X_Reporting_H.Metric_ID, Info_Weekly.Date, " _
& "Info_Weekly.Value " & _
"FROM ((Metrics_X_Reporting_H INNER JOIN Metrics ON Metrics_X_Reporting_H.Metric_Name_ID = Metrics.Metric_Name_ID) " _
& _
"INNER JOIN Reporting_H ON Metrics_X_Reporting_H.H_ID = Reporting_H.H_ID) " _
& _
"INNER JOIN Info_Weekly ON Metrics_X_Reporting_H.Metric_ID = Info_weekly.Metric_ID " _
& "WHERE (((Metrics.Metric)='" & "Force Completed On Time - Weekly" & "') " & _
"AND ((Reporting_H.Level_1)='" & "ELT" & "') " & _
"AND ((Info_weekly.Date)='" & MDate & "'));"




</pre>