How do I varify one table existing in the ACCESS database ?

[Ken]

In my VB application I used ADO object to connect to ACCESS database. I want
to verify whether one table is in ACCESS database. How do I implement code
to verify it? Thanks!

[Douglas J. Steele]

http://www.able-consulting.com/ADOX_Faq.htm#Q6 at Carl Prothman's site.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


"Ken" <kzhao@lighthouse.org> wrote in message news:3d83ded8$1@10.1.10.29...
>
> In my VB application I used ADO object to connect to ACCESS database. I
want
> to verify whether one table is in ACCESS database. How do I implement code
> to verify it? Thanks!

[Roy]

You can also use DAO's TableDefs collection to do this.

Roy

"Ken" <kzhao@lighthouse.org> wrote in message news:3d83ded8$1@10.1.10.29...
>
> In my VB application I used ADO object to connect to ACCESS database. I want
> to verify whether one table is in ACCESS database. How do I implement code
> to verify it? Thanks!

[michael]

Using ADO you could you the ADOX object however this will have to be distruted
in the setup routine. The other alternative is to catch the error.

michael

Private Function TableExists(TableName As String) As Boolean
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Const ERR_TABLE_NOT_EXISTS As Long = -2147217865
On Error GoTo LocalError

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\TWI\TWIData.mdb;Persist
Security Info=False"
If cn.State > 0 Then
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from " & TableName & " where 0=1", cn, adOpenStatic,
adLockReadOnly, adCmdText
Set rs.ActiveConnection = Nothing
rs.Close
cn.Close
End If
TableExists = True

CleanUp:
Set rs = Nothing
Set cn = Nothing

LocalError:
If Err.Number = ERR_TABLE_NOT_EXISTS Then
'TableExists=False
Else
'Message other errors
MsgBox Err.Number & " - " & Err.Description
End If

Resume CleanUp
End Function

"Ken" <kzhao@lighthouse.org> wrote:
>
>In my VB application I used ADO object to connect to ACCESS database. I
want
>to verify whether one table is in ACCESS database. How do I implement code
>to verify it? Thanks!

[Roy]

I truly believe that when working against MS Access DAO is better choice simply because it's "natural" to Access's Jet engine. So
consider doing this:

Private Sub Form_Load()
'===================
Dim daoDB As Database
Dim i%, TblExist As Boolean

Set daoDB = OpenDatabase("c:\my documents\biblio.mdb")
TblExist = False
For i = 0 To daoDB.TableDefs.Count - 1
If daoDB.TableDefs(i).Name = "Authors" Then
TblExist = True
End If
Next i
If TblExist = True Then
'some code goes here
End If

End Sub

Note: some error handler must be in order

Roy

"michael" <mmayfield_NOSPAM@houston.rr.com> wrote in message news:3d85d627$1@10.1.10.29...
>
> Using ADO you could you the ADOX object however this will have to be distruted
> in the setup routine. The other alternative is to catch the error.
>
> michael
>
> Private Function TableExists(TableName As String) As Boolean
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Const ERR_TABLE_NOT_EXISTS As Long = -2147217865
> On Error GoTo LocalError
>
> Set cn = New ADODB.Connection
> cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\TWI\TWIData.mdb;Persist
> Security Info=False"
> If cn.State > 0 Then
> Set rs = New ADODB.Recordset
> rs.CursorLocation = adUseClient
> rs.Open "Select * from " & TableName & " where 0=1", cn, adOpenStatic,
> adLockReadOnly, adCmdText
> Set rs.ActiveConnection = Nothing
> rs.Close
> cn.Close
> End If
> TableExists = True
>
> CleanUp:
> Set rs = Nothing
> Set cn = Nothing
>
> LocalError:
> If Err.Number = ERR_TABLE_NOT_EXISTS Then
> 'TableExists=False
> Else
> 'Message other errors
> MsgBox Err.Number & " - " & Err.Description
> End If
>
> Resume CleanUp
> End Function
>
> "Ken" <kzhao@lighthouse.org> wrote:
> >
> >In my VB application I used ADO object to connect to ACCESS database. I
> want
> >to verify whether one table is in ACCESS database. How do I implement code
> >to verify it? Thanks!
>

[George Shubin]

Ken:

Here's a quick little routine I use:

Function IsTable(sTableName As String) As Boolean
'Determines if a data table exists in a database
Dim oDb As ADODB.Connection
Dim oRs As ADODB.Recordset
Set oDb = New ADODB.Connection
oDb.CursorLocation = adUseServer
oDb.Open GetProvider(gsDriver, gsDataPath, gsDatabase, False) '<--- my
function to provide a connection string
Set oRs = oDb.OpenSchema(adSchemaTables, Array(Empty, Empty, sTableName,
"TABLE"))
If Not oRs.EOF Then
IsTable = True
Else
IsTable = False
End If
oRs.Close
Set oRs = Nothing
oDb.Close
Set oDb = Nothing
End Function


--
------------------------------------------------------------------------
George Shubin Custom Software Development
dX Software Systems Database Applications
Ph: 503-981-6806 Fax: 503-982-0120
www.dxonline.com george@dxonline.com
------------------------------------------------------------------------


"Ken" <kzhao@lighthouse.org> wrote in message news:3d83ded8$1@10.1.10.29...
>
> In my VB application I used ADO object to connect to ACCESS database. I
want
> to verify whether one table is in ACCESS database. How do I implement code
> to verify it? Thanks!