Open Access query from Excel using parameter

[Dan]

I need to either open an access query from Excel using a cell as criteria or
run sql using a cell as criteria to display the matching data. I don't want
to import the data to excel. I have a sql table called ledger with a file
called ledger_obj. I need to display all of the data where cell A1 =
ledger_obj. Thank you.

 

[Joel]

You can use all the Access macro instruction from Excel VBA. Just get the
object

set obj = Getobject("ledger_obj.mdb")

then in Excel VBA add the reference from menu tools - References

Microsoft Access 11.0 Library object (or latest on your PC)

if you are using ADO methods
Microsoft ActiveX Data Object 2.8 library (or latest on your PC)

Or DAO method
Microsoft DAO 3.5 object Library (or Latest)

Use the Access VBA help to get help for particular method you are using.

Sub SQLX()

Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim rstEmployees As Recordset

Set dbsNorthwind = GetObject("Northwind.mdb")
Set qdfTemp = dbsNorthwind.CreateQueryDef("")

' Open Recordset using temporary QueryDef object and
' print report.
Call SQLOutput("SELECT * FROM Employees " & _
"WHERE Country = 'USA' " & _
"ORDER BY LastName", qdfTemp)

' Open Recordset using temporary QueryDef object and
' print report.
Call SQLOutput("SELECT * FROM Employees " & _
"WHERE Country = 'UK' " & _
"ORDER BY LastName", qdfTemp)

dbsNorthwind.Close

End Sub

Function SQLOutput(strSQL As String, qdfTemp As QueryDef)

Dim rstEmployees As Recordset

' Set SQL property of temporary QueryDef object and open
' a Recordset.
qdfTemp.Sql = strSQL
Set rstEmployees = qdfTemp.OpenRecordset

Debug.Print strSQL

With rstEmployees
' Enumerate Recordset.
Do While Not .EOF
Debug.Print " " & !FirstName & " " & _
!LastName & ", " & !Country
.MoveNext
Loop
.Close
End With

End Function

 

[Dan]

Thank you. I can get the mdb and query to open but I'm not sure about how to
use a cell as criteria.

 

[Joel]

With rstEmployees
' Enumerate Recordset.
RowCount = 1
Do While Not .EOF
LastName = .Lastname
FirstName = .FirstName
Country = .Country
with thisworkbook.sheets("Sheet1")
.Range("A" & RowCount) = LastName
.Range("B" & RowCount) = FirstName
.Range("C" & RowCount) = Country
end if
RowCount = rowCount + 1
.MoveNext
Loop
.Close
End With