Use a cell value to query an external Access DB

[knute_gis]

I use this forum all the time and it is awesome but couldn't find an answer this time.

I got the below vba macro to work but want to use a cell value as
the search value instead of typing it directly in the macro.
I tried several different things but always returned an error

I changed the text color red that would be the cell.



[FONT=&quot]Option Explicit[/FONT]
[FONT=&quot]Sub getDataFromAccess()
‘ Click on Tools, References and select
‘ the Microsoft ActiveX Data Objects 2.0 Library[/FONT]
[FONT=&quot]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[/FONT]
[FONT=&quot]Cells.Clear[/FONT]
[FONT=&quot]‘ Database path info[/FONT]
[FONT=&quot]‘ Your path will be different
DBFullName = “C:\Users\takyar\Documents\NorthWind.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[/FONT]
[FONT=&quot]‘ Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
‘ Filter Data
Source = “SELECT * FROM Customers WHERE [Job Title] = ‘Owner’ ”[/FONT]
[FONT=&quot].Open Source:=Source, ActiveConnection:=Connection[/FONT]
[FONT=&quot]‘ MsgBox “The Query:” & vbNewLine & vbNewLine & Source[/FONT]
[FONT=&quot]‘ Write field names
For Col = 0 To Recordset.Fields.Count – 1
Range(“A1”).Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

‘ Write recordset
Range(“A1”).Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
Set Connection = Nothing[/FONT]
[FONT=&quot]End Sub

Thanks for you help, Knute[/FONT]

 

[Fazza]

hi, Knute
one way maybe
regards, Fazza

Source = replace$(“SELECT * FROM Customers WHERE [Job Title] = ‘Owner’ ”, "Owner", range("B2").value2)

 

[JumboCactuar]

Hi,
You can pass variables into the query like this"

JobTitle1 = Range("A1")

Source = “SELECT * FROM Customers WHERE [Job Title] = ' &JobTitle1& ' ”

 

[knute_gis]

Thanks for the replies. I will give both of them a try and let you know if they work!

 

[knute_gis]

Hi,
You can pass variables into the query like this"

JobTitle1 = Range("A1")

Source = “SELECT * FROM Customers WHERE [Job Title] = ' &JobTitle1& ' ”

I got this one to run without an error but it does not populate the cell with access data.

 

[knute_gis]

hi, Knute
one way maybe
regards, Fazza

Source = replace$(“SELECT * FROM Customers WHERE [Job Title] = ‘Owner’ ”, "Owner", range("B2").value2)

I figured this one out and it worked!!!

Thank you