VBA: DoCmd.OpenQuery failure after creating a new query

Posted on 2003-03-18
Medium Priority
Last Modified: 2008-03-10
Hey there boys and girls:

I have an interesting quirk when building queries in Access.  Don't fret over the code as it works perfectly.  My problem is if I uncomment the DoCmd.OpenQuery strQueryName, acViewNormal, acEdit, it either opens the new query created or it aborts telling me that it cannot find the query in the collection.  Never mind that I refresh the views and tables before the DoCmd.  Any clues as to why it fails sometimes and not others?  Is there another internal Access function available to update an index or something??


Public Function BuildQueryType(strFieldError As String, strTableSelected As String)
    Const conQueryAlreadyExists As Integer = 3012
    Const conQueryDeleted As Integer = 3167
    Dim objDatabase             As DAO.Database
    Dim objCatalog              As ADOX.Catalog
    Dim objQueryDef             As DAO.QueryDef
    Dim strSQLQuery             As String
    Dim strQueryName            As String
    Dim strQueryDescription     As String
    Dim intTextTruncation       As Integer
    On Error GoTo BuildQueryType_Err
    Set objCatalog = New ADOX.Catalog
    Set objDatabase = OpenDatabase(CurrentProject.FullName)
    strQueryName = RTrim("Qry " & strTableSelected & " w/Err_Fld = " & strFieldError)
    If Len(Trim(strQueryName)) > 65 Then
        intTextTruncation = Len(Trim(strQueryName)) - 65
        strQueryName = Mid(strQueryName, 1, Len(Trim(strQueryName)) - intTextTruncation)
    End If
    If Len(Trim(strFieldError)) = 0 Or _
       Len(Trim(strTableSelected)) = 0 Then
        MsgBox "Select a Table and Field Error value from the drop down list.", vbOKOnly + vbExclamation, CurrentProject.Name & ": BuildQueryType Function "
        strSQLQuery = "SELECT " & strTableSelected & ".Wkr_ID, Case_Serial(" & strTableSelected & ".Case_Num) AS Case_Serial, "
        strSQLQuery = strSQLQuery & "FBU(" & strTableSelected & ".Case_Num) AS FBU, Mult(" & strTableSelected & ".Case_Num) AS Mult, "
        strSQLQuery = strSQLQuery & strTableSelected & ".Case_Stat, " & strTableSelected & ".Pers_Num, " & strTableSelected & ".CIN, "
        strSQLQuery = strSQLQuery & strTableSelected & ".Last_Name, " & strTableSelected & ".First_Name, "
        strSQLQuery = strSQLQuery & strTableSelected & ".Err_Code, " & strTableSelected & ".Err_Fld, "
        strSQLQuery = strSQLQuery & strTableSelected & ".Rec_Desc, " & "MatchFldErrToScreenNames(" & strTableSelected & ".Sys_ID, " & strTableSelected & ".Err_Fld) AS Screen_Name, "
        strSQLQuery = strSQLQuery & strTableSelected & ".Err_Desc, "
        strSQLQuery = strSQLQuery & strTableSelected & ".Err_Val, " & strTableSelected & ".Err_Cor_Val, " & strTableSelected & ".Online_Corr_Ind, "
        strSQLQuery = strSQLQuery & strTableSelected & ".Smart_Wkr_ID, " & strTableSelected & ".Smart_ID, " & strTableSelected & ".Smart_SSN FROM " & strTableSelected & " WHERE (((" & strTableSelected & ".Err_Fld)= '" & strFieldError & "'));"
        With objDatabase
            Set objQueryDef = .CreateQueryDef(strQueryName, strSQLQuery)
        End With
    '   Update the Query Description
        strQueryDescription = "Show all rows in the " & strTableSelected & " where the error field =  '" & strFieldError & "'"
        Set objDatabase = OpenDatabase(CurrentProject.FullName)
        Set objQueryDef = objDatabase.QueryDefs(strQueryName)
        SetFieldProperty objQueryDef, "Description", dbText, strQueryDescription

        DoCmd.Close acForm, "frmQuerySelection", acSaveNo
    '   This commented code works sometimes and not others.  Gets an error that it cannot find the new query
    '   even though it exists in the database collection.
    '    DoCmd.OpenQuery strQueryName, acViewNormal, acEdit
    End If
    Set objDatabase = Nothing
    Set objCatalog = Nothing
    Set objQueryDef = Nothing
    Exit Function

    If Err = conQueryAlreadyExists Then
        With objDatabase
             .QueryDefs.Delete strQueryName
        End With
        If Err = conQueryDeleted Then
        End If
    ElseIf Err = conQueryDeleted Then
            MsgBox CStr(Err) & " " & Err.Description, , CurrentProject.Name & ": BuildQueryType procedure"
            Resume BuildQueryType_Exit
        End If
End Function

Question by:VBProEd
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 41

Accepted Solution

shanesuebsahakarn earned 500 total points
ID: 8163001
Set objDatabase = CurrentDb()
instead of:
Set objDatabase = OpenDatabase(CurrentProject.FullName)

perhaps ?

Author Comment

ID: 8163057
Yes, that was the trick!  Thanks.

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question