ADP equivalent for CurrentDB

[Peter Wone]

I realise that CurrentDb returns Nothing because there is no current Jet
database in an ADP. So how does one get a reference to the default database
connection? In bound forms I've been using Me.Recordset.ActiveConnection but
in an unbound form this doesn't work for fairly obvious reasons.

 

[Steve Jorgensen]

I realise that CurrentDb returns Nothing because there is no current Jet
database in an ADP. So how does one get a reference to the default database
connection? In bound forms I've been using Me.Recordset.ActiveConnection but
in an unbound form this doesn't work for fairly obvious reasons.

As I recall, it's CurrentProject.Connection

 

[steel]

I use this template for recordsets, works well;

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strMsg As String

'SQL_Connection
On Error GoTo <function_name>_Error

conn.Open CurrentProject.Connection
Set cmd.ActiveConnection = conn

strSQL = <insert SQL TExt>

cmd.CommandText = strSQL
cmd.CommandType = adCmdText
Set rs = cmd.Execute

If rs.State = adStateClosed Then
'no connection made
Goto <function_name>_error
else
<Connection OK - carry out function>
End if

<function_name>_Exit:
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
Exit Sub

<function_name>_Error:
strMsg = "ERROR: " & Err.Description
If MsgBox(strMsg, vbCritical, "Error:") = vbOK Then
End If
Resume <function_name>_Exit