Automation error..maybe missing parameter?

kalebson · March 2nd, 2006, 02:59 PM

I posted this in the Excel forum as well but since it relates to Access thought you might also have a look. TIA

Could you all look for a problem in this ADO? TIA

Public Sub PlainTextQuery()

    Dim rsData As ADODB.Recordset
    Dim szConnect As String
    Dim szSQL As String

    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                "Data Source=C:\Test\Calls.mdb; "

    szSQL = "SELECT Date, Skillset, Calls " & _
            "FROM Calls " & _
            "ORDER BY Date"

    Set rsData = New ADODB.Recordset
    rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

    If Not rsData.EOF Then
        Sheet1.Range("A2").CopyFromRecordset rsData
        rsData.Close
        With Sheet1.Range("A1:C1")
            .Value = Array("Date", "Skillset", "Calls")
            .Font.Bold = True
        End With
        Sheet1.UsedRange.EntireColumn.AutoFit
    Else
        MsgBox "Error: No Records Returned.", vbCritical
    End If

    If CBool(rsData.State And adStateOpen) Then rsData.Close
    Set rsData = Nothing

End Sub

Gives Run-time error '-2147217904(80040e10)

This is almost identical to one in the VBA books. Thanks.

Bob Bedell · March 2nd, 2006, 11:09 PM

Hi kalebson,

On the face of it, there is nothing wrong with the code you have. It ran fine in my VBA project (Access XP SP3). I just added the Automation objects:

Code:

Dim xlApp As Object
Dim xlWb As Object
Dim Sheet1 As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set Sheet1 = xlWb.Worksheets("Sheet1")

xlApp.Visible = True

Which line is your code breaking on? Are you using Office 2K3? Seems to be some problems using ADO with Excel 2K3 as I was discussing with nebbish last night at this link.

http://p2p.wrox.com/topic.asp?TOPIC_ID=40811

Might want to have a peak at the registry settings we were talking about. At any rate, your code worked for me.

HTH,

Bob

kalebson · March 3rd, 2006, 09:37 AM

Thats EXACTLY what it was Bob....per you post on 2k3..Thank you very much.

Bob Bedell · March 3rd, 2006, 11:20 AM

That's interesting kalebson. The problem apparently throws a variety of erros (at least three) and is becoming fairly well documented, so I checked for any KB articles/hot fixes/service packs. You might want to have a look at:

http://support.microsoft.com/kb/904953/

This version of the problem becomes a known issue after the installation of Office 2003 Service Pack 2.

Bob

kalebson · March 3rd, 2006, 11:48 AM

Thanks again...I forwarded this info tou TS team...Ill let them do the updates here. Only 1 issue left on my other script and I think im good for the weekend. 5 PCs all the same HW/SW and I get an error and they dont on the same script....FUN one. Thanks again for your help.