Searching Access database from excel-CodePudding

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

CodePudding user response：

Source = "SELECT * FROM Orders Where [Voltage]=" & var

You want to search for the value in the variable var, not the name of the variable.