Detect Table

  • Thread starter Thread starter box2003
  • Start date Start date
B

box2003

I have a form which is used for import and export functions. As part of
this process, I want to check if a table already exists in the database, so
I can create the table or clear the table rows before my import. I need a
method of detecting the presence of a table before an error message tells me
a table does not exist when I run a process. When the table is known not to
exist, I create it using code behind the form. I would like to determine if
the table is actually there before going ahead a creating it. Would I use
similar code as was used to created the table. I do not want to check the
database window to verify the table, I would rather have the process
seamless so the user can maintain interaction at the form level.

Thank you for your assistance.
 
box2003 said:
I have a form which is used for import and export functions. As part
of this process, I want to check if a table already exists in the
database, so I can create the table or clear the table rows before my
import. I need a method of detecting the presence of a table before
an error message tells me a table does not exist when I run a
process. When the table is known not to exist, I create it using
code behind the form. I would like to determine if the table is
actually there before going ahead a creating it. Would I use similar
code as was used to created the table. I do not want to check the
database window to verify the table, I would rather have the process
seamless so the user can maintain interaction at the form level.

Thank you for your assistance.
Click to expand...

There are lots of ways to do this. Here's one:

'----- start of code -----
Function fncTableExists( _
TableName As String, _
Optional DBPath As String) _
As Boolean

Dim DB As DAO.Database

If Len(DBPath) > 0 Then
Set DB = DBEngine.OpenDatabase(DBPath)
Else
Set DB = CurrentDb
End If

If Len(TableName) = 0 Then Err.Raise 5

On Error Resume Next
fncTableExists = IsObject(DB.TableDefs(TableName))

If Not DB Is Nothing Then
DB.Close
Set DB = Nothing
End If

End Function

'----- end of code -----

The above function requires that you have a reference set to the
Microsoft DAO 3.x Object Library -- 3.6 for Access 2000 or later, 3.51
for Access 97. To check for the existence of a table in the current
database, you would call it like this:

If fncTableExists("SomeTableName") Then
' the table exists
Else
' it doesn't
End If
 
Public Function funcTableExists(strTable As String) As Boolean
On Error GoTo ErrorPoint

' This function will check to see if a
' table exists within the current database
' Similar to IsLoaded function it will return True or False
' Jeff Conrad - Access Junkie
' Usage:
' If funcTableExists("SomeTable") = True Then
' ' Table Exists
' Else
' ' Table does not exist
' End If
' **Requires DAO Reference be set**

Dim db As DAO.Database
Dim doc As DAO.Document

Set db = CurrentDb()

With db.Containers!Tables
For Each doc In .Documents
If doc.Name = strTable Then
funcTableExists = True
End If
Next doc
End With

ExitPoint:
On Error Resume Next
Set db = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Function
 
Jeff Conrad said:
Public Function funcTableExists(strTable As String) As Boolean
On Error GoTo ErrorPoint

' This function will check to see if a
' table exists within the current database
' Similar to IsLoaded function it will return True or False
' Jeff Conrad - Access Junkie
' Usage:
' If funcTableExists("SomeTable") = True Then
' ' Table Exists
' Else
' ' Table does not exist
' End If
' **Requires DAO Reference be set**

Dim db As DAO.Database
Dim doc As DAO.Document

Set db = CurrentDb()

With db.Containers!Tables
For Each doc In .Documents
If doc.Name = strTable Then
funcTableExists = True
End If
Next doc
End With

ExitPoint:
On Error Resume Next
Set db = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Function
Click to expand...

Jeff -

If you find the table, you may want to go ahead and leave the loop:

For Each doc In .Documents
If doc.Name = strTable Then
funcTableExists = True
Exit For
End If
Next doc
 
Jeff -

If you find the table, you may want to go ahead and leave the loop:

For Each doc In .Documents
If doc.Name = strTable Then
funcTableExists = True
Exit For
End If
Next doc
Click to expand...

Hi Yoda,

Yeah, I realize the loop need not continue.
With a large database this could save a little time, but I'm usually working
with databases that do not have a whole bunch of tables.
Also, I just did not want the other tables to feel left out.
;-)

Thanks, and I will change the code.
 
Jeff Conrad said:
Hi Yoda,

Yeah, I realize the loop need not continue.
With a large database this could save a little time, but I'm usually working
with databases that do not have a whole bunch of tables.
Also, I just did not want the other tables to feel left out.
;-)

Thanks, and I will change the code.
--
Click to expand...

What? You mean I should be taking the emotions of my objects into account?
Should I be using...

DoCmd.DeleteObject acTable, "TableName", acMyApologiesForDoingThis

and changing

DoCmd.RunSQL "Delete from tbl Where Obsolete"

to

DoCmd.RunSQL "Delete from tbl Where
StillAReallyGoodRecordButNeedsToBeInADifferentTableToReallyFulfillYourPotent
ial"

Should we start a new newsgroup for ethical coding?
 
Rob Oldfield said:
What? You mean I should be taking the emotions of my objects into
account? Should I be using...

DoCmd.DeleteObject acTable, "TableName", acMyApologiesForDoingThis

and changing

DoCmd.RunSQL "Delete from tbl Where Obsolete"

to

DoCmd.RunSQL "Delete from tbl Where
StillAReallyGoodRecordButNeedsToBeInADifferentTableToReallyFulfillYourPo
tent
ial"

Should we start a new newsgroup for ethical coding?
Click to expand...

ROFL

"Never anthropomorphize conputers. They HATE that!"
 
What? You mean I should be taking the emotions of my objects into account?
Should I be using...

DoCmd.DeleteObject acTable, "TableName", acMyApologiesForDoingThis

and changing

DoCmd.RunSQL "Delete from tbl Where Obsolete"

to

DoCmd.RunSQL "Delete from tbl Where
StillAReallyGoodRecordButNeedsToBeInADifferentTableToReallyFulfillYourPotent
ial"

Should we start a new newsgroup for ethical coding?
Click to expand...

ROFL!!
Yes, precisely.

Remember the Junkie motto:
"Be nice to thine objects, or fail they will when showing
the app to prospective clients."
 
Back
Top