Using ADO.NET to get Access Table Name

[tanai]

Hello!

 

I am trying to get Table Name from Access database by using ADO.NET.

Here is the code that I used. After run, it appears error as "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

 

 

Dim DataCommand As OleDbCommand
Dim DataReader As OleDbDataReader
DataCommand = New OleDbCommand
DataCommand.Connection = DataConnection
DataCommand.CommandType = CommandType.Text
DataCommand.CommandText = "SELECT * FROM MSysObjects Where Type=1 and Flags=0"
DataConnection.Open()
DataReader = DataCommand.ExecuteReader
If DataReader.HasRows Then
      MsgBox("OK, there are some tables!")
Else
      Msgbox("There is no table!")
End If
DataReader.Close()

If I change the table from MSysObjects to be other regular tables, it works fine. So, I am wondering if ADO.NET can be used to access to the system table or not. Or What I have been doing wrong? Would you please help advice?

 

Thank you very much.

Tanai

[kejpa]

If I change the table from MSysObjects to be other regular tables' date=' it works fine. So, I am wondering if ADO.NET can be used to access to the system table or not. [/quote']

Yes you can. But you need to have read rights to access the MSysObjects table.

You need to use the right login and have the right System.mdw

 

Can you open the database using Access?

If you can't then you need to get the right System.mdw and username/password

 

HTH

/Kejpa

[tanai]

Dear /Kejpa,

 

Thank you very much for your advice.

 

The problem does come from the access right. I have just known that userid "admin" does not have access right to those system tables by default eventhough it can browse the system table in MS Access directly.

 

Have a nice day!

Tanai

[cpopham]

That is an interesting result. So how did you give the Access "admin" rights to view the system table?

 

Chester

[tanai]

Hi Chester,

 

The easiest, it is use MS Access to set it up. :p The hard way, I think it is to use ADOX to set the permission. However, in my point of view, if I have to use ADOX, I 'd better use ADOX to get table name and schema directly. That would be easier.

 

Cheers,

Tanai

[cpopham]

Yeah and that brings up a good point, why not just use the get schema command to find out if you have tables, instead of trying to read the data dictionary? Just trying to learn some things here :)

 

Chester