View Full Version : Solved: Test if tables exist
Hi,
Is it possible to check if a table exists and if so delete it with vba? I have looked at the IsLoaded property of the accessobject, but can't figure out how to implement it.
Thank-you for any help.
matthewspatrick
05-29-2008, 02:06 PM
Make sure you have a reference to DAO...
Function DeleteTable(TableName As String)
' returns:
' 0 = table not found
' 1 = table deleted
' 2 = table found but not deleted
Dim td As DAO.TableDef
Dim TableExists As Boolean
Dim DidDelete As Boolean
On Error Resume Next
Set td = CurrentDb.TableDefs(TableName)
If Err = 0 Then
TableExists = True
Else
Err.Clear
TableExists = False
DeleteTable = 0
Exit Function
End If
td.Delete
DidDelete = (Err = 0)
If DidDelete Then DeleteTable = 1 Else DeleteTable = 2
End Function
Patrick,
Thank-you very much for the code, it's very much appreciated. I will give it a go at work tomorrow.
Regards,
Patrick,
I tried the function today at work, but encountered a couple of problems. I could only find a reference to Microsoft Data Access components, which I checked.
Then when I called the function from the OnOpen event of my form, I received an error.... The expression you entered has a function containing the wrong number of arguments.
Any ideas what I may have done wrong or missed?
Thanks
matthewspatrick
05-30-2008, 12:57 PM
Sorry, try Microsoft DAO (my most recent version is 3.6).
Right I now have Microsoft DAO 3.6 installed and I still receive the error.
Thanks
DarkSprout
06-02-2008, 07:06 AM
The above is a little messy, try using:
BooleanResult = DCount("*", "MSysObjects", "[Type] = 1 AND [Name] = '" & TableName & "'") > 0
And wrapped in a function, which can delete:=
Public Function TableExists(ByVal strTable As String, Optional DeleteIfFound As Boolean= False) As Integer
TableExists = DCount("*", "MSysObjects", "[Type] = 1 AND [Name] = '" & strTable & "'") > 0
If TableExists And DeleteIfFound Then
DoCmd.RunSQL "DROP TABLE [" & strTable & "]; "
End If
End Function
Darksprout,
Thank-you for the code works flawlessly. Can I place the function in a module and call it from my form (vba)?
Thanks again.
DarkSprout
06-05-2008, 06:22 AM
Yup!
:friends:
Just like to say thank-you to Patrick and Darksprout for your help. I now have this set up to run whenever the form is opened.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.