VBA - "Out of stack space" error : ADO DAO RDO RDS

While this is an ADO issue, I do not get the same results in MS Access. The
following code, written for AutoCAD VBA generates an 'Out of stack space'
error. I can run the query in Access without error. The result set, as
tested is less than 4 records, with 6 fields each (not nearly enough to be
causing the problem).

First, I create the ADODB connection...

' Attempt to connect to the database
On Error GoTo CONNECT_ERROR
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Persist Security Info-False"

ADO_Connection.Open ConnectString, "", "", adConnectUnspecified

Then I attempt to open a recordset ...

' If the connection to the database has been opened
If ADO_Connection.State = adStateOpen Then
Dim SQL As String
Dim RS As New ADODB.Recordset

' Define a query to select all of the drawing sets by name
SQL = "Select * From DrawingSets;"

' Execute the query
On Error GoTo SQL_ERROR
RS.Open SQL, ADO_Connection, adOpenForwardOnly, adLockReadOnly, -1

The ADO_Connection seems to be created without error. The error occurs on
the last line, where I try to open the recordset.

Perhaps someone can shed some light on this one. The code worked fine until
yesterday. I have no idea what I might have changed to cause this error (I
added a lot of code to the module). I've posted this in the VBA
Customization discussion group on the Autodesk website as well.

Whenever you get an error such as this I find it easier to break the error
line out if I can so instead of


RS.Open SQL, ADO_Connection, adOpenForwardOnly, adLockReadOnly, -1

I would have
With RS
Set .ActiveConnection = ADO_Connection
.Source = SQL
.CursorType = adOpenForwardOnly
.Lock = adLockReadOnly
.Open
End with

and see if that casued a problem.

If the problem persists I would look at reinstalling ADO, which can be a
pain but is eased if you download the COMCheck ustility from Microsoft.

BTW there are some errors in your code.
If ADO_Connection.State = adStateOpen Then

would be better written as either
If (ADO_Connection.State AND adStateOpen) = adStateOpen Then

or
If ADO_Connection.State <> adStateClosed Then



--
Terry Kreft
MVP Microsoft Access


"John Dutz" <jdutz@NOSPAMibigroup.com> wrote in message
news:u1ioCghYFHA.2508@TK2MSFTNGP15.phx.gbl...
> While this is an ADO issue, I do not get the same results in MS Access.
The
> following code, written for AutoCAD VBA generates an 'Out of stack space'
> error. I can run the query in Access without error. The result set, as
> tested is less than 4 records, with 6 fields each (not nearly enough to be
> causing the problem).
>
> First, I create the ADODB connection...
>
> ' Attempt to connect to the database
> On Error GoTo CONNECT_ERROR
> ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & FileName & ";" & _
> "Persist Security Info-False"
>
> ADO_Connection.Open ConnectString, "", "", adConnectUnspecified
>
> Then I attempt to open a recordset ...
>
> ' If the connection to the database has been opened
> If ADO_Connection.State = adStateOpen Then
> Dim SQL As String
> Dim RS As New ADODB.Recordset
>
> ' Define a query to select all of the drawing sets by name
> SQL = "Select * From DrawingSets;"
>
> ' Execute the query
> On Error GoTo SQL_ERROR
> RS.Open SQL, ADO_Connection, adOpenForwardOnly, adLockReadOnly, -1
>
> The ADO_Connection seems to be created without error. The error occurs on
> the last line, where I try to open the recordset.
>
> Perhaps someone can shed some light on this one. The code worked fine
until
> yesterday. I have no idea what I might have changed to cause this error
(I
> added a lot of code to the module). I've posted this in the VBA
> Customization discussion group on the Autodesk website as well.
>
>