I am trying to make a code that will search an Acess file for a specific value in a column and return that row. So far I have it working just not while using a variable instead of a specific value.
first I set the value I am searching for to the variable var that is cell A1 then I am trying to search for it in the "Voltage" column in my Access file and when I use var I get the error " No value given for one or more required parameters."
Option Explicit
Sub getDataFromAccess()
'Cell search value
Dim var As Integer
var = Range("A1").value
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
'Cells.Clear
'Database path info
DBFullName = "C:\Users\jmike\Desktop\excel
database - Copy\Database.accdb"
'Open the connection
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect
'Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
'Filter Data
Source = "SELECT * FROM Orders Where[Voltage]=var"
.Open Source:=Source, ActiveConnection:=Connection
'Write field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A2").Offset(0, Col).value = Recordset.Fields(Col).Name
Next
'Write recordset
Range("A2").Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
Source = "SELECT * FROM Orders Where [Voltage]=" & var
You want to search for the value in the variable var, not the name of the variable.