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

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

    Hi

    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
    Code:
    if exists (select * from sysobjects where name='TestTable' and type ='U')
    .
    .
    .
    Can anyone please help me out with this.

    Thanks in advance

    -Dex

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Dex

    (DAO) code I use below:

    Code:
     
    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)
    	
    	rst.MoveFirst
    	
    	If Not Nz(rst.Fields(0), vbNullString) = vbNullString Then
    		
    		If Not Err.Number = 0 Then
    		
    			fObjectExists = False
    		Else
    		
    			fObjectExists = True
    			
    		End If
    	
    	Else
    		
    		fObjectExists = False
    		
    	End If
    	
    	rst.Close
    	Set rst = Nothing
    	
    	If KillDB Then
    		
    		db.Close
    		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.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    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

    Err_jpsIsObjectExist:
    ' Set to false.
    jpsIsObjectExist = False
    Exit Function

    End Function
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    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
  •