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.