Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Question Unanswered: How to write VBA to check if a table/form/query exists in Access2000?

    Hello,

    In Access2000, how to write a VBA function/Sub to detect if a table/form/query exists or not?

    and, how to detect if a file in the filesystem exists or not?

    I would like to create a button, when click, export a query to Excel format, but firstly, i need to determine if the query exist, so that to set the button visible or not. Then, after click, i need to determine if the Excel file already exists, delete it first if it exists.

    Thanks~~

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    For each item you seek...each contain their own collection and the information can be obtained.

    Check out TableDefs for tables, QueryDefs for queries, and forms are considered objects. Search your Access help for these items and while your at it...take a look at the For Each statement...you can bring up samples as to do what you want.


  3. #3
    Join Date
    Dec 2003
    Posts
    10
    Originally posted by CyberLynx
    For each item you seek...each contain their own collection and the information can be obtained.

    Check out TableDefs for tables, QueryDefs for queries, and forms are considered objects. Search your Access help for these items and while your at it...take a look at the For Each statement...you can bring up samples as to do what you want.

    Sorry, i find nothing...can you post me some examples or URLs?

  4. #4
    Join Date
    Dec 2003
    Posts
    1

    Re: How to write VBA to check if a table/form/query exists in Access2000?

    Something like this should work:


    Function inTables(tblName As String)

    Dim tdfLoop As TableDef
    Dim dbs As Database
    Dim exists As Integer


    exists = 0

    For Each tdfLoop In CurrentDb.TableDefs
    If tdfLoop.Name = tblName Then
    exists = 1
    Exit For
    End If
    Next

    inTables = exists

    End Function

    Do something similar, using the Dir() function for the file...

  5. #5
    Join Date
    Dec 2003
    Posts
    10
    Thanks, it works nice~

    But, i don't understand how to detect if a file (in the filesystem) exits,
    and what command to delete the file if it exits??

  6. #6
    Join Date
    Dec 2003
    Posts
    10
    I have done it

    strFile = "c:\temp\test.xls"

    'Check if file exists
    If Len(Dir$(strFile)) > 0 Then
    'First remove readonly attribute, if set
    SetAttr strFile, vbNormal
    'Then delete the file
    Kill strFile
    End If

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Right on Mantat

Posting Permissions

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