Using ADO.NET to get Access Table Name

T

tanai

New member
Joined
Dec 1, 2004
Messages
3
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"


Code: 
 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
 
tanai said:
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.
Click to expand...
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 cant then you need to get the right System.mdw and username/password

HTH
/Kejpa
 
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
 
That is an interesting result. So how did you give the Access "admin" rights to view the system table?

Chester
 
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
 
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
 
Back
Top