Solved

Table exists in .MDB using VB.NET?

Posted on 2003-11-12
5
2,165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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