We help IT Professionals succeed at work.

Excel VBA code to check if value exists in Access table

Medium Priority
138 Views
Last Modified: 2019-10-11
Hi. What Excel VBA code would I use to test if a value of 87 is present in a column named Col1 in a table named Table1 in an Access database in the same folder as the spreadsheet
Comment
Watch Question

Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
With the same code you got in another question ...you do the connection ...load the recordset from the table...iterate the recordset ...check the value in interest.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
You could also connect to the Access database and table and link it from the Excel file.  Once that is set up then you can treat the sheet you import the linked data to just like a normal Excel sheet and look for the data value(s) you want in it.  Here's a few references if you aren't familiar with this approach.



»bp
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try the following Function...

Function ValueExistsInDatabase(ValueToCheck As Long) As Boolean
Dim wb              As Workbook
Dim wbPath          As String
Dim ConnStrAccess   As String
Dim AccessFilePath  As String
Dim Conn            As ADODB.Connection
Dim rs              As ADODB.Recordset

Application.ScreenUpdating = False

Set wb = ThisWorkbook
wbPath = wb.Path & "\"

AccessFilePath = wbPath & "YourDatabase.accdb"

ConnStrAccess = _
                "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & AccessFilePath & ";" & _
                "Persist Security Info=False;"

Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset

Conn.ConnectionString = ConnStrAccess
Conn.Open

On Error GoTo CloseConnection
With rs
   .ActiveConnection = Conn
   .Source = "SELECT COUNT(*) FROM Table1 WHERE Col1=" & ValueToCheck
   .LockType = adLockReadOnly
   .CursorType = adOpenForwardOnly
   .Open
End With
On Error GoTo CloseRS

ValueExistsInDatabase = rs.Fields(0).Value

CloseRS:
rs.Close
CloseConnection:
Conn.Close
End Function

Open in new window

And then to test whether a value exists in Col1 in Table1, you may try this...

Sub TestFunction()
Debug.Print ValueExistsInDatabase(5)
End Sub

Open in new window


This will return True if the value being checked was found in the Col1 in Table1 else it will return False.

Don't forget to change the name of your database in line#14, currently it says "YourDatabase.accdb"
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!