Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking Table Existence (2000)

    In the Module1 module of the attached file I've prepared DAO (CheckTables) and ADO (CheckTable) versions of a function that determines whether a table exists.
    I've run them against the Italgen table but only the DAO version correctly determines that the table exists.
    What's wrong with the ADO version?
    <font color=red>
    Function CheckTables(strTable) As Boolean
    On Error GoTo ErrorHandler
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTable)
    CheckTables = True
    ErrorHandlerExit:
    Exit Function
    ErrorHandler:
    If Err = 3265 Then
    CheckTables = False
    Resume ErrorHandlerExit
    Else
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
    End If
    End Function
    </font color=red><font color=448800>
    Function CheckTable(strTable) As Boolean
    On Error GoTo ErrorHandler
    Dim cat As New ADOX.Catalog
    Set cnn = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)
    CheckTable = True
    ErrorHandlerExit:
    Exit Function
    ErrorHandler:
    If Err = 3265 Then
    CheckTable = False
    Resume ErrorHandlerExit
    Else
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
    End If
    End Function</font color=448800>
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Checking Table Existence (2000)

    Hi,
    You missed out:
    cat.ActiveConnection = cnn
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Table Existence (2000)

    Thanks Rory,
    your input sparked my imagination and I've noticed that I can leave out
    Set cnn = CurrentProject.Connection
    if I type
    cat.ActiveConnection = CurrentProject.Connection
    instead of
    cat.ActiveConnection = cnn
    <font color=ff69b4>Ciao</font color=ff69b4>

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Addendum...

    I've done the same for queries but why doesn't the function detect crosstab queries such as FQuery1 query in the attached file?

    Function CheckQuery(strQuery) As Boolean
    On Error GoTo ErrorHandler
    Dim cat As New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    'Set cnn = CurrentProject.Connection
    Set tbl = cat.Views(strQuery)
    CheckQuery = True
    ErrorHandlerExit:
    Exit Function
    ErrorHandler:
    If Err = 3265 Then
    CheckQuery = False
    Resume ErrorHandlerExit
    Else
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
    End If
    End Function
    Attached Files Attached Files

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Addendum...

    Because a crosstab query is a procedure and not a view.
    Set tbl = cat.Procedures(strQuery)
    will not generate an error.
    Francois

Posting Permissions

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