"TableExists" in another database. 1

[lars7]

Hi,
I am exporting a backup of my main table to another database but I would like to check if a table with the same name exists there first. I have a module "TableExists" but this will only work within the main database. My plan is to attach the code to the "on open" of my switchboard but as this can be opened and closed many times a day I don't want to overwrite the table all the time. I have called the Table tbltest & date so the name will change every day. This all works fine in the main database but I don't want to store all the backups there.

Here is the code to export the table:

DoCmd.CopyObject "C:\Documents and Settings\tmcmeekin1\My Documents\db1.mdb", "tblTest" & Date, acTable, "trust staff"

and the module for checking for the table.

Public Function TableExists(TableName As String) As Boolean
' Checks for the existence of a query (named as QueryName)
' and returns true if query exists.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
TableExists = False
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = TableName Then
TableExists = True
Exit Function
End If
Next tdf
End Function

Any help or advise here would be appreciated.

 

[Golom]

Upgrade your "TableExists" function to

Public Function TableExists(TableName As String, _
       Optional DataBaseName As String = "") As Boolean
[COLOR=black cyan]
' Checks for the existence of a query (named as QueryName)
' and returns true if query exists.
[/color]
Dim db                          As DAO.Database
Dim tdf                         As DAO.TableDef
TableExists = False
If Len(DataBaseName) = 0 Then
    Set db = CurrentDb
Else
    Set db = DAO.DBEngine(0).OpenDatabase(DataBaseName)
End If
For Each tdf In db.TableDefs
    If tdf.Name = TableName Then
        TableExists = True
        Set db = Nothing
        Exit Function
    End If
Next tdf
Set db = Nothing
End Function

and call it with

TableExists "myTable", "C:\SomePath\OtherDB.mdb"

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lars7]

Hi Golom,
Thanks for responding.
This worked great.

Private Sub Command0_Click()

If TableExists("tblTest" & Date, "C:\Documents and Settings\tmcmeekin1\My Documents\test1.mdb") Then
Exit Sub
Else
DoCmd.CopyObject "C:\Documents and Settings\tmcmeekin1\My Documents\test1.mdb", "tblTest" & Date, acTable, "tbltruststaff"
End If

End Sub

and the module:

Option Compare Database
Public Function TableExists(TableName As String, _
Optional DataBaseName As String = "") As Boolean


Dim db As DAO.Database
Dim tdf As DAO.TableDef
TableExists = False
If Len(DataBaseName) = 0 Then
Set db = CurrentDb
Else
Set db = DAO.DBEngine(0).OpenDatabase(DataBaseName)
End If
For Each tdf In db.TableDefs
If tdf.Name = TableName Then
TableExists = True
Set db = Nothing
Exit Function
End If
Next tdf
Set db = Nothing
End Function

Once again thanks for the help.