Testing for table existence 
Author Message
 Testing for table existence

I have created a table in an mdb using the CREATE TABLE sql statement.

How do I test for a particular table name and then delete it if it exists?

Thanks

(reply to newgroup and author please)

Ken



Fri, 09 Feb 2001 03:00:00 GMT  
 Testing for table existence
Testing if a table exist:

 Use the TableDefs collection.
     For i = 0 To db.TableDefs.Count - 1
         Select Case db.TableDefs(i).Name
         Case "tbltest", "tbltester"
                   Msgbox "These Tables Exist"
         End Select
     Next i

Delete a table:
DoCmd.DeleteObject acTable, "Former Employees Table"

Good luck

Greetings

Luc Debois
CIO TRAINING CENTER
BELGIUM


Quote:
>I have created a table in an mdb using the CREATE TABLE sql statement.

>How do I test for a particular table name and then delete it if it exists?

>Thanks

>(reply to newgroup and author please)

>Ken



Fri, 09 Feb 2001 03:00:00 GMT  
 Testing for table existence


Quote:
> How do I test for a particular table name and then delete it if it exists?

Although you could test to see if it exists, no need to.  SImply delete
it. If it's there, it'll go away.  If it's not, no harm done.  Just make
sure and handle the error that occurs when you try and delete a non-
existent table.  

Here's a simple solution:

Sub DeleteTable(strName as String)
    On Error Resume Next
    CurrentDb.TableDefs.Delete strName
End Sub

That should do it, as long as we're talking about a Jet table (you didn't
mention...) -- Ken



Sun, 11 Feb 2001 03:00:00 GMT  
 Testing for table existence

Quote:

> I have created a table in an mdb using the CREATE TABLE sql statement.

> How do I test for a particular table name and then delete it if it exists?

> Thanks

> (reply to newgroup and author please)

> Ken

function uDeleteTable(strTable as string)
dim db as database
dim tdf as tabledef

set db = currentdb

for each tdf in db.tabledefs
        if tdf.name = strTable
                docmd.deleteobject acTable, tdf.nam
        end if
next
end function



Tue, 13 Feb 2001 03:00:00 GMT  
 Testing for table existence

Quote:
>function uDeleteTable(strTable as string)
>dim db as database
>dim tdf as tabledef

>set db = currentdb

>for each tdf in db.tabledefs
> if tdf.name = strTable
> docmd.deleteobject acTable, tdf.nam
> end if
>next
>end function

I have done something very similar in trying to delete a table in the
back-end from the front-end, but can't get it to work. Any idea why I get a
run-time error 3011 at the DoCmd.DeleteObject line? I have confirmed that
the table does exist in my back-end, I have used find and replace to ensure
all table names are spelled correctly, and the path defined when setting
dbTest is correct.
---
Run-time Error 3011: The Microsoft Jet database engine could not find the
object 'TblTestLink'. Make sure the object exists and that you spell its
name and path name correctly.
---
Thanks,
Jeff
============================
Public Function DeleteTblTestLink()
  Dim wrkTest As Workspace
  Dim dbTest As Database
  Dim tdTest As TableDef
  Dim rstTest As Recordset
  Dim blnExist As Boolean

    Set wrkTest = DBEngine.Workspaces(0)
    Set dbTest = wrkTest.OpenDatabase("c:\My Documents\Office97\Data97.mdb",
True, False)

    blnExist = False

      For Each tdTest In dbTest.TableDefs
        Debug.Print tdTest.NAME
        If tdTest.NAME = "TblTestLink" Then
          blnExist = True
        End If
      Next tdTest

    If blnExist = False Then
      MsgBox "TblTestLink does not exist", vbOKOnly, "TblTestLink Status"
    Else
      DoCmd.DeleteObject acTable, "TblTestLink"     '''***Run-time Error
3011 Here!!!
        dbTest.TableDefs.Refresh
    End If
End Function



Tue, 13 Feb 2001 03:00:00 GMT  
 Testing for table existence
If your backend is SQL Server, you should be able to execute a pass-through
query that drops the table object.  Similar options should work on Oracle,
etc.
HTH, Keith
______________________________________
Keith Sopher, MCSD
The Arlington Group, www.arlingtongroup.com



Wed, 14 Feb 2001 03:00:00 GMT  
 Testing for table existence
The backend is another Access-97 database.

Jeff

Quote:

>If your backend is SQL Server, you should be able to execute a pass-through
>query that drops the table object.  Similar options should work on Oracle,
>etc.
>HTH, Keith
>______________________________________
>Keith Sopher, MCSD
>The Arlington Group, www.arlingtongroup.com




Wed, 14 Feb 2001 03:00:00 GMT  
 Testing for table existence
Then you'll need to open up the database through code (either in the same
workspace or another) and delete the table from the tabledefs collection.
If it doesn't already exist you'll get an error (#3010?).

If you want to test for it first, then try to append the table to the
tabledefs collection.  If a tabledef already exists with the same name,
you'll also get an error that you can trap for.

______________________________________
Keith Sopher, MCSD
The Arlington Group, www.arlingtongroup.com



Wed, 14 Feb 2001 03:00:00 GMT  
 Testing for table existence
Hi Jeff,

Try deleting the remote table as

'********* Code Start **********
Sub sDeleteRemoteTable()
Dim db As Database
    Set db = DBEngine(0).OpenDatabase(CurrentDb.Name)
    With db
        .TableDefs.Refresh
        On Error Resume Next
        .TableDefs.Delete "Proc2"
        If Err Then
            Err.Clear
            MsgBox "Table Not present"
        Else
            MsgBox "Successful"
        End If
    End With
    db.TableDefs.Refresh
    Set db = Nothing
End Sub
'********* Code End *********

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

:The backend is another Access-97 database.
:
:Jeff
:
:
:
:>If your backend is SQL Server, you should be able to execute a
pass-through
:>query that drops the table object.  Similar options should work on Oracle,
:>etc.
:>HTH, Keith
:>______________________________________
:>Keith Sopher, MCSD
:>The Arlington Group, www.arlingtongroup.com

:>
:>
:>
:>
:
:



Wed, 14 Feb 2001 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Testing for table existence & listing available tables

2. Test for table existence

3. Testing for table existence

4. Testing for table existence

5. Testing for table existence

6. Testing for Existence of Table

7. Test for the existence of a table

8. Testing the existence of a table

9. TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST

10. Testing for existence of a query

11. Testing for a record already in existence

12. test for existence of a field

 

 
Powered by phpBB® Forum Software