Case -2147417851 '"The server threw an exception"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

However, today, I am using the "CopyFromRecordset" Excel method
I am receiving the "Subject" error above. There is no one else in th
Back-end to Lock the Query doing a "MakeTable" which is used as
String Argument for the "CopyFromRecordset"

Any insight welcomed

TIA - Bob
 
Bob Barnes said:
I am using Office 2000 (Access to Excel) Automation which I have used
quite a bit.

However, today, I am using the "CopyFromRecordset" Excel method.
I am receiving the "Subject" error above. There is no one else in the
Back-end to Lock the Query doing a "MakeTable" which is used as a
String Argument for the "CopyFromRecordset".

Any insight welcomed.
Click to expand...

I don't follow your explanation. Where does a make-table query come
into it, or a string argument for the CopyFromRecordset method? As far
as I can see, that method has no string arguments, and a make-table
query doesn't return a recordset, so I'm rather lost. Would you post
the code you're using?
 
Lots of code (appropriate snips)..

Private Sub cmdGoChart_Click(
Dim Z As Database, RS As DAO.Recordset, AQQ As DAO.QueryDe
Dim PM As DAO.Parameter, M$, N$, Q$, File
Dim objXLApp As Object 'Excel.Ap
Dim objXLWb As Object 'Excel.Workboo
Dim objXLSheet As Object 'Excel.Workshee
On Error GoTo AAA
...
Select Case cboMgm
Case "Mgr
File = "C:\BobDev\PDMgr.XLS
End Selec
...
Q = "SELECT RecDate, Round(Avg(TotPts),1) AS ThePts"
& " FROM RScores GROUP BY RecDate, AGroup"
& " HAVING RecDate <= #" & cboChartDate & "# AND"
& " AGroup = """ & cboMgmt & """;
N = "DataSheet
Call CopyTheData(Q, File, N, "TheData"
'Save wb, close up other rs objects, & quit Excel - Done in "CopyTheData
DoCmd.Hourglass Fals
If bLock = True The
bLock = Fals
Els
M = "The File " & File & vbCrL
M = M & "For " & cboMgmt & " Has Been Updated.": MsgBox M, , "
End I
AAA2
Call ShowHide
Exit Su
AAA1
Select Case Er
Case Els
MsgBox "Error Number " & Err.Number & " " & Err.DESCRIPTION: Resume AAA
End Selec
End Su

More..
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
'DoCmd.SetWarnings Fals
Outa
objXLWb.Save: objXLWb.Clos
'DoCmd.SetWarnings Tru
'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
'DoCmd.Hourglass Fals
Exit Su
ProcError
Select Case Er
Case -2147417851 '"The server threw an exception

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
 
Dirk - Hope you return to read this. I've also posted thi
in "excel.programming" this AM

Also searched the MS KB for "The server threw an exception" - nothing as suc
in Access 2000, but a Win2K Patch was mentioned

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...certainly not big

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 - Bob
 
Bob Barnes said:
Dirk - Hope you return to read this. I've also posted this
in "excel.programming" this AM.

Also searched the MS KB for "The server threw an exception" - nothing
as such
in Access 2000, but a Win2K Patch was mentioned.

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 Recordset
of up to 52 records with 2 fields per Record...certainly not big.

Parts of my code...
Set RS = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot) <== No
problem w/ the "strSQL" 'start Excel
Set objXLApp = CreateObject("Excel.Application")
...................
'select desired worksheet
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 - Bob
Click to expand...

I don't have much to offer. It could be a problem with the server or
the installation of DAO or Jet, especially if this exact routine in this
same database was working before and has suddenly stopped. I would
certainly investigate problems with the recordset or the call to
CopyFromRecordset first, though. You might try:

(a) Verifying (by inspection in the Locals Window) that the recordset is
open and not at EOF when it is passed to the CopyFromRecordset method.

(b) Opening a dynaset-type recordset instead of a snapshot --
dbOpenDynaset instead of dbOpenSnapshot.

(c) Specifying the number of rows to copy, in the call to
CopyFromRecordset. I seem to recall this being an issue the one time in
the past that I used this method, but that's all hazy now. I *think*
that if you specify more rows than the recordset actually contains, it's
still okay. However, if you open the recordset as a dynaset you can do
RS.MoveLast, Rs.MoveFirst, and then pass RS.RecordCount to the
CopyFromRecordset method as the number of rows.
 
Dirk - I got it to work, but I'm not sure why.

I looked up "CopyFromRecordset" in Excel, & saw...
expression.CopyFromRecordset(Data, MaxRows, MaxColumns)

The code I had from the Newsgroups 3 - 4 months ago was...
expression.CopyFromRecordset RS <=== where "RS" was "Set RS..." above in the code.

So, I set
objXLSheet.Range(strCellRef).CopyFromRecordset RS, 100, 2

& it works.

Thanks again, Bob
 
Bob Barnes said:
Dirk - I got it to work, but I'm not sure why.

I looked up "CopyFromRecordset" in Excel, & saw...
expression.CopyFromRecordset(Data, MaxRows, MaxColumns)

The code I had from the Newsgroups 3 - 4 months ago was...
expression.CopyFromRecordset RS <=== where "RS" was "Set RS..."
above in the code.

So, I set
objXLSheet.Range(strCellRef).CopyFromRecordset RS, 100, 2

& it works.

Thanks again, Bob
Click to expand...

Wasn't that my third suggestion? <g> Glad you found an answer. As I
said, I vaguely remember running into a problem with CopyFromRecordset
once before, where the solution was to specify the number of rows to
copy.
 
Back
Top