The definitions for tables are stored in a system table called MSysObjects, the connection string is in field Connect. You can access this table to get the connection string as and when you want to run the sproc (you'll need to reference a table you know is in the same database, I have front ends linked to multiple databases), though as the connection string does not change you may be better to set it to a global variable or just hard code it in (which i have fallen into the habit of doing).
NOTE: This is as per MS Access 2007, it is the only one I have installed
Below is an example of a function I use to execute a sproc, the sproc returns a value to confirm it has completed successfully, which is returned in @Ret. Hope this helps.
Function LogImportFile(strFile As String) As Long
On Error GoTo Err_Handle_LogImportFile
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "DRIVER={SQL Server};SERVER=[Server];DATABASE= _
[DatabaseName];Trusted_Connection=Yes"
' The above is for linking to a SQL Server table using a DSN less connection
' which I would highly recommend (DSN less) if you plan to distribute your
' database
cnn.Open cnn.ConnectionString
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "cmsUser.usp_LogImportFile"
cmd.CommandTimeout = 0
Set param = cmd.CreateParameter("@FileName", adVarChar, adParamInput, _
200, strFile)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@Ret", adInteger, adParamOutput)
cmd.Parameters.Append param
cmd.Execute
LogImportFile = cmd.Parameters("@Ret")
Exit_Proc:
Set cnn = Nothing
Set cmd = Nothing
Set param = Nothing
Exit Function
Err_Handle_LogImportFile:
Msgbox "LogImportFile - " & Err.Number & " - " & Err.Description
LogImportFile = -1
GoTo Exit_Proc
End Function
