Check for Existance of Table

[George1111]

VB6 with Access 2000 Database

I have had success previously in checking that a Table exists by running

eg,
On Error GoTo NOFILE

RsOrdADO.Open StrSql, Db, adOpenStatic, adLockReadOnly
MsgBox "File exists"
... do something ...

NOFILE:
MsgBox "File does not exist"

For some reason, it now wants to crash with an error telling me that the file does not exist
ie, It completely ignores the On Error GoTo NORECS statement

What is the correct way to check if a table exists ?

Thanks

[dglienna]

add this:

msgbox StrSql & " " & Db

and post the results

[George1111]

Just a simple select statement

"Select Account, Name from OrderFile"

You don't sleep either ? (Thanks)

[dglienna]

try [Name]

[George1111]

Here it is - This works



Call checking using


DatabaseName = "C:\Data\MyDB.mdb"
TableName = "MyTable"

If TableExists(DatabasePath, TableName) Then
MsgBox "EXISTS"
End If


Function called

Public Function TableExists(DatabaseName As String, _
TableName As String) As Boolean

'DataBaseName is the file/path name of the database
'with the field you want to test
'tablename is the table which you want to test
'if database doesn't exist, an error is raised

Dim oDB As Database, td As TableDef

On Error GoTo errorhandler
Set oDB = Workspaces(0).OpenDatabase(DatabaseName)
On Error Resume Next

Set td = oDB.TableDefs(TableName)
TableExists = Err.Number = 0
oDB.Close

exit function

errorhandler:

Err.Raise Err.Number
Exit Function

end function

[George1111]

I think the problem stems from the SQL Statement
(in my original question)


If I use

On Error GoTo S1
RsOrdADO.Open "Select * from OrdIndex", Db, adOpenDynamic

The On error does jump to S1 if the file does not exist - the key is saying "Select * from"

On the other hand, when using

On Error GoTo S1
RsOrdADO.Open "Select Code, Ord_value from OrdIndex", Db, adOpenDynamic


The system crashes as the query uses specific Field names rather than the generic *