using wildcards in visual basic

[Guest]

Hello

Any assistance with this matter would be greatly appreciated.
I am currently using a 'DeleteObject' command to delete a table in my access
database. The table name has a prefix which changes slightly, and therefore
I am trying to use a wildcard in my visual basic to refer to the table. Eg.
the table name changes from "001Table" to "002Table", and I have used a
number of variations of wildcards such as table name = Like "*"&"Table", but
have not been able to get this to work.
Is there any way that I can use a wildcard in my visual basic code to refer
to the table name? Once again, any advice/suggestions would be extremely
appreciated

Thanking you in advance
AC

 

[Douglas J. Steele]

If you're looking to delete all tables that end in Table, you can use code
like:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim lngLoop As Long

Set dbCurr = CurrentDb()
For lngLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
Set lngLoop = dbCurr.TableDefs(lngLoop)
If (lngLoop.Attributes And dbSystemObject) = 0 Then
If Right(tdfCurr.Name, 5) = "Table" Then
dbCurr.TableDefs.Delete tdfCurr.Name
End If
End If
Next lngLoop

 

[Guest]

Hi Doug

Thanks very much for your response. Unfortunately, I am getting a comile
error: Object required. It highligts the following line in the code:
Set lngLoop = dbCurr.TableDefs(lngLoop)
I have tried a few things, but due to limited visual basic knowledge, I have
not been able to rectify the problem.
Would you be able to assist?

Thank you
AC

 

[Douglas J. Steele]

Sorry. Stupid typo. That line should be

Set tdfCurr = dbCurr.TableDefs(lngLoop)

The next line is also incorrect. It should be

If (tdfCurr.Attributes And dbSystemObject) = 0 Then


Sorry about that.

 

[Guest]

Thanks very much Doug...your assistance is greatly appreciated! The code now
works wonderfully.

Kind regards
AC

 

[ac512]

Hello Douglas

A couple of months ago, you helped me with this enquiry.
Your recommendation was extremely helpful, and has been working wonderfully
until recently. For some reason, I am now receiving the following error
message:
"Compile Error: User-defined type not defined".
The following line in the code is then highlighted:
"dbCurr As DAO.database"

Is there any reason that you might know of as to why this is now occurring?
I am sorry to trouble you with this issue again - any help would be greatly
appreciated.

Kind regards
AC

 

[John W. Vinson]

Hello Douglas

A couple of months ago, you helped me with this enquiry.
Your recommendation was extremely helpful, and has been working wonderfully
until recently. For some reason, I am now receiving the following error
message:
"Compile Error: User-defined type not defined".
The following line in the code is then highlighted:
"dbCurr As DAO.database"

PMFJI but this is a pretty easy and common error. Open the VBA editor; select
Tools... References from the menu. Scroll down and find the unchecked checkbox
by

Microsoft DAO 3.6 Object Library

or the highest version of DAO that's there, and check it.

Close the References window and select Debug... Compile <my database>. Should
fix it.

John W. Vinson [MVP]

 

[ac512]

That works wonderfully - thank you very much for your assistance John. Much
appreciated.

Kind regards
AC