ADO error: "closed or invalid connection object"

[Dave]

Can anyone give me a clue as to why the following code is failing at the
"cmd.execute" line with a message "closed or invalid connection object:"


Dim objConn As ADODB.Connection
Dim cmd As New ADODB.Command

Dim sql As String

Set objConn = CurrentProject.Connection
sql = "SELECT * FROM test WHERE testid=1"

MsgBox (sql)

cmd.CommandText = sql
cmd.CommandType = adCmdText
cmd.Execute

Set cmd = Nothing
Set objConn = Nothing

 

[Juan M Afán de Ribera]

Hi,

you need to connect the command object to the data. So, you better do it
this other way

....

MsgBox (sql)

cmd.ActiveConnection = objConn
cmd.CommandText = sql
cmd.CommandType = adCmdText
cmd.Execute

....

HTH

 

[Dave]

Ah, yes. Very good.

Thank you Juan.

Hi,

you need to connect the command object to the data. So, you better do it
this other way

...

MsgBox (sql)

cmd.ActiveConnection = objConn
cmd.CommandText = sql
cmd.CommandType = adCmdText
cmd.Execute

...

HTH

--
Saludos
Juan M Afan de Ribera [MVP Access]
http://www.mvp-access.com/juanmafan

Can anyone give me a clue as to why the following code is failing at the
"cmd.execute" line with a message "closed or invalid connection object:"


Dim objConn As ADODB.Connection
Dim cmd As New ADODB.Command

Dim sql As String

Set objConn = CurrentProject.Connection
sql = "SELECT * FROM test WHERE testid=1"

MsgBox (sql)

cmd.CommandText = sql
cmd.CommandType = adCmdText
cmd.Execute

Set cmd = Nothing
Set objConn = Nothing