How to avoid inadvertently hiding tables when using Select..Into?

[beacon]

Hi everybody,

[Access 2010]

I'm not sure if the subject will ultimately describe my issue, but it was the best I could come up with based on the info I've got.

I'm working on a database that I inherited from a previous employee and it's very basic, at least in terms of objects...there's only tables and queries, no code. However, the queries depend on other queries in order to perform their functions, i.e. a Make Table query creates a table that is used in the definition of a second query.

Due to the large number of queries the dependency, I wanted to automate the entire process. Because I'm working with Make Table queries, one of the steps I have to complete is to delete the table that the Make Table query produces since the Make Table query may be used multiple times in a loop (changing the table used in the definition of the query each iteration).

So, I'm attempting to loop through the TableDefs collection looking for the table names that the Make Table query is supposed to create each iteration. The problem that I'm running into though, is that my loop through TableDefs doesn't always find a particular table...almost as if it's hidden. I can unhide the hidden tables and the table is there, but the table properties don't show the table to be hidden. I've stepped through the code to see each of the table names that are available in the TableDefs loop, but for some reason the table isn't found.

This leads me to another problem that I alluded to, which is that I can't use the Make Table query if the table created by the query already exists (it produces error 3010).

I'm stumped as to why the table isn't showing up in the TableDefs collection. Can anyone tell me why this is occurring?

Here's my code...I'll break it up into snippets and provide some info on what I'm trying to do at each step:

Declare variable, assign Table/QueryDefs to variables, create an array of table names that will be used in the query definition (changing at the For..Loop iteration) for the "Add TPI" query.

    Dim db As DAO.Database
    Dim td As DAO.TableDefs
    Dim t As Object
    Dim qdfBigCountP1 As DAO.QueryDef
    Dim qdfBigCountP2 As DAO.QueryDef
    Dim qdfBigCountP3 As DAO.QueryDef
    Dim qdfBigCountP4 As DAO.QueryDef
    Dim qdfEligibilityCheck As DAO.QueryDef
    Dim strBigCountP1SQL As String
    Dim strBigCountP3SQL As String
    Dim strBigCountP4SQL As String
    Dim strEligibilityCheckSQL As String
    Dim arrTables(1) As String
    Dim countTable As Integer
    Dim strTable As String
    Dim strWrap As String
    Dim strNoWrap As String
    Dim blnTablesFound As Boolean
 
    Set db = CurrentDb()
    Set td = db.TableDefs
    Set qdfBigCountP1 = db.QueryDefs("Big Count P1")
    Set qdfBigCountP2 = db.QueryDefs("Big Count P2")
    Set qdfBigCountP3 = db.QueryDefs("Big Count P3")
    Set qdfBigCountP4 = db.QueryDefs("Big Count P4")
    Set qdfEligibilityCheck = db.QueryDefs("Eligibility Check")
 
    arrTables(0) = "Title V Selected Providers (Jan 15th)"
    arrTables(1) = "Title XX Selected Providers (Jan 15th)"
 

Start the main For..Loop, the strWrap and strNoWrap values are important for the hidden table problem, then I assign new SQL to the queries used.

    For countTable = 0 To UBound(arrTables)
        strTable = arrTables(countTable)
 
        Select Case countTable
            Case 0
                strWrap = "Undup Title V Wrap (Jan 15th)"
                strNoWrap = "Undup Title V No Wrap (Jan 15th)"
            Case 1
                strWrap = "Undup Title XX Wrap (Jan 15th)"
                strNoWrap = "Undup Title XX No Wrap (Jan 15th)"
            Case Else
                Debug.Print "An error occurred in the Select..Case Statement"
        End Select
 
        td.Refresh
 
        strBigCountP1SQL = "SELECT [" & strTable & "].Provider, [" & strTable & "].PCN, [" & strTable & "].FDOS, [" & strTable & "].[Claim Type], [" & strTable & "].SSN, " & _
                           "[" & strTable & "].[Procedure Code], [" & strTable & "].[Current Claim Status], [" & strTable & "].Sex, FiscalYearAge([" & strTable & "].DOB) " & _
                           "AS Age, [" & strTable & "].[Allowed Quantity], [" & strTable & "].[Billed Quantity], [" & strTable & "].[Modifier 1], [" & strTable & "].Paid, " & _
                           "[" & strTable & "].[DHS County Code], [" & strTable & "].TPI INTO [Core Count P1] FROM [" & strTable & "] WHERE (((" & _
                           "[" & strTable & "].[Current Claim Status])<>'D'));"
 
        qdfBigCountP1.SQL = strBigCountP1SQL
        qdfBigCountP1.Close
 
        strEligibilityCheckSQL = "SELECT [" & strTable & "].* FROM [" & strTable & "] INNER JOIN [WHP SSN Undup] ON [" & strTable & "].SSN=[WHP SSN Undup].SSN " & _
                                 "WHERE ((([" & strTable & "].FDOS) Between [Beginning Eligibility] And [Ending Eligibility]) AND ((" & _
                                 "[" & strTable & "].[Current Claim Status])<>'D'));"
 
        qdfEligibilityCheck.SQL = strEligibilityCheckSQL
        qdfEligibilityCheck.Close
 
        strBigCountP3SQL = "SELECT [Core Count P1].Provider, [Core Count P1].PCN, [Core Count P1].[Current Claim Status], [Core Count P1].[Procedure Code], " & _
                           "[Core Count P1].SSN, Sum([Core Count P1].[Billed Quantity]) AS [Billed Quantity], Sum([Core Count P1].[Allowed Quantity]) AS [Allowed Quantity], " & _
                           "First([Core Count P1].[Claim Type]) AS [Claim Type], Sum([Core Count P1].Paid) AS Paid, First([Core Count P1].Sex) AS Sex, " & _
                           "First([Core Count P1].Age) AS Age, First([Core Count P1].[Modifier 1]) AS [Modifer 1] INTO [" & strNoWrap & "] FROM [Core Count P1] GROUP BY " & _
                           "[Core Count P1].Provider, [Core Count P1].PCN, [Core Count P1].[Current Claim Status], [Core Count P1].[Procedure Code], [Core Count P1].SSN;"
 
        qdfBigCountP3.SQL = strBigCountP3SQL
        qdfBigCountP3.Close
 
        strBigCountP4SQL = "SELECT [Eligibility Check].Provider, [Eligibility Check].PCN, [Eligibility Check].FDOS, [Eligibility Check].[Procedure Code], " & _
                           "[Eligibility Check].[Current Claim Status], [Eligibility Check].SSN, First([Eligibility Check].[Claim Type]) AS [Claim Type], " & _
                           "Sum([Eligibility Check].Paid) AS Paid, Sum([Eligibility Check].[Allowed Quantity]) AS [Allowed Quantity], " & _
                           "Sum([Eligibility Check].[Billed Quantity]) AS [Billed Quantity], [Eligibility Check].[Modifier 1] AS [Modifer 1], " & _
                           "First([Eligibility Check].Sex) AS Sex, First(FiscalYearAge([Eligibility Check].DOB)) AS Age, [Eligibility Check].[DHS County Code], " & _
                           "[Eligibility Check].TPI INTO [" & strWrap & "] FROM [Eligibility Check] GROUP BY [Eligibility Check].Provider, [Eligibility Check].PCN, " & _
                           "[Eligibility Check].FDOS, [Eligibility Check].[Procedure Code], [Eligibility Check].[Current Claim Status], [Eligibility Check].SSN, " & _
                           "[Eligibility Check].[Modifier 1], [Eligibility Check].[DHS County Code], [Eligibility Check].TPI;"
 
        qdfBigCountP4.SQL = strBigCountP4SQL
        qdfBigCountP4.Close
 
        td.Refresh
 

This is still in the main For..Loop. The next For..Loop looks for the "Core Count P1" table in the TableDefs collection (no problem here), then the next For..Loop looks for the tables that are assigned to the strWrap and strNoWrap tables (I use the boolean value to try to delete both tables in the loop while also being able to exit the loop without having to iterate through everything in the TableDefs collection). Then I execute each of the QueryDefs...qdfBigCountP3.Execute and/or qdfBigCount4.Execute have been throwing the 3010 error because those tables create the Wrap and No Wrap tables.

        For Each t In td
            If t.Name = "Core Count P1" Then
                td.Delete "Core Count P1"
                Debug.Print "The Core Count P1 table was found and deleted."
                Exit For
            End If
        Next t
 
        td.Refresh
 
        blnTablesFound = False
 
        For Each t In CurrentDb.TableDefs
            Debug.Print t.Name
            If t.Name = strWrap Then
                td.Delete strWrap
                Debug.Print "The " & strWrap & " table was found and deleted."
                Debug.Print blnTablesFound
                If blnTablesFound = True Then
                    Exit For
                Else
                    blnTablesFound = True
                End If
            ElseIf t.Name = strNoWrap Then
                td.Delete strNoWrap
                Debug.Print "The " & strNoWrap & " table was found and deleted."
                Debug.Print blnTablesFound
                If blnTablesFound = True Then
                    Exit For
                Else
                    blnTablesFound = True
                End If
            End If
        Next t
 
        qdfBigCountP1.Execute dbFailOnError
        qdfBigCountP2.Execute dbFailOnError
        qdfBigCountP3.Execute dbFailOnError
        qdfBigCountP4.Execute dbFailOnError
 
    Next countTable
 
    Set qdfEligibilityCheck = Nothing
    Set qdfBigCountP4 = Nothing
    Set qdfBigCountP3 = Nothing
    Set qdfBigCountP2 = Nothing
    Set qdfBigCountP1 = Nothing
    Set qdfBigCountP1 = Nothing
    Set td = Nothing
    Set db = Nothing
 
End Sub
 

Any ideas? Sorry for the super long post, but I wanted to make sure I provided enough info.

Thanks,
beacon

[beacon]

Never mind...I figured it out. For some reason I used:

Dim t as Object
 

...instead of:

Dim t as DAO.TableDef
 

I don't have any clue why I used Object instead of TableDef,, but, as is usually the case, as soon as I post something here I see the error.

Thanks,
beacon

[beacon]

I have a follow up to this that I'm hoping someone can answer. I had the following line in my code:

For each t in td
 

...and when the subroutine completed, the tables created by the Make Table query are still hidden or, at least, don't show up in the Navigation Pane.

When I change the line to:

For each t in CurrentDB.TableDefs
 

...it appears to work and make the newly created tables appear in the Navigation Pane.

Why would using 'td' over 'CurrentDB.TableDefs' make a difference if 'td' is a variable that is assigned to 'db.TableDefs' in my code? Can anyone explain why the tables created by the Make Table query would be hidden?

Thanks,
beacon

[zmbd]

It may be the use of parentheses
I know it sounds crazy... but try it
(1) set td=CurrentDB not
(2) set td=CurrentDB()

It has to do with another way of referring to the database
(3) DBEngine.Workspaces(0).Databases(0)

From what little I understand, (1) has a more direct relationship to the user graphical interface...
Really, I don't understand it anymore than that nor have I done any research into the differences. What I do know is that when I accidentally use (2) that when I type that first "(" I am prompted for an item index as a table definition and therein may lay the issue (just an educated guess... )

-z