By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,379 Members | 3,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,379 IT Pros & Developers. It's quick & easy.

Delete Table

P: n/a
Hello!

I wanna import a table with a makro, but a table with this name already
exists. So the imported table (f. i. "table") is saved as "table1". How can
I check, if such a table already exists and then delete it?
So how can I check the existence of a table?

________________________________________
Function Importieren1()
On Error GoTo Importieren1_Err

DoCmd.DeleteObject acTable, "schueler"
DoCmd.RunCommand acCmdImport
Importieren1_Exit:
Exit Function

Importieren1_Err:
MsgBox Error$
Resume Importieren1_Exit

End Function
________________________________________

Yours Jürgen
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
pw
>Hello!

I wanna import a table with a makro, but a table with this name already
exists. So the imported table (f. i. "table") is saved as "table1". How can
I check, if such a table already exists and then delete it?
So how can I check the existence of a table?

________________________________________
Function Importieren1()
On Error GoTo Importieren1_Err

DoCmd.DeleteObject acTable, "schueler"
DoCmd.RunCommand acCmdImport
Importieren1_Exit:
Exit Function

Importieren1_Err:
MsgBox Error$
Resume Importieren1_Exit

End Function
________________________________________

Yours Jürgen


Not sure how to check if a table exists, but you can use the DROP
command (check the help file) to delete an Access table:

dbCurrDb.Execute "DROP TABLE tblResCloneWrkf;"
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)
----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
Nov 12 '05 #2

P: n/a
Hi Micheal,

This is from the Microsoft Knowledge Base:
Copy and paste this code into a new module (in the database window)

-----------------------------------------------------

'INF: How to Determine If a Table or Query Exists
'Article id: Q113549
'Copyright (c) Microsoft Corporation. All rights reserved.
'************************************************* *******
' FUNCTION: IsTableQuery()
'
' PURPOSE: Determine if a table or query exists.
'
' ARGUMENTS:
' DbName: The name of the database. If the database name
' is "" the current database is used.
' TName: The name of a table or query.
'
' RETURNS: True (it exists) or False (it does not exist).
'
'************************************************* *******

Function IsTableQuery(DbName As String, TName As String) As Integer
Dim Db As Database, Found As Integer, Test As String
Const NAME_NOT_IN_COLLECTION = 3265

' Assume the table or query does not exist.
Found = False

' Trap for any errors.
On Error Resume Next

' If the database name is empty...
If Trim$(DbName) = "" Then
' ...then set Db to the current Db.
Set Db = CurrentDb()

Else
' Otherwise, set Db to the specified open database.
Set Db = DBEngine.Workspaces(0).OpenDatabase(DbName)

' See if an error occurred.
If Err Then
MsgBox "Could not find database to open: " & DbName
IsTableQuery = False
Exit Function
End If
End If

' See if the name is in the Tables collection.
Test = Db.TableDefs(TName).Name

If Err <> NAME_NOT_IN_COLLECTION Then Found = True

' Reset the error variable.
Err = 0

' See if the name is in the Queries collection.
Test = Db.QueryDefs(TName$).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True

Db.Close

IsTableQuery = Found

End Function
-----------------------------------------------------
Then use it like this:
If IsTableQuery("", "schueler") Then
DoCmd.DeleteObject acTable, "schueler"
End If

*******************************************
HTH,
Don
Michael Magg <ao***********@aon.at> wrote in message
news:40***********************@newsreader02.highwa y.telekom.at...
Hello!

I wanna import a table with a makro, but a table with this name already
exists. So the imported table (f. i. "table") is saved as "table1". How can I check, if such a table already exists and then delete it?
So how can I check the existence of a table?

________________________________________
Function Importieren1()
On Error GoTo Importieren1_Err

DoCmd.DeleteObject acTable, "schueler"
DoCmd.RunCommand acCmdImport
Importieren1_Exit:
Exit Function

Importieren1_Err:
MsgBox Error$
Resume Importieren1_Exit

End Function
________________________________________

Yours Jürgen

Nov 12 '05 #3

P: n/a
"Don Leverton" <My*****@Telus.Net> wrote in news:42fmc.2612$uN4.305
@clgrps12:
Hi Micheal,

This is from the Microsoft Knowledge Base:
Copy and paste this code into a new module (in the database window)
I'm sure it was a full chapter in War and Peace before that!

-----------------------------------------------------

'INF: How to Determine If a Table or Query Exists
'Article id: Q113549
'Copyright (c) Microsoft Corporation. All rights reserved.
'************************************************* *******
' FUNCTION: IsTableQuery()
'
' PURPOSE: Determine if a table or query exists.
'
' ARGUMENTS:
' DbName: The name of the database. If the database name
' is "" the current database is used.
' TName: The name of a table or query.
'
' RETURNS: True (it exists) or False (it does not exist).
'
'************************************************* *******

Function IsTableQuery(DbName As String, TName As String) As Integer
Dim Db As Database, Found As Integer, Test As String
Const NAME_NOT_IN_COLLECTION = 3265

' Assume the table or query does not exist.
Found = False

' Trap for any errors.
On Error Resume Next

' If the database name is empty...
If Trim$(DbName) = "" Then
' ...then set Db to the current Db.
Set Db = CurrentDb()

Else
' Otherwise, set Db to the specified open database.
Set Db = DBEngine.Workspaces(0).OpenDatabase(DbName)

' See if an error occurred.
If Err Then
MsgBox "Could not find database to open: " & DbName
IsTableQuery = False
Exit Function
End If
End If

' See if the name is in the Tables collection.
Test = Db.TableDefs(TName).Name

If Err <> NAME_NOT_IN_COLLECTION Then Found = True

' Reset the error variable.
Err = 0

' See if the name is in the Queries collection.
Test = Db.QueryDefs(TName$).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True

Db.Close

IsTableQuery = Found

End Function
-----------------------------------------------------
Then use it like this:
If IsTableQuery("", "schueler") Then
DoCmd.DeleteObject acTable, "schueler"
End If

*******************************************
HTH,
Don
Michael Magg <ao***********@aon.at> wrote in message
news:40***********************@newsreader02.highwa y.telekom.at...
Hello!

I wanna import a table with a makro, but a table with this name already
exists. So the imported table (f. i. "table") is saved as "table1". How

can
I check, if such a table already exists and then delete it?
So how can I check the existence of a table?

________________________________________
Function Importieren1()
On Error GoTo Importieren1_Err

DoCmd.DeleteObject acTable, "schueler"
DoCmd.RunCommand acCmdImport
Importieren1_Exit:
Exit Function

Importieren1_Err:
MsgBox Error$
Resume Importieren1_Exit

End Function
________________________________________

Yours Jürgen



--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.