DoCmd failing to find query object checked for in set of QryDefs

[John Pritchard]

Hi,

I'm getting an intermittent problem with the following
code:-


Sub CheckQry()

Dim i As Integer

With dbscredan

For i = 0 To .QueryDefs.Count - 1

If .QueryDefs(i).Name = "MainQry" Then

DoCmd.DeleteObject acQuery, .QueryDefs
(i).Name

End If

Next i


End With

End Sub

This works correctly most of the time but occasionally
fails with message :

7874 Unable to find object... on the DoCmd statement. The
dbscredan is a DAO connection. I'm using Access 2000
(9.0.3821) and can't find any reason for the problem.

The process I'm running is designed to dynamically create
a query from data in an excel spreadsheet. I was hoping to
allow users to call the Getdata process any number of
times in an access session - currently I limit them to one
load per session to avoid this problem. The code is quite
simple and is given below - Any help would be much
appreciated as this is my first Access application and I'd
like it to work well.

Thanks for any help

John P.

ps Full Code:-

' Global declaration
Dim dbscredan As DAO.Database


Function GetData(ByVal FullPath As String) As Boolean
Dim Centre As String
Dim TableName As String
Dim i As Integer
Dim qrystr As String
Dim xlapp As Excel.Application

On Error GoTo ErrorHandler

'Open database and workbook

Set dbscredan = DBEngine.Workspaces(0).Databases(0)

' Open Excel Application

Set xlapp = New Excel.Application

xlapp.Workbooks.Open filename:=FullPath

For i = 1 To xlapp.Worksheets.Count

Centre = xlapp.Worksheets(i).Name
TableName = Centre & "tbl"

' Check table exists if does delete it


Call CheckTable(TableName)

' Create the table for this tab (centre) with a
link back to the excel sheet

DoCmd.TransferSpreadsheet acLink,
acSpreadsheetTypeExcel9, TableName, _
FullPath, -1, Centre & "!"

' dynamically create sql string to place in main
qry

If i < xlapp.Worksheets.Count Then

qrystr = qrystr & "SELECT " & """" & Centre
& """" & " as centre,* FROM " & TableName & _
" Where Amount Is Not Null And batch Is Not
Null Union ALL "

Else

qrystr = qrystr & "SELECT " & """" & Centre
& """" & " as centre,* FROM " & TableName & _
" Where Amount Is Not Null And batch Is Not
Null;"

End If

Next i

' check if main query exists if so delete it

Call CheckQry

' create main qry

Call CreateMainQry(qrystr)

' Close workbook - and Excel application

xlapp.ActiveWorkbook.Close

xlapp.Quit
Set xlapp = Nothing

GetData = True

Exit Function

ErrorHandler:

Select Case Err.Number
Case 1004
MsgBox "File " & FullPath & " can not be found
or can not be accessed."
Case Else
MsgBox Err.Number & " : " & Err.Description
End Select

GetData = False

End Function

Sub CreateMainQry(ByVal qrystr As String)

Dim MainQry As QueryDef

With dbscredan

Set MainQry = .CreateQueryDef("MainQry", qrystr)

End With

End Sub

Sub CheckTable(ByVal TableName As String)

Dim i As Integer

With dbscredan

For i = 0 To .TableDefs.Count - 1

If .TableDefs(i).Name = TableName Then

DoCmd.DeleteObject acTable, TableName

End If

Next i


End With

End Sub

Sub CheckQry()

Dim i As Integer

With dbscredan

For i = 0 To .QueryDefs.Count - 1

If .QueryDefs(i).Name = "MainQry" Then

DoCmd.DeleteObject acQuery, .QueryDefs
(i).Name

End If

Next i


End With

End Sub

 

[Van T. Dinh]

What is "dbscredan"?

If "dbscredan" is not the Current Database, the DoCmd.Delete won't find the
QueryDef Object in "dbscredan" as DoCmd applies to the Current Database
(i.e. the one open through the Access interface and not another database you
opened by code.

 

[John Pritchard]

Hi,

dbscredan is a DAO connection to the current database &
this is where the application code is as well as the data.
No other databases are involved. I click on the icon for
the database and the startup procedure opens my
application.

Only my checkqry procedure fails and this only after 2 or
3 successful loads. My checktable procedure follows the
same logic but has never failed ?

Is there an alternative to using DoCmd. I'm happy to use
another method to remove the querydef thats no longer
required.

Thanks for your help

John P.

 

[Van T. Dinh]

Sorry. I just realised that you posted the full code
(which I didn't see) after the code snippet and my
previous question was answered by the full code.

Actually, your code is fairly inefficient and can error
out as well. The reasons are:

1. You For loop will be executed throughout the index
regardless where you find the QueryDef. You should really
use Exit For to get out of the For loop as soon as you
delete the QueryDef.

2. Once you delete a QueryDef, the Collection is re-
indexed and the last iteration of For loop will error out
as the Count has been reduced by one.

OTOH, you simply want to delete the Query so it is more
efficient to simply try to delete the Query with error-
trapping to trap and ignore the error created by the non-
existent Query. Something like:

On Error Resume Next
DoCmd.DeleteObject acQuery, "MainQuery"

HTH
Van T. Dinh
MVP (Access)

 

[John Pritchard]

Hi,

I changed the code to allow then ignore any error and
things are working fine - Thanks.

I'd like to experiment with calling a generic error
handler - I'm not very fond of resumes and goto. Do you
have any general guidance - or could point me towards a
good article. I'll raise another question to cover this if
necessary as I realise you've answered my original query
well.

Thanks again

John P.

 

[Van T. Dinh]

The Resume and GoTo have no problems if you use them in a
properly-constructed error-trapping. In fact, they are suggested by
Microsoft.

I don't worry about calling a generic error-handler. I like to see which
module / procedure errors out in the error message-box so each procedure has
its own error-trapping. I use an add-in utility "Code Commenter" (part of
Total Visual CodeTools from FMS http://www.fmsinc.com ) which takes no time
to add error-trapping to every piece of code in the database.