Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

List of Tables in VBA that hides deleted tables

breefcbreefc Member Posts: 4
Hello, I got great help on this site with this problem, and now have another issue.

I have a form that has a list of tables within the database as a list box from which someone can choose a table to compare with another table. Works nicely, but displays ALL tables ever created, even the deleted ones. Could someone please help me to have it only display tables that exist?





Private Sub Form_Load()

Dim db As Database, tbl As TableDef
Set db = CurrentDb

For Each tbl In db.TableDefs
If Left$(tbl.Name, 4) <> "MSys" Then
lsTables.RowSource = tbl.Name & "; " & lsTables.RowSource
End If
Next

End Sub

Comments

  • DaiMitnickDaiMitnick Member Posts: 77
    Hi breefc, try as I might I can't really re-create this problem, my databases never show deleted tables, so makes it hard to diagnose. However, it did sometimes display the tmp file of the table after deletion but this disappeared by the time I create a new table, I found it also deleted when I 'repaired' the database, so could you try seeing if that solves it. To do it:

    [red]Go to Tools > Database Utilities > Compact and Repair Database...[/red]

    Really hope that works, if not send some more info if you can, preferably the database itself if possible (obviously empty all records first), or whatever code and screenshots you can. Dai.


    ------------------------------------------
    Do or do not, there is no try. |
    ------------------------------------------
  • breefcbreefc Member Posts: 4
    The whole database? It's rather large.

    Here's all my code so far. It still displays the old, deleted tables in the drop down menu of my form even after I have compacted.


    Private Sub Command4_Click()

    End Sub

    Private Sub Command6_Click()
    On Error GoTo Err_Handler


    Dim dbs As Database
    Dim strSQL As String
    Dim strQueryName As String
    Dim qryDef As QueryDef
    Dim tablename As String



    tablename = Me.lsTables.Value




    'set variable values
    Set dbs = CurrentDb
    strQueryName = "main_list"
    'delete old query, if it exists
    dbs.QueryDefs.Delete strQueryName
    strSQL = "SELECT archive.* FROM [" & tablename & "] INNER JOIN archive ON [" & tablename & "].email = archive.email;"
    Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
    DoCmd.OpenQuery (strQueryName)

    Exit Sub:



    Err_Handler:
    Select Case Err.Number

    Case 94
    MsgBox "Did you forget to select an email to compare, you silly sausage?"
    Case 3265
    MsgBox Err.Number & Err.Description
    Case 3270
    MsgBox "ACK YOU BROKE IT!! (Please choose the latest archive date (yesterday)"
    Case Else
    MsgBox Err.Number
    Resume Next
    End Select
    End Sub


















    Private Sub Form_Load()

    Dim db As Database, tbl As TableDef
    Set db = CurrentDb

    For Each tbl In db.TableDefs
    If Left$(tbl.Name, 4) <> "MSys" Then
    lsTables.RowSource = tbl.Name & "; " & lsTables.RowSource
    End If
    Next

    End Sub


    '------------------ Code End -------------------

  • DaiMitnickDaiMitnick Member Posts: 77
    Like I said it's rather difficult to solve it as I can't see the problem myself. The only thing I can think of trying is to run an sql query instead hoping that somehow it won't pick up the deleted databases. Try adding another query, just like the one you already made but this time change strSQL value to:

    [code] strSQL = "SELECT Name FROM MSysObjects WHERE Type = 1" [/code]

    Run that query and see if it has old table names or not (it will have the system tables, but we can filter them out later), if it works perhaps we can work from that to fill the list.
    ------------------------------------------
    Do or do not, there is no try. |
    ------------------------------------------
Sign In or Register to comment.