By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,161 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,161 IT Pros & Developers. It's quick & easy.

Check for existence of a table

P: n/a
RLN
Re: Access 2003

I have code to check to see if a table exists.
There are several other tables (tblQ12, tblQ13, etc) that do already
exist & this code runs fine; msgbox says the value returned is true
when the table already exists prior to the run of this module.

If the table does not exist, this line bombs
varTemp = dbsTemp.TableDefs(prmTable).Name
with this error:
Run time 3265 - "Item not found in this collection"

Here is the code:

<begin code>
Function TableExists_TSB(prmDatabase As String, prmTable As String) As
Boolean
' Returns : True - table exists, False - table does not exist
Dim dbsTemp As Database
Dim varTemp As Variant

On Error GoTo PROC_ERR

Set dbsTemp = CurrentDb()

On Error Resume Next
varTemp = dbsTemp.TableDefs(prmTable).Name
TableExists_TSB = (Err = 0)
MsgBox "Table " & prmTable & " : " & TableExists_TSB, vbOKOnly,
cstPgmName & " TEST-Code: TableExists_TSB"
dbsTemp.Close

PROC_EXIT:
Exit Function

PROC_ERR:
TableExists_TSB = False
Resume PROC_EXIT

End Function
<end code>
Here is how I use this function in the app:
<begin code>
blnOK = TableExists_TSB(" ", prmTableName)
If blnOK Then
'...table exists; delete old data in it so the new data will be
added
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from " & prmTableName
DoCmd.SetWarnings True
Else
'....blnOK is false / table does not exist so we'll create this
table in another module
End If
<end code>

Any ideas as to what is wrong with my function above?

Thanks.

Sep 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"On Error Resume Next" is the lazy way to deal with what could be a
serious problem.

What do you want it to do if the table DOESN'T exist? I suggest
writing code that traps Err.Number = '3265' and deal with it properly.

RLN wrote:
Re: Access 2003

I have code to check to see if a table exists.
There are several other tables (tblQ12, tblQ13, etc) that do already
exist & this code runs fine; msgbox says the value returned is true
when the table already exists prior to the run of this module.

If the table does not exist, this line bombs
varTemp = dbsTemp.TableDefs(prmTable).Name
with this error:
Run time 3265 - "Item not found in this collection"

Here is the code:

<begin code>
Function TableExists_TSB(prmDatabase As String, prmTable As String) As
Boolean
' Returns : True - table exists, False - table does not exist
Dim dbsTemp As Database
Dim varTemp As Variant

On Error GoTo PROC_ERR

Set dbsTemp = CurrentDb()

On Error Resume Next
varTemp = dbsTemp.TableDefs(prmTable).Name
TableExists_TSB = (Err = 0)
MsgBox "Table " & prmTable & " : " & TableExists_TSB, vbOKOnly,
cstPgmName & " TEST-Code: TableExists_TSB"
dbsTemp.Close

PROC_EXIT:
Exit Function

PROC_ERR:
TableExists_TSB = False
Resume PROC_EXIT

End Function
<end code>
Here is how I use this function in the app:
<begin code>
blnOK = TableExists_TSB(" ", prmTableName)
If blnOK Then
'...table exists; delete old data in it so the new data will be
added
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from " & prmTableName
DoCmd.SetWarnings True
Else
'....blnOK is false / table does not exist so we'll create this
table in another module
End If
<end code>

Any ideas as to what is wrong with my function above?

Thanks.
Sep 22 '06 #2

P: n/a
RLN wrote:
Re: Access 2003

I have code to check to see if a table exists.
There are several other tables (tblQ12, tblQ13, etc) that do already
exist & this code runs fine; msgbox says the value returned is true
when the table already exists prior to the run of this module.
As manning says, you need to deal with the error properly. As an
alternative approach, here's an uncommented version of the code I use to
check and see if a table exists. I think it's probably much better than
what you've got. I've never ever had a problem with it.

Function fTblExists(strName As String) As Boolean

Dim booFound As Boolean
Dim dbs As DAO.Database
Dim tbl1 As DAO.TableDef

On Error GoTo Err_Proc
booFound = False
Set dbs = Access.CurrentDb
For Each tbl1 In dbs.TableDefs
If tbl1.Name = strName Then
booFound = True
Exit For
End If
Next
fTblExists = booFound
Exit_Proc:
Set tbl1 = Nothing
dbs.Close
Set dbs = Nothing
Exit Function
Err_Proc:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & " " & _
Err.Description, vbCritical, "fTblExists", _
Err.HelpFile, Err.HelpContext
Resume Exit_Proc
End Select

End Function

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Sep 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.