Solved

Table exists in .MDB using VB.NET?

Posted on 2003-11-12
5
2,139 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:cathG2000
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:infin8
ID: 9737016
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 9739192
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
 

Author Comment

by:cathG2000
ID: 9740528
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
 
LVL 28

Accepted Solution

by:
iboutchkine earned 125 total points
ID: 9740795
>>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
 

Author Comment

by:cathG2000
ID: 9742746
Thanks!  That does the job :)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question