Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I tested various methods for finding out if a table exists several years ago. Here is the code for all of them as I implemented, including my simple test routine.</p> <pre><code>Public Function TableExists(strTableName As String, Optional ysnRefresh As Boolean, Optional db As DAO.Database) As Boolean ' Originally Based on Tony Toews function in TempTables.MDB, http://www.granite.ab.ca/access/temptables.htm ' Based on testing, when passed an existing database variable, this is the fastest On Error GoTo errHandler Dim tdf As DAO.TableDef If db Is Nothing Then Set db = CurrentDb() If ysnRefresh Then db.TableDefs.Refresh Set tdf = db(strTableName) TableExists = True exitRoutine: Set tdf = Nothing Exit Function errHandler: Select Case Err.Number Case 3265 TableExists = False Case Else MsgBox Err.Number &amp; ": " &amp; Err.Description, vbCritical, "Error in mdlBackup.TableExists()" End Select Resume exitRoutine End Function Public Function TableExists2(strTableName As String, Optional ysnRefresh As Boolean, Optional db As DAO.Database) As Boolean On Error GoTo errHandler Dim bolCleanupDB As Boolean Dim tdf As DAO.TableDef If db Is Nothing Then Set db = CurrentDb() bolCleanupDB = True End If If ysnRefresh Then db.TableDefs.Refresh For Each tdf In db.TableDefs If tdf.name = strTableName Then TableExists2 = True Exit For End If Next tdf exitRoutine: Set tdf = Nothing If bolCleanupDB Then Set db = Nothing End If Exit Function errHandler: MsgBox Err.Number &amp; ": " &amp; Err.Description, vbCritical, "Error in mdlBackup.TableExists1()" Resume exitRoutine End Function Public Function TableExists3(strTableName As String, _ Optional db As DAO.Database) As Boolean ' Based on testing, when NOT passed an existing database variable, this is the fastest On Error GoTo errHandler Dim strSQL As String Dim rs As DAO.Recordset If db Is Nothing Then Set db = CurrentDb() strSQL = "SELECT MSysObjects.Name FROM MSysObjects " strSQL = strSQL &amp; "WHERE MSysObjects.Name=" &amp; Chr(34) &amp; strTableName &amp; Chr(34) strSQL = strSQL &amp; " AND MSysObjects.Type=6;" Set rs = db.OpenRecordset(strSQL) TableExists3 = (rs.RecordCount &lt;&gt; 0) exitRoutine: If Not (rs Is Nothing) Then rs.Close Set rs = Nothing End If Exit Function errHandler: MsgBox Err.Number &amp; ": " &amp; Err.Description, vbCritical, _ "Error in TableExists1()" Resume exitRoutine End Function Public Sub TestTableExists(strTableName As String, intLoopCount As Integer) Dim dteStart As Date Dim i As Integer Dim bolResults As Boolean dteStart = Now() For i = 0 To intLoopCount bolResults = TableExists(strTableName, , CurrentDB()) Next i Debug.Print "TableExists (" &amp; intLoopCount &amp; "): " &amp; Format(Now() - dteStart, "nn:ss") dteStart = Now() For i = 0 To intLoopCount bolResults = TableExists2(strTableName, , CurrentDB()) Next i Debug.Print "TableExists2 (" &amp; intLoopCount &amp; "): " &amp; Format(Now() - dteStart, "nn:ss") dteStart = Now() For i = 0 To intLoopCount bolResults = TableExists3(strTableName, CurrentDB()) Next i Debug.Print "TableExists3 (" &amp; intLoopCount &amp; "): " &amp; Format(Now() - dteStart, "nn:ss") End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCheck if access table exists
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDavid-W-Fenton
    1. USDavid-W-Fenton
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload