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

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

"Dave" <(E-Mail Removed)> escribió en el mensaje
news:ubyyn9$(E-Mail Removed)...
> 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
>
>

Ah, yes. Very good.

Thank you Juan.


"Juan M Afán de Ribera" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Dave" <(E-Mail Removed)> escribió en el mensaje
> news:ubyyn9$(E-Mail Removed)...
> > 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
> >
> >
>
>