Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unanswered: How do I test for existance of a table

    How do I check to see if a table exists first, so I can create it if it does not exist or use existing data if it does exist.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Here is the manner using ADO, you must refrence ADO and ADOX.

    Code:
    Dim DBPath As String
    
    Private Sub Form_Load()
        Dim CAT As ADOX.Catalog
        Dim TBL As ADOX.Table
        Dim CON As ADODB.Connection
        Dim X As Integer
        
        'Database path
        DBPath = "D:\Andrew\SatWater.mdb"
        
        'Make a new connection
        Set CON = New ADODB.Connection
        CON.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & "; Jet OLEDB:Database Password=;"
        CON.Open
        
        'New catalog
        Set CAT = New ADOX.Catalog
        CAT.ActiveConnection = CON
        
        'Print them all out
        For X = 0 To CAT.Tables.Count - 1
            Debug.Print CAT.Tables(X).Name
        Next X
        
        'Clean up
        CON.Close
        Set CON = Nothing
        Set CAT = Nothing
        Set TBL = Nothing
    End Sub
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Here is the manner using DAO, you must reference DAO 3.6.

    Code:
    Public wrkPan As DAO.Workspace
    Public dbPan As DAO.Database
    
    'Open the database
    Public Sub OpenPANDb(UName As String, DBPathAndFile As String, PWD As String)
        'Get the workspace
        Set wrkPan = CreateWorkspace("", UName, "", dbUseJet)
        'Get the database
        Set dbPan = wrkMidas.OpenDatabase(DBPathAndFile, False, False, ";pwd=" & PWD)
    end sub
    
    Public Function TableExists(TableName As String) As Boolean
        Dim TDef As DAO.TableDef
    
        'Initialize
        TableExists = False
        
        'Does the DataSets table exist?
        For Each TDef In dbPan.TableDefs
            If UCase(TDef.Name) = UCase(TableName) Then
                TableExists = True
                Exit Function
            End If
        Next
    End Function
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  4. #4
    Join Date
    Sep 2004
    Posts
    10

    Write a Query to Find the Table

    There is an easier way. Query the MSysObjects table on the "Name" field.

    (If you don't see the table, go to Tools | Options. Click the "View" tab. Check the box entitled "system objects.")

    For example, this SQL statement will return 1 if the "tblCustomer" table exists:

    SELECT Count(MSysObjects.Name) AS [Count]
    FROM MSysObjects
    GROUP BY MSysObjects.Name
    HAVING (((MSysObjects.Name)="tblCustomer"));

    If the "tblCustomer" table does not exist, no records will be returned.

    You can use DLookup and NZ to get a 1 or a 0. Here is the function:

    Public Function DoesItExist()

    DoesItExist = Nz(DLookup("[Count]", "qryTableExists"), 0)

    End Function

    GOOD LUCK

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    Use this function fExistTable from the Access Web
    http://www.mvps.org/access/tables/tbl0001.htm

    Put the function in a Module. I usually have a modual called 'Utilities' or 'Functions' to keep general purpose functions for accessability from any script. With the function in place you can write something like this to determine if the table exists and perform an action depending on the result.

    If fExistTable Then
    ' The table exists continue.
    Else
    ' Create the Table
    End If
    ~

    Bill

Posting Permissions

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