Solved

Table exists in .MDB using VB.NET?

Posted on 2003-11-12
5
2,128 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now