I am using Office XP and am constantly crashing Excel. I have written VBA
code to use ADO to retrieve data from an Access database. The first time I
run the program, everything works fine. However, the second time I run it,
Excel crashes and asks to report the problem to MS. I can always restart
Excel, run the program with a new SQL statement, and get my answer. But
never twice in a row. Any ideas why ADO cannot run twice? (I am using ADO
2.7, if that helps.)

Thanks

How large is the result set

--
crispb
-----------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php...fo&userid=1088
View this thread: http://www.excelforum.com/showthread.php?threadid=31457

Hi George:

Post your code, it's usually something overlooked in declaring,
or setting variables or closing the connection or setting the recordset to
nothing, impossible to tell without the code.

It could be size as Crispbd said but that would a big a// recordset
(not result set that is returned by RDO).
If you have a problem someone else has had the same one

Good Luck
TK



Post your code, it's usually something overlooked in declaring,
or setting varibals or cloaing the connection or seting the recordset to
nothing, impossble to tell without the code.

Good Luck
TK



I am using Office XP and am constantly crashing Excel. I have written VBA
code to use ADO to retrieve data from an Access database. The first time I
run the program, everything works fine. However, the second time I run it,
Excel crashes and asks to report the problem to MS. I can always restart
Excel, run the program with a new SQL statement, and get my answer. But
never twice in a row. Any ideas why ADO cannot run twice? (I am using ADO
2.7, if that helps.)

"TK" wrote ...

I am using Office XP and am constantly crashing Excel. I have written VBA
code to use ADO to retrieve data from an Access database. The first time I
run the program, everything works fine. However, the second time I run it,
Excel crashes and asks to report the problem to MS.

it's usually something overlooked in declaring,
or setting variables or closing the connection or setting the recordset to
nothing

Are you referring to ADO object variables and connections to Jet (MS
Access)? I've never being able to recreate anything nasty in testing.
I find that even letting open active connections/recordsets go out of
scope results in the Jet connections being closed cleanly (using Stop
is another matter <g). Do you have some code to reproduce?

Most of my Excel ADO crashes are because I'm inadvertently querying an
open workbook, a known bug that causes memory leakage. Perhaps the OP
is using SELECT..INTO or INSERT INTO..SELECT to target an open
workbook.

Jamie.

--

Below is the VBA code I am using. One other important point: the recordset
[George] is actually a pass-through query that retrieves data from a backend
DB2 database. The first time, it works like a charm; it prompts me for a
userid and password, and retrieves the data into Excel. The second time,
Excel dies.

Sub ImportDB()
' Imports data from Access database through ADO

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim strDB As String
Dim fldCount As Integer
Dim iCol As Integer

' Set the string to the name of the database
strDB = "M:\George.mdb"

' Open connection to the database
Set cnt = New ADODB.Connection
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDB & ";"

' Open recordset based on Orders table
Set rst = New ADODB.Recordset
rst.Open "SELECT QTR, SUM(PREMIUM) From [George] GROUP BY QTR", cnt

' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
ActiveSheet.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

' Copy the recordset to the worksheet, starting in cell A2
ActiveSheet.Cells(2, 1).CopyFromRecordset rst

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

Hi George:

Sorry Im not much help here but I tested your code by connecting to
Access "as you can see by the revised code" I could not fail the code
retrieving a rs into excel. That should narrow the issue to your select
statement or login.
One thing in your procedu If you use the New (keyword) you do not
then need to use the Set (keyword ) because the variable has been initialize.
I think the old line is still to Dim then Set, of course it made no difference
in testing your procedure.

'op test 11/18/04

Private Sub CommandButton16_Click()

' Sub ImportDB()
' Imports data from Access database through ADO

'Dim cnt As New ADODB.Connection
Dim cnt As ADODB.Connection

'Dim rst As New ADODB.Recordset
Dim rst As ADODB.Recordset

Dim strDB As String
Dim fldCount As Integer
Dim iCol As Integer

' Set the string to the name of the database
strDB = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")
'''"M:\George.mdb"

' Open connection to the database
Set cnt = New ADODB.Connection
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDB & ";"

' Open recordset based on Orders table
Set rst = New ADODB.Recordset
rst.Open "select * from products", cnt '//' "SELECT QTR, SUM(PREMIUM) From
[George] GROUP BY QTR", cnt
'Rs.Open strSql, cnn, adOpenStatic, adLockBatchOptimistic

' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
ActiveSheet.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

' Copy the recordset to the worksheet, starting in cell A2
ActiveSheet.Cells(1, 1).CopyFromRecordset rst

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

Good Luck
TK

George wrote ...

important point: the recordset
[George] is actually a pass-through query that retrieves data from a backend
DB2 database

This is the second thread in recent weeks with the same problem i.e. a
connection to a linked table 'pass-through' works the first time but
subsequently fails:

http://groups.google.com/groups?thre...ing.google.com

Have you tried missing out the .mdb and querying the database direct?

Jamie.

--

I have tried querying the database directly from Excel, but with no success.
I keep getting error "3706 - provider cannot be found". Besides, I already
am doing other queries in Access, so the data is there.

Thanks for the link to the other newsgroup thread. It looks like another
problem for Microsoft to fix. I have sent them an error report a couple of
times that Excel crashed. Maybe it's something .NET will address and fix.

Thanks

Hi George:

You might want to review:

Access Help "Pass Through Queries"

"Caution If you convert a pass-through query to another type of
query, such as a select query, you'll lose the SQL statement that
you entered."

Also:

http://support.microsoft.com/default...b;en-us;209116

Good Luck
TK

"George" wrote:

I have tried querying the database directly from Excel, but with no success.
I keep getting error "3706 - provider cannot be found". Besides, I already
am doing other queries in Access, so the data is there.

Thanks for the link to the other newsgroup thread. It looks like another
problem for Microsoft to fix. I have sent them an error report a couple of
times that Excel crashed. Maybe it's something .NET will address and fix.

Thanks