Ms Access - Execute a Saved Query by Name in Vba

MS Access - execute a saved query by name in VBA

You can do it the following way:

DoCmd.OpenQuery "yourQueryName", acViewNormal, acEdit

OR

CurrentDb.OpenRecordset("yourQueryName")

Execute Query from Access via Excel Query in VBA

I think you can treat it like a stored procedure.

If we start right before Dim sqlQuery As String

 Dim cmd as new ADODB.Command
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "myQuery"
 cmd.ActiveConnection = con

 Set rs = cmd.Execute()

Then pickup your recordset work after this.

How to execute a query in ms-access in VBA code?

Take a look at this tutorial for how to use SQL inside VBA:

http://www.ehow.com/how_7148832_access-vba-query-results.html

For a query that won't return results, use (reference here):

DoCmd.RunSQL

For one that will, use (reference here):

Dim dBase As Database
dBase.OpenRecordset

How can I refer to Saved Queries in Access VBA?

Use an object or two:

Dim SavedQry As DAO.QueryDef
Dim rs As DAO.Recordset

Set SavedQry = CurrentDb.QueryDefs("qryCurrentItems")
SavedQry.Parameter("ParameterName1").Value = SomeValue
SavedQry.Parameter("ParameterName2").Value = AnotherValue
Set rs = SavedQry.OpenRecordset()
' Investigate records.

Querying Access From Excel. Expected Query Name After Execute

If all you are looking for is a single related value and don't really need a full set of data consider:

Dim acc As Access.Application, varData As Variant
Set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase ("D:\Users\*****\Documents\Database2.accdb")
'code here to start worksheet loop
varData = acc.DLookup("qx", "Table1", "ID=" & need worksheet cell reference here)
Debug.Print IIf(IsNull(varData), "Not found", varData)
'end worksheet loop here

Or maybe better would be to hit the Access file only once to pull all data into a recordset then use recordset Find method.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Users\*****\Documents\Database2.accdb"
rs.Open "SELECT qx FROM Table1", cn, adOpenDynamic, adLockPessimistic
'code here to start worksheet loop
rs.MoveFirst
rs.Find "ID=" & need worksheet cell reference here
Debug.Print IIf(rs.EOF, "Not found", rs!qx)
'end worksheet loop here

Second approach may actually be faster procedure.

---

Related Topics