ADO error -- Object variable or With block variable not set

[rlb_wpg]

Although I've used the adodc object before with no problems, I now receive a "Object variable or With block variable not set" error.

My References:
Microsoft ADO Ext 2.5 for DLL & security
Microsoft Data Binding Collection
Microsoft Data Environment Instance 1.0
Microsoft Jet and Replication Object 2.5 Library
(I will be accessing both Access & SQL 7 databases)

My Components:
Microsoft ADO Data Control 6.0

My ADO properties:
adoReports.CommandTimeout = 60
adoReports.CommandType = adCmdText
adoReports.ConnectionString = gconSwitch
adoReports.ConnectionTimeout = 60
adoReports.CursorLocation = adUseServer
adoReports.CursorType = adOpenStatic
adoReports.LockType = adLockReadOnly
adoReports.MaxRecords = 0
adoReports.Mode = adModeShareDenyNone
adoReports.RecordSource = "Select * from Clients"

gconSwitch = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Switch;Data Source=Server1"



I have tried changing properties and comparing properties with working code from other projects, to no avail.

I am beyond frustrated. I hope someone can help,
rlb_wpg

[jmcswain]

You need to reference ADO itself, not just the extensibility library.

[rlb_wpg]

In the "Available References" list, what is the reference name?

[Fried Egg]

Microsoft ActiveX Data Objects 2.x

[rlb_wpg]

The library was included I missed typing in the list.
Thanks for replying.
Any other suggestions?

[Fried Egg]

When do you get the aforementioned error?

[rlb_wpg]

The first clue an error occurred was when the form
loaded the object was inaccessible, even though the
it was enabled.

To find the error, I placed the following code in the
Form_Load subroutine:
adoReports.Recordset.MoveFirst

[simonm]

The connection is failing (perhaps due to an invalid connection string) so therefore the data control is unable to instantiate it's recordset and hence the error you encountered.

Just to test it, in your Form_Load, instantiate and open a new connection object using the data control's connection string and see if that works. If it does, take the SQL string and open a recordset with it and see where you get to.

If the above works, you could try refreshing the data control when you open the form.

[rlb_wpg]

Thanks for the reply.

Tested it, the connection string is fine.
Tested the sql statement also, it's fine.

It works fine when I create my own connection, command and recordset objects.
(dim cntTest as adodb.connection ... etc.)

Actually, that's what I've had to do considering I couldn't get the Adodc object to work.

I just would have preferred using the adodc object. Plus, it should work. I just don't get it.

[jestes]

dim rst as new adodb.recordset

[simonm]

I never used data bound controls anymore (the last time was with DAO) so I don't know what sort of problems could occur.

[MagellanTX]

Souds like you forgot to instantiate the object...

For example, if your code looks something like this...

DIM rsRecords as RECORDSET

then you need to add the line:

SET rsRecords = NEW RECORDSET

This should fix the error you are getting.

Good luck!

[jestes]

what does your actual code look like?

dim strConn as string, Conn as New adodb.connection
dim Rst as New adodb.recordset, strSQL as string
dim command as adodb.command

strConn = ado.ConnectionString 'ado = ado control
If conn.State = True Then
conn.Close
conn.Open strconn
Else
conn.Open strconn
End If


strSQL = "Select * from Clients"

With rst
.ActiveConnection = conn
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic

.Open Source:=strSQL

End With


The only time I've ever gotten the error you're getting is if my recordset or connection have not been referenced as either new, or part of another object.

[jestes]

[samwise]

I tried to use it, for opening up an Excell spreadsheet, to no avail. (I can't remember the problem, but I spent a whole day on it trying to resolve it).
Finally, I gave up, threw away the ADO data control, use the Connection and Recordset objects, and had my project finished in 15 minutes.

Samwise Galenorn
[email protected]

[MagellanTX]

Very important!!

When you are done with the recordset, make sure free the resources.

SET rsRecords = NOTHING

If you do not do this, then everytime you execute the function to build the recordset it will build a new one and still hold your old ones in memory. You will quickly run out of resources.

[leekitts]

If you set up the ConnectionString and RecordSource properties correctly, the first thing you should do in your code is:

ADODC.Refresh

I think this is basically like the Open property for ADODB.

[mikehall]

I encountered the same error when I moved my program to a test machine. I suspected that the test machine was running a different mdac version than the development machine. I updated it to mdac 2.5 and it fixed the problem.

[Mahdi Jazini]

Sorry for this... I know this topic is about 12 year ago. but i have posted under this topic because:

1) this important question is on top of google search results
2) there is no any correct answer in this topic!

i have tested this:

this error can be for the following reason:

if you use a variable (ADODB.connection type) as an argument in a sub or function, after executing the ADODB open command line, you'll get this error:


Object variable or With block variable not set

to avoid this error, you need to define the ADODB.connection variables as a public variable in your program not an argument

this was my experience
i hope it can help you

[Elroy]

Hey, the moderator may close it on you, but I love it when an old thread becomes active and the thread starter re-posts, showing he/she is still out there.

[MarkT]

@Mehdi Jazini - can you give an example of what you are talking about? From what I'm reading it sounds like you think this should cause an error but I don't see it that way.

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=MARKPC"
    cn.Open
    
    Set rs = GetRecords(cn)
End Sub

Private Function GetRecords(ByVal cn As ADODB.Connection) As ADODB.Recordset
Dim rs As ADODB.Recordset

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Source = "Select * From Employees"
        .Open
    End With
    
    Set GetRecords = rs

End Function

[Mahdi Jazini]

@Mehdi Jazini - can you give an example of what you are talking about? From what I'm reading it sounds like you think this should cause an error but I don't see it that way.

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=MARKPC"
    cn.Open
    
    Set rs = GetRecords(cn)
End Sub

Private Function GetRecords(ByVal cn As ADODB.Connection) As ADODB.Recordset
Dim rs As ADODB.Recordset

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Source = "Select * From Employees"
        .Open
    End With
    
    Set GetRecords = rs

End Function

1 hour ago i have changed my code in my program. but i remember the ADODB.Connection argument was ByRef not ByVal

it seems the error is in ByRef mode

i have used ByRef because i wanted to use its argument out of the function too

i didn't know that

thank you for your info

[Navion]

Hey, the moderator may close it on you, but I love it when an old thread becomes active and the thread starter re-posts, showing he/she is still out there.

Yeah, I noticed the moderator is pretty zealous about closing old threads that are resurfacing.