Displaying a Table Name

[philljp390]

Hope someone can help with this one....

I want a form to open up and display a list of tables that exist from a pre-defined list. For example:

Tables:

Customers
Orders
Products
Couriers

Pre-defined list:

Customers
Products
Businesses
Contacts

So as the Customers and Products tables exist, I want the form to display a message saying:

"The following tables still exist, please delete them before proceeding

Customers
Products"

Is this possible?

 

[ghudson]

Sounds like your users have access to the db objects? If so I advise against that for the users should not be able to modify db objects.

You should delete the tables for the users if they exists. This function will delete all tables that have a suffix of "_X" like Customers_X.

Public Function DeleteTables()
On Error GoTo Err_DeleteTables
    
    Dim tblDef As TableDef
    
    For Each tblDef In CurrentDb.TableDefs
    If InStr(1, tblDef.Name, "_X") > 0 Then
        DoCmd.DeleteObject acTable, tblDef.Name
    End If
    Next tblDef
    
'    msgbox "done"
    
Exit_DeleteTables:
    Exit Function
    
Err_DeleteTables:
    msgbox Err.Number & " - " & Err.Description
    Resume Exit_DeleteTables
    
End Function

You could modify this to use a table and compare the records in the table and delete the tables if the table name is in the list. If that is your route you will have to figure that one out since looping record sets is one of my many weaknesses.

 

[KenHigg]

Sounds like your users have access to the db objects? If so I advise against that for the users should not be able to modify db objects.

Heed this advice.

kh

 

[philljp390]

Thanks, this wouldn't be for "users" as such, just to automate the process.

Would something like this work:

Public Function DeleteTables()
On Error GoTo Err_DeleteTables
    
    Dim tblDef As TableDef
    
    For Each tblDef In CurrentDb.TableDefs
    If InStr(1, tblDef.Name, "Customers" or "Products" or "Businesses" or "Contacts") > 0 Then
        DoCmd.DeleteObject acTable, tblDef.Name
    End If
    Next tblDef
    
'    msgbox "done"
    
Exit_DeleteTables:
    Exit Function
    
Err_DeleteTables:
    msgbox Err.Number & " - " & Err.Description
    Resume Exit_DeleteTables
    
End Function

As the pre-defined list is 'set in stone' if you see what i mean.

 

[philljp390]

I pasted the code under a button and im getting:

"User-defined type not defined"

tblDef As TableDef

TableDef doesn't exist in my list of variable types

 

[philljp390]

ok, solved teh Tabledefs part, just needed to tick Microsfot DAO 3.6 in the references.

Any ideas on the code part above?

 

[philljp390]

ok, solved by doing:

If InStr(1, tblDef.Name, "Customers") >0 or InStr(1, tblDef.Name,"Products") >0 or InStr(1, tblDef.Name,"Businesses")>0 or InStr(1, tblDef.Name,"Contacts") >0 Then

Probably not the best way to do it, but it works

 

[ghudson]

You should incorporate naming conventions into your db.

Like... tblCustomers, tblProducts...