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

Macro Conditions - Check if table name exists

P: 10
what should be the syntax of a macro condition if I want to check if the table name is already existing? I need this condition so that if the table name is existing, the macro will perform other task and avoid getting an error. For example, I have Table1 already and I run the macro w/o the condition, it will result to an error. I need the condition to skip actions that will produce the error.
any idea will help. thanks in advance!
Dec 18 '06 #1
Share this Question
Share on Google+
7 Replies


100+
P: 143
Try this function. I would try to steer clear of macros. Use them only when you have to.

Expand|Select|Wrap|Line Numbers
  1. Function TableExists(strTableName As String) As Integer
  2. 'Returns True if table exists
  3. Dim dbs As Database
  4. Dim i As Integer
  5. Set dbs = CurrentDb()
  6. TableExists = False
  7. dbs.TableDefs.Refresh
  8. For i = 0 To dbs.TableDefs.Count - 1
  9. If strTableName = dbs.TableDefs(i).Name Then
  10. 'Table Exists
  11. TableExists = True
  12. Exit For
  13. End If
  14. Next i
  15. DoCmd.DeleteObject acTable, strTableName
  16.  
  17. Set dbs = Nothing
  18. End Function
  19.  
Dec 18 '06 #2

NeoPa
Expert Mod 15k+
P: 31,602
Tanis makes a good point.
Try this slightly shorter/more straightforward function though.
Expand|Select|Wrap|Line Numbers
  1. Public Function TableExists(strTable As String) As Boolean
  2.     Dim strName As String
  3.  
  4.     On Error Resume Next
  5.     'If table exists already then strName will be > ""
  6.     strName = CurrentDb.TableDefs(strTable).Name
  7.     TableExists = Not (strName = "")
  8. End Function
Dec 19 '06 #3

P: 10
sad to say but I really have to do it using macro =(
Dec 20 '06 #4

P: 10
still needs help in macro conditions...thanks!
Dec 21 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
what should be the syntax of a macro condition if I want to check if the table name is already existing? I need this condition so that if the table name is existing, the macro will perform other task and avoid getting an error. For example, I have Table1 already and I run the macro w/o the condition, it will result to an error. I need the condition to skip actions that will produce the error.
any idea will help. thanks in advance!
I honestly don't know of a way to do this using a macro. One of the reasons VBA was designed is because macros are limited.

Mary
Dec 21 '06 #6

P: 10
ok...thanks mary!
Dec 21 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
ok...thanks mary!
Wish I could have helped more. If you decide you want to try VBA let us know and we'll do what we can to help.

Mary
Dec 21 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.