Table exists in .MDB using VB.NET?

Hi,  I am using VB.NET and need to find out if a table exists before I create it in my .MDB (Access 2002) database.  I cannot use an "if exists" statement against an Access database, so need some other way to determine if the table already exists.  An alternative is to generate a list of tables that are in the database but I do not know how to do this either using VB.NET.

I am using the following:
        conn.Open()
        sqlSTR = "SELECT * INTO MatchedWords" & docCount.ToString & " FROM MatchedWords"
        Dim selectIntoCMD As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlSTR, conn)
        Dim recordsAffected As Int32
        Try
            recordsAffected = selectIntoCMD.ExecuteNonQuery

Any help appreciated,
Catherine
cathG2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

infin8Commented:
0
iboutchkineCommented:
Here is the code to list tables


Private Sub ListAccessTables()
        Dim sSQL As String = "SELECT [Name] FROM MSysObjects WHERE Type=1 And Left([Name],1)<>'~' And Left([Name],4)<>'MSys'"
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        Dim sConn As OleDbConnection

        'without .mdw database I could not get permission to read mSysObjects
        'I had to create System database TestDB.mdw
        msConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & fsDBName & ";Persist Security Info=False;" & _
                 "Jet OLEDB:System database=" & fsSystemDBName'Name of the System db (db.mdw)
        sConn = New OleDbConnection(msConn)
        da = New OleDbDataAdapter(sSQL, sConn)
        ds = New DataSet()
        sConn.Open()
        Try
            If GrantReadAccessToSysObjects() Then
                cmd = New OleDbCommand(sSQL)
                cmd.CommandTimeout = 0
                cmd.Connection = sConn
                dr = cmd.ExecuteReader
            Else
                MsgBox("Permission to read tables denied.")
            End If
        Catch e As Exception
            MsgBox(Err.Number & "  " & Err.Description)
            sConn.Close()
            Exit Sub
        End Try


        While (dr.Read())
            lstTables.Items.Add(CType(dr.GetValue(0), String))
        End While

        sConn.Close()
    End Sub
 
   Private Function GrantReadAccessToSysObjects()
        Dim bSuccess As Boolean = False
        Dim sConn As OleDbConnection = New OleDbConnection(msConn)

        sConn.Open()
        Try
            Dim SQL As String = "GRANT SELECT ON TABLE MSysObjects TO Admin"
            Dim myCommand As OleDbCommand = New OleDbCommand(SQL, sConn)
            myCommand.ExecuteNonQuery()
            bSuccess = True
        Catch e As OleDbException
            MsgBox(Err.Number & "  " & Err.Description)
        Finally
            sConn.Close()
        End Try
        Return bSuccess
    End Function
0
cathG2000Author Commented:
infini8>  The ADOX example seems straight forward enough, however, if there is a way to check that the table exists without having to add extra components (MDAC and references) to my PC, then I'd rather do that.

iboutchkine>   OK, I have some niggly little questions: Please answer no.2 if the other is too troublesome.
1.  I can run sSQL directly on my Access DB and it works, but I cannot run the SQL string directly on my Access DB (i.e. in an Access query).  Yet the SQL string works when called from the program - how does that work?
2.  Can you explain how the "GRANT SELECT ON TABLE MSysObjects TO Admin" statement works?  when I distribute this application, what changes will I have to make on the PC?  I am worried that this code will only work for Admin users and that won't be the type of user using the program.

It looks like a toss up between whether to install extra components to the PC or whether to mess with permissions.  Is there any other way?  I know you can view the table schema retrieved from a table in a dataset somehow.. is there a way to retrieve all table schema for the database?  I don't understand why such a simple task has become such a difficult thing to code in VB.Net.. it is very frustrating.
0
iboutchkineCommented:
>>1.  I can run sSQL directly on my Access DB and it works, but I cannot run the SQL string directly on my Access

I don't understand You can run or yoou cannot run?

>>2.  Can you explain how the "GRANT SELECT ON TABLE MSysObjects TO Admin" statement works?  when I distribute this application, what changes will I have to make on the PC?  I am worried that this code will only work for Admin users and that won't be the type of user using the program.

There is a hidden table MSYSObject in Access db. It contains all the Access objects. In order to run it  you have to have Admin rights

If your user id not admin you can use different code to list tables

Imports System.Data.OleDb

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
#End Region

    Dim sDBNAme As String = "c:\TestDB\TestDB.mdb"
    Dim cn As New OleDb.OleDbConnection()
    Dim sConn As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBNAme & ";Persist Security Info=False"
        cn = New OleDbConnection(sConn)
        cn.ConnectionString = sConn

        ' Open a connection
        cn.Open()

        'Call GetOleDbSchemaTable
        Dim schemaTable As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
        New Object() {Nothing, Nothing, Nothing, "TABLE"})

        Dim tableList As New DataTable("Table") 'Table
        Dim rowvals(0) As Object
        Dim newdc As New DataColumn("Col")
        tableList.Columns.Add(newdc)
        Dim rowcoll As DataRowCollection = tableList.Rows

        Dim counter As Integer

        For counter = 0 To schemaTable.Rows.Count - 1
            Dim rd As DataRow = schemaTable.Rows(counter)
            If rd("TABLE_TYPE").ToString = "TABLE" Then
                rowvals(0) = rd("TABLE_NAME").ToString
                rowcoll.Add(rowvals)
            End If
        Next

        ' Attach data row to the grid and close the connection
        dg.DataSource = tableList
        cn.Close()

    End Sub
End Class


that willl list all the tables to the grid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cathG2000Author Commented:
Thanks!  That does the job :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.