I have the following code, I'm trying to:
1) connect to a database
2) Create a command object so that I can then use the command object for
querying.

My code is below, I keep getting an Object Variable or With block not set
for this line: Set theCommand.ActiveConnection = theConnection.

Dim theConnection As ADODB.Connection
Dim connectionString As String
Sub ConnectToDatabase()
'Dim theConnection As ADODB.Connection
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\Mydb.mdb"

Set theConnection = CreateObject("ADODB.Connection")
theConnection.Open (connectionString)
Call GetRevenueFigure
End Sub

Sub GetRevenueFigure()
Dim sqlStr As String
Dim theCommand As ADODB.Command
sqlStr = "SELECT * FROM Revenue;"

Set theCommand.ActiveConnection = theConnection
theCommandText = sqlStr

End Sub


This is the first time I've used ADO so bear with me if I'm way off the mark.
Thanks.

you need

SET theCommand = NEW Command



"Simon" wrote:

> I have the following code, I'm trying to:
> 1) connect to a database
> 2) Create a command object so that I can then use the command object for
> querying.
>
> My code is below, I keep getting an Object Variable or With block not set
> for this line: Set theCommand.ActiveConnection = theConnection.
>
> Dim theConnection As ADODB.Connection
> Dim connectionString As String
> Sub ConnectToDatabase()
> 'Dim theConnection As ADODB.Connection
> connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source= C:\Mydb.mdb"
>
> Set theConnection = CreateObject("ADODB.Connection")
> theConnection.Open (connectionString)
> Call GetRevenueFigure
> End Sub
>
> Sub GetRevenueFigure()
> Dim sqlStr As String
> Dim theCommand As ADODB.Command
> sqlStr = "SELECT * FROM Revenue;"
>
> Set theCommand.ActiveConnection = theConnection
> theCommandText = sqlStr
>
> End Sub
>
>
> This is the first time I've used ADO so bear with me if I'm way off the mark.
> Thanks.
>