How to check if a table exists in Access XP

  • Thread starter Thread starter Anthony Bowman
  • Start date Start date
A

Anthony Bowman

Hello,
I have a access application that resides locally on users desk, I
am writing a VB.Net interface that let's the users pull data down from
a SQL server database and populate the local access database with that
data, I need to check the local access database to see if there is
already a table with the name the users give. Anyone run into that or
have any idea how this would be done.

Thanks
Anthony
(e-mail address removed)
 
if u go to tools and option..and check to show the system tables, few more
tables will appear in your table list..this are the system tables.

one among this table stores all the name of the objects(including tables)..
u can query this table to check the existance of table.

Regards,
NetPointer
 
On 3 Nov 2003 14:09:24 -0800, (e-mail address removed) (Anthony Bowman) wrote:

¤ Hello,
¤ I have a access application that resides locally on users desk, I
¤ am writing a VB.Net interface that let's the users pull data down from
¤ a SQL server database and populate the local access database with that
¤ data, I need to check the local access database to see if there is
¤ already a table with the name the users give. Anyone run into that or
¤ have any idea how this would be done.
¤

Yes, you can use GetOleDbSchemaTable:

Dim AccessConnection As New System.Data.OleDb.OleDbConnection()
Dim SchemaTable As DataTable

AccessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"
AccessConnection.Open()

'Retrieve schema information about Table1.
SchemaTable =
AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "Table1"})

If SchemaTable.Rows.Count <> 0 Then
Console.WriteLine("Table " & SchemaTable.Rows(0)!TABLE_NAME.ToString & " Exists")
Else
Console.WriteLine("Table does not exist")
End If

AccessConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul Clement said:
On 3 Nov 2003 14:09:24 -0800, (e-mail address removed) (Anthony Bowman) wrote:

¤ Hello,
¤ I have a access application that resides locally on users desk, I
¤ am writing a VB.Net interface that let's the users pull data down from
¤ a SQL server database and populate the local access database with that
¤ data, I need to check the local access database to see if there is
¤ already a table with the name the users give. Anyone run into that or
¤ have any idea how this would be done.
¤

Yes, you can use GetOleDbSchemaTable:

Dim AccessConnection As New System.Data.OleDb.OleDbConnection()
Dim SchemaTable As DataTable

AccessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"
AccessConnection.Open()

'Retrieve schema information about Table1.
SchemaTable =
AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "Table1"})

If SchemaTable.Rows.Count <> 0 Then
Console.WriteLine("Table " & SchemaTable.Rows(0)!TABLE_NAME.ToString & " Exists")
Else
Console.WriteLine("Table does not exist")
End If

AccessConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...



Great - Both of the suggestions worked wonders and have fixed my
problem. I appreciate the help.

Thanks
Anthony
 
Back
Top