I want to populate a combobox with the name of the tables of one access database. I use the following statment

SELECT [Name] FROM MSysObjects WHERE [Type] = 1

I get stuck on the error Record(s) cannot be read; no read permission on 'MSysObjects'.

Any help?

Thank you

 

Hello,

According to me 'MSysObjects' is hidden table created it-self  by Access database. Users might not have permission to read this table.

Can't you use different table name?

Regards

Kuldeep Deokule

Hello

My access database name's english

How can write the sql statment for extract the name of all tables in that database?

Thank you  

Hello,

To get a list of table names use following code.

        Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\english.mdb"
        Dim olecon As New OleDbConnection(sConn)
        olecon.Open()
        Dim dt As DataTable = olecon.GetSchema("tables")
        olecon.Close()
        GridView1.DataSource = dt
        GridView1.DataBind()

I gridview you'll get list of tables.
I suggest don't use Msysobject. It creates problem related to access. ADOX and Msysobjects works fine with windows applications but may problematic for web apps.

Regards

Kuldeep Deokule

Works great

One more question: if i want to show all the tables except the system tables (TABLE_TYPE only Table). Any ideas?

It is possible to do it with a sql statment?

Thank you a lot...

Hello,

Ok..

Actually hint is in your last post itself (TABLE_TYPE only Table).

Here is a code, just change two lines in earlier code:

        Dim tblrestrictions As String() = New String() {Nothing, Nothing, Nothing, "TABLE"}
        Dim dt As DataTable = olecon.GetSchema("tables", tblrestrictions)

Note:
Restrictions are a string array in the following format:
{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}.

Regards

Kuldeep Deokule

Very good Kuldeep;  On quick question.  On the same lines how do one see Field names and their types? thanks in Advance

If you people are using AccessDataSource and having error "Record(s) cannot be read; no read permission on [table]", first step is to open up the MDB file and assign full rights to Admin user for each & every table either its system or hidden table.

if you want to see all system & hidden tables, go to Tools -> Options -> View Tab and enable all check boxes.

if you want to assign security to Admin (or any) user, go to Tools -> Security -> User and Group Permissions and assign rights for each table.

I'm currently using Visual Studio 2008 Beta2 (Orcas) & I felt to share this info with you people.

Moiz

MCSD.Net