How to check if a table exists in MS Access for vb macros

Question

Possible Duplicate    Check if access table exists       I m new to vba macros  Any idea how to check if a table exists or not  I have searched for previous posts but did not get a clear solution for this

User · Answer

Exists   IsObject CurrentDb TableDefs tablename

User · Answer

I know the question is already answered  but I find that the existing answers are not valid  they will return True for linked tables with a non working back-end  Using DCount can be much slower  but is more reliable     Function IsTable sTblName As String  As Boolean      does table exists and work        note  finding the name in the TableDefs collection is not enough             since the backend might be invalid or missing      On Error GoTo hell     Dim x     x   DCount      sTblName      IsTable   True     Exit Function hell      Debug Print Now  sTblName  Err Number  Err Description     IsTable   False  End Function

User · Answer

This is not a new question  I addresed it in comments in one SO post  and posted my alternative implementations in another post  The comments in the first post actually elucidate the performance differences between the different implementations   Basically  which works fastest depends on what database object you use with it

User · Answer

Access has some sort of system tables You can read about it a little here you can fire the folowing query to see if it exists   1   it exists  0   it doesnt      SELECT Count  MSysObjects   Name   AS  Count  FROM MSysObjects WHERE    MSysObjects Name   TblObject   AND   MSysObjects Type  1

User · Answer

Setting a reference to the Microsoft Access 12 0 Object Library allows us to test if a table exists using DCount   Public Function ifTableExists tblName As String  As Boolean      If DCount   Name     MSysObjects     Name        amp  tblName  amp         1 Then          ifTableExists   True      End If  End Function

[ms-access] How to check if a table exists in MS Access for vb macros

Examples related to ms-access

Examples related to vba