Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Table exists in .MDB using VB.NET?

Posted on 2003-11-12
5
Medium Priority
?
2,188 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 375 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

876 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