Run-time Error 3422

[Rjc8513]

Cannot find a reference to Run-time error 3422 (Cannot modify table structure. Another user has the table open.)

Here is my code:

Function Create_Chart()

Dim dbs As Database

Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point

On Error GoTo Err_CreateChart

Set dbs = CurrentDb()

theAccessQuery = "sqry CANCELLATIONS by HOUR subform"
QueryName = "sqry CANCELLATIONS by HOUR CHART AccQry01"
NoQueries = dbs.QueryDefs.Count
For I = 0 To NoQueries - 1
If dbs.QueryDefs(I).Name = QueryName Then
DoCmd.DeleteObject acQuery, QueryName
Exit For
End If
Next I

dbs.CreateQueryDef QueryName, _
"SELECT [HOUR],[VOL by ZIP] " & _
"FROM [sqry CANCELLATIONS by HOUR subform];"

theExcelFile = "C:\TEMP\AccQry01.xls"

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, QueryName, _
theExcelFile, False

The code crashes at the "DoCmd..." line.

Can anyone help? Thanks.

 

[PHV]

You may try this:
...
dbs.CreateQueryDef QueryName, _
"SELECT [HOUR],[VOL by ZIP] " & _
"FROM [sqry CANCELLATIONS by HOUR subform];"
dbs.QueryDefs.Refresh
DoEvents
...
BTW, which line is highlighted when in debug mode after the error ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rjc8513]

PHV, thanks for the response. Your suggested changes didn't work.

The "DoCmd..." line is highlighted after the error.

 

[PHV]

Is "sqry CANCELLATIONS by HOUR CHART AccQry01" created and runnable ?
Is "C:\TEMP\AccQry01.xls" opened by someone ?
No ghost Excel.exe in the Task manager ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

The sheet name in Excel is the table/query name in Access. I'm not certain about this, but I think there is a limit on the number of characters in a sheet name - about 30 possibly?

 

[Rjc8513]

Yes PHV, the query in question does run and returns what I'm looking for, although I do create it on-the-fly. No, "C:\TEMP" is not otherwise open.

earth..., I tried your suggestion as well and that didn't work either.

Thanks to you both for trying.