rf44 (5/7/2014)

---

If you want to keep a query in Access, use a pass-through query:

Sub RefreshQuery(Byval Value As String)

' Name of the query in the Access database.

' Replace QueryName by the actual name of the query.

'

Const c_Name As String = "QueryName"

' Name of the stored procedure.

' Replace with the actual name of the SP and its parameter.

'

Const c_SQL As String = "MyProcToGetSomeData @LocID = @P;"

' Connection string to the server.

' Replace by the actual values (see: https://www.connectionstrings.com/sql-server/)

'

Const c_Connect As String = "ODBC;Driver={SQL Server};Server=ServerName;Database=DatabaseName;TrustedConnection=Yes"

Dim qdf As DAO.QueryDef

' Create the query if it does not exist already.

'

If DCount("*", "MSysObjects", "name = '" & c_Name & "'") = 0 Then

SET qdf = CurrentDb.CreateQueryDef("")

With qdf

.Connect = c_Connect

.Name = c_Name

.SQL = c_SQL

End With

End If

Set qdf = CurrentDb.QueryDefs(c_Name)

With qdf

.Connect = c_Connect ' Just in case the connection changed since the last call.

.SQL = Replace(c_SQL , "@P", Value )

End With

Set qdf = Nothing

End Sub

Have a nice day!