What's the reports query / what query in access speak are you trying to archive in the SP?
SP's are specific to the database they are created in.
You can get complicated with "joining" databases together but that certainly isn't beginner stuff.
An SP can take a number of parameters and either perform an action or return a result set.
The normal route is to use a pass though query to run the SP return a result to access.
I use a generic function for this - first create a pass through query - I've called it qPT_Generic.
This sub sets up the query and executes it.
Code:
Public Sub sSendToPT_Generic(strQuery As String, bReturnRecs As Boolean)
Dim db As Database
Dim qDEF As QueryDef
Set db = CurrentDb()
Set qDEF = db.QueryDefs("qPT_Generic")
qDEF.Connect = db.TableDefs("tb_A_TableThatIsConnectedToYourBE_Database").Connect
qDEF.SQL = strQuery
qDEF.ReturnsRecords = bReturnRecs
If Not bReturnRecs Then
db.Execute "qPT_Generic", dbFailOnError
End If
Set qDEF = Nothing
Set db = Nothing
End Sub
Now to use it you simply pass the exec statement to it;
So to delete a tables contents quickly;
Code:
strSQL = "TRUNCATE TABLE tt_MyTemporaryTable"
sSendToPT_Generic strSQL, False
To turn your qPT_Generic into the recordsource for something;
This runs an SP called sp_Report_DailyComparison and uses a date parameter
Code:
strSQL = "EXEC sp_Report_DailyComparison @dStartDate = '" & Format(dtRunDate, "yyyy-mm-dd") & "'"
Call sSendToPT_Generic(strSQL, True)
Set rstData = db.OpenRecordset("qPT_Generic", dbOpenSnapshot)
Note the way the parameter is passed in - it's named - this is an optional thing.