Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: Finding Table Name in Access 2000 ...

    Hi all.

    Ok. Once again, I know this topic has been posted on before but a search returns no useful results ...

    I need a means to find if a table exists in Access. I know it involves the Tables Collection ... I can't find the source code (snippet) for doing so tho. If someone would be so kind as to post it again, I would thank you ...

    Reason: Upgrading a code module and thought it would be better than the way I did it ... (ages ago!)

    Thanks!!!!
    Back to Access ... ADO is not the way to go for speed ...

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    How about:
    Code:
    Function TableExists(tblName As String) As Boolean
    
        Dim tbd As TableDef
    
        For Each tbd In CurrentDb.TableDefs
            If tbd.Name = tblName Then TableExists = True
        Next
    
    End Function

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    That will do! Thanks.
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Matthew Reeves
    How about:
    Code:
    Function TableExists(tblName As String) As Boolean
    
        Dim tbd As TableDef
    
        For Each tbd In CurrentDb.TableDefs
            If tbd.Name = tblName Then TableExists = True
        Next
    
    End Function
    Well ... Actually it won't. THis requires using DAO 3.X library AND the "Name" property (tbd.Name) does not exist...

    Hmmmm ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok. This works (using the DAO 3.6 Library):

    Code:
        Dim tbd As DAO.TableDef
        Dim TableExists As Boolean
        
        ' Test for existing table ...
        TableExists = False
        For Each tbd In CurrentDb.tabledefs
            If tbd.Name = CC_DailyTbl Then TableExists = True
        Next
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by M Owen
    Ok. This works (using the DAO 3.6 Library):

    Code:
        Dim tbd As DAO.TableDef
        Dim TableExists As Boolean
        
        ' Test for existing table ...
        TableExists = False
        For Each tbd In CurrentDb.tabledefs
            If tbd.Name = CC_DailyTbl Then TableExists = True
        Next
    Now, what would be REALLY NICE is an ADO version ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    OKAY! I've been playing with this thread: http://www.dbforums.com/t1005591.html

    and have come up with the following ADO code:
    Code:
        Dim i As Integer, TableExists As Boolean
        Dim CAT As ADOX.Catalog
        Dim i As Integer
        
        ' Test for existing table ...
        TableExists = False
        
        Set CAT = New ADOX.Catalog
        CAT.ActiveConnection = CurrentProject.Connection
        For i = 0 To CAT.Tables.Count - 1
            If CAT.Tables(i).Name = CC_DailyTbl Then
                TableExists = True
                Exit For
            End If
        Next
        Set CAT = Nothing
    Thanks goes to SCIROCCO!

    P.S. - You'll need to reference the "MS ADO Ext. 2.X for DDL an Security" library ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •