Re: Excel ADO Crash

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: George (George_at_discussions.microsoft.com)
Date: 11/18/04 

Date: Thu, 18 Nov 2004 05:56:01 -0800

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

Relevant Pages

  • Re: coding help - please
    ... the DAO recordset is empty. ... DoCmd.SendObject acSendReport, "All Emp Time", acFormatSNP, ... Dim rst As DAO.Recordset ... Set rst = CurrentDb.OpenRecordset("All ...
    (microsoft.public.access.modulesdaovba)
  • Re: Exporting data from an append table into excel without field h
    ... this is the most simple code to export to excel using copy from recordset. ... Dim objXLApp As Object 'Excel.Application ... Set rst = CurrentDb.OpenRecordset ... Set objXLWs = objXLWb.Worksheets ...
    (microsoft.public.access.externaldata)
  • Re: HELP PLEASE: Populate certain userform fields from access data
    ... Here is my updated sqlStr and recordset code: ... Dim cnt As Object, rst As Object, strSQL As String ... Set cnt = CreateObject ... You query din't have a "Loan Table" parameter so I didn't ...
    (microsoft.public.excel.programming)
  • Re: A command button (Access) that saves the current record in a file
    ... 'In the onclick event of command button ... Dim fso As New FileSystemObject ... Set rst = Me.frmTextFileDemoSub.Form.RecordsetClone ... 'set recordset object rst to current record ...
    (microsoft.public.access.externaldata)
  • Re: Booking System - Vacant room report
    ... in what format? ... within the date range as a field in the recordset, ... Dim colDates As Collection ... Set rst = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)