Error -2147417851 '"The server threw an exception"

[Bob Barnes]

I posted this last nite in Access.formscoding. A MVP has answered w/ a question & I jus
responded to that

The question..

I am using Office 2000 (Access to Excel) Automation which I have use
quite a bit

However, today, in using the "CopyFromRecordset" Excel method
I am receiving the "Subject" error above. There was no one else in th
Back-end to Lock the Query

I use a String Argument as part of this code. It fails at "CopyFromRecordset"..

Public Sub CopyTheData(strSql As String, strWorkBook As String,
Optional strWorkSheet As String, Optional strCellRef As String
On Error GoTo ProcErro
DoCmd.Hourglass True: bLock = Fals
Dim objXLApp As Object 'Excel.Applicatio
Dim objXLWb As Object 'Excel.Workboo
Dim objXLSheet As Object 'Excel.Workshee
Dim RS As DAO.Recordset, RT As DAO.Recordse
Dim fld As DAO.Field, I%, iSheets
'set rs from sql, table or quer
Set RS = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot
'start Exce
Set objXLApp = CreateObject("Excel.Application"
'open workbook, error routine will create it if doesn't exis
'only create workbooks with 1 shee
iSheets = objXLApp.SheetsInNewWorkbook 'save user's settin
objXLApp.SheetsInNewWorkbook = 1 'set for only 1 shee
Set objXLWb = objXLApp.Workbooks.Open(strWorkBook
objXLApp.SheetsInNewWorkbook = iSheets 'restore user's settin
'select a worksheet, if sheet doesn't exis
'the error routine will add i
If strWorkSheet = "" The
strWorkSheet = "Sheet1
End I
'If Range is missing default to A
If strCellRef = "" Then strCellRef = "A1
'select desired workshee
Set objXLSheet = objXLWb.Worksheets("TheChart"
objXLSheet.Range("ATitle").Clea
objXLSheet.Range("ATitle") = Forms!frmMain!cboMgmt
& " For W/E (Saturday) " & Forms!frmMain!cboChartDat
objXLSheet.Range("Person").Clea
objXLSheet.Range("Person") = Forms!frmMain!cboMgmt.Column(1
'=
Set objXLSheet = objXLWb.Worksheets(strWorkSheet
objXLSheet.Range(strCellRef).Clear 'Is "TheData
objXLSheet.Range(strCellRef).CopyFromRecordset R
Set objXLSheet = objXLWb.Worksheets("TheChart"
'Save w
Outa
objXLWb.Save: objXLWb.Clos
'close up other rs object
If Not RS Is Nothing Then RS.Clos
Set RS = Nothin
Set objXLSheet = Nothin
Set objXLWb = Nothin
'quit Exce
If Not objXLApp Is Nothing Then objXLApp.Qui
Set objXLApp = Nothin
Exit Su
ProcError
Select Case Er
Case -2147417851 ' --> "The server threw an exception
'Answer what ???
Case 9 'Worksheet doesn't exis
objXLWb.Worksheets.Ad
Set objXLSheet = objXLWb.ActiveShee
objXLSheet.Name = strWorkShee
Resume Nex
Case 1004 'Workbook doesn't exist, make i
objXLApp.Workbooks.Ad
Set objXLWb = objXLApp.ActiveWorkboo
objXLWb.SaveAs strWorkBoo
Resume Nex
Case Els
DoCmd.Hourglass Fals
MsgBox Err.Number & " " & Err.DESCRIPTIO
Sto
Resume
End Selec
End Su

TIA - Bob

 

[Bob Barnes]

Is there a good alternative to "CopyFromRecordset"

When I need a single value, I use an Access Recordset
& write that value (no problems), but this is a Recordse
of up to 52 records with 2 fields per Record

Parts of my code..
Set RS = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot) <== No problem w/ the "strSQL
'start Exce
Set objXLApp = CreateObject("Excel.Application"
...................
'select desired workshee
Set objXLSheet = objXLWb.Worksheets(strWorkSheet) <== No problem w/ the "strWorkSheet
objXLSheet.Range(strCellRef).Clear 'Is "TheData" <== No problem w/ the "strCellRef
objXLSheet.Range(strCellRef).CopyFromRecordset RS <=== Fails there w/ "...exception" above

Funny thing...I've been using this code for a couple of months w/ no problems
Maybe a Server problem here ?

ANY insight welcomed. TIA - Bo

 

[Bob Barnes]

A solution is in "access.formscoding" - same Subject as above, dated 2/4/04

HTH - Bob

 

[onedaywhen]

Your code only use a recordset to copy the data into Excel. There are
serveral ways of doing the same without a recordset. One alternative
is to specify the target workbook and range in a query e.g.

SELECT *
INTO [Database=C:\MyWorkbook.xls;Excel 8.0].ATitle
FROM MyQuery

 

[onedaywhen]

Oops, got that syntax a bit wrong, should be:

SELECT *
INTO [Excel 8.0;Database=C:\MyWorkbook.xls;].ATitle
FROM MyQuery

 

[Bob Barnes]

Always pleased to learn other ways of making things happen.

Thank you - Bob