Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005

    Unanswered: Check whether a table exists in a .mdb file or not


    I needed a query that could check if a table exists in the access database or not. i.e. say I have an .mdb file named 'TestAcces.mdb' and I have to query this to check whether a particular table say 'TestTable' exists or not.

    I am looking something similar to the MS SQL Server Query
    if exists (select * from sysobjects where name='TestTable' and type ='U')
    Can anyone please help me out with this.

    Thanks in advance


  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    Hi Dex

    (DAO) code I use below:

    Public Enum ObjectTypes
    	ObjForm = -32768
    	ObjMacro = -32766
    	ObjReport = -32764
    	ObjModule = -32761
    	ObjLocalTable = 1
    	ObjODBCLinkedTable = 4
    	ObjJetLinkedTable = 6
    	ObjQuery = 5
    End Enum
    Public Function fObjectExists(ObjectName As String, ObjectType As ObjectTypes, Optional db As dao.Database) As Boolean
    	Dim KillDB As Boolean
    	Dim rst As dao.Recordset
    	If db Is Nothing Then
    		Set db = CurrentDb
    		KillDB = True
    	End If
    On Error Resume Next
    	Set rst = db.OpenRecordset("SELECT [Name] FROM MSysObjects WHERE [Name] = '" & ObjectName & "' AND [Type] = " & ObjectType, dbOpenSnapshot, dbReadOnly, dbReadOnly)
    	If Not Nz(rst.Fields(0), vbNullString) = vbNullString Then
    		If Not Err.Number = 0 Then
    			fObjectExists = False
    			fObjectExists = True
    		End If
    		fObjectExists = False
    	End If
    	Set rst = Nothing
    	If KillDB Then
    		Set db = Nothing
    	End If
    End Function
    By default it searches the currentdb however you can instantiate a db object for a remote db and pass that as an arguement. Obviously, this only works for access dbs.

    Ignoring errors is, of course, not not normally good practice - I just popped it in at the time as I was struggling checking the EOF of recordsets. Can't remember why... but it makes no difference in this function anyhoo.

    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    And here's a function I've created and used:

    Public Function jpsIsObjectExist(pintObjectType As Integer, pvarObjectName As Variant) As Integer
    ' Purpose: Determine if object exists.
    ' Remarks: pintObjectType can be acTable, acQuery, acForm, acReport, acMacro, or acModule.
    ' (By the way those last Access constants are 0, 1, 2, 3, 4, and 5 respectively.)
    ' Returns True if exists, otherwise False.
    ' If any other possible error, returns False.
    ' This function actually selects an object in the database window
    ' which COULD interfere with other database processing.
    ' If SelectObject causes an error, then object doesn't exist and
    ' this function correctly returns False.

    On Error GoTo Err_jpsIsObjectExist

    ' Quick exit as False if ObjectName is null.
    If IsNull(pvarObjectName) Then
    jpsIsObjectExist = False
    Exit Function
    End If

    ' Try to select object and if there is an error, then it doesn't exist.
    DoCmd.SelectObject pintObjectType, pvarObjectName, True
    jpsIsObjectExist = True

    Exit Function

    ' Set to false.
    jpsIsObjectExist = False
    Exit Function

    End Function
    J. Paul Schmidt, Freelance Web and Database Developer
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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