Selecting all tabels from an access db.

[Lasse Eskildsen]

Hello,

I would like to load various access databases in my application, but I can't
figure out how to get all tabels from the database into my dataset.

The databases are all different, so I don't know the names of any tables.

Any help on how to solve this would be greatly appreciated!

 

[One Handed Man \( OHM - Terry Burns \)]

Depends on your databale. Table names in access can be found using the
system table.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1
Or (MSysObjects.Type)=6))
ORDER BY MSysObjects.Name;

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .

Time flies when you don't know what you're doing

 

[Ken Tucker [MVP]]

Hi,


Another method.

Dim strConn As String



Dim conn As OleDbConnection

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = Northwind.mdb;"

conn = New OleDbConnection(strConn)

conn.Open()

Dim dtTableNames As DataTable

dttablenames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _

New Object() {Nothing, Nothing, Nothing, "TABLE"})

conn.Close()

Dim dr As DataRow

For Each dr In dtTableNames.Rows

Trace.WriteLine(dr.Item("TABLE_NAME"))

Next



Ken

----------------------

Hello,

I would like to load various access databases in my application, but I can't
figure out how to get all tabels from the database into my dataset.

The databases are all different, so I don't know the names of any tables.

Any help on how to solve this would be greatly appreciated!

 

[Paul Clement]

¤ Depends on your databale. Table names in access can be found using the
¤ system table.
¤
¤ SELECT MSysObjects.Name
¤ FROM MSysObjects
¤ WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1
¤ Or (MSysObjects.Type)=6))
¤ ORDER BY MSysObjects.Name;

Just an FYI, Access system tables are typically secured so they cannot be accessed. Using the schema
method is preferable.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[One Handed Man \( OHM - Terry Burns \)]

Cant say Ive ever run in to that problem yet

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .

Time flies when you don't know what you're doing

 

[Paul Clement]

¤ Cant say Ive ever run in to that problem yet

You're probably using an older version of Access.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[One Handed Man \( OHM - Terry Burns \)]

No were all using 2003 professional

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .

Time flies when you don't know what you're doing

 

[Cor Ligthert]

Hi Terry,

I tried the method from you and from Ken,

Yours did give by me an error (even when I changed "MSys" in 'MSys')

Can you maybe check it what it can be, although the method from Ken is fine
of course, so do not take to much time for it.

However there can be a reason that you like to show it is working, by
instance for people who are later searching for this on Google

Cor

 

[Paul Clement]

¤ No were all using 2003 professional

Then the tables are probably not secured.

If you implement user-level security, access to these tables is generally removed. You will also
notice that by default the tables are not visible to the user unless this option is turned on. If
any of the system tables become corrupted your database could be hosed.

In addition, since these are internal tables used specifically by Access and Jet, there is always
the possibility that they could be changed in a future version.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[One Handed Man \( OHM - Terry Burns \)]

Thanks 4 pointing this out.

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .

Time flies when you don't know what you're doing

 

[Lasse Eskildsen]

Hi,


Another method.

[....]

Thanks, just what I've been looking for )

 

[muhammad usman]

MSysobjecst is not permiitted to access from outside.
What do i have to do to access it and get the table names

USMAN

 

[Ken Tucker [MVP]]

Hi,


Use the oledbconnection classes getoledbschematable method.


Dim strConn As String



Dim conn As OleDbConnection

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = Northwind.mdb;"

conn = New OleDbConnection(strConn)

conn.Open()

Dim dtTableNames As DataTable

dttablenames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _

New Object() {Nothing, Nothing, Nothing, "TABLE"})

conn.Close()

Dim dr As DataRow

For Each dr In dtTableNames.Rows

Trace.WriteLine(dr.Item("TABLE_NAME"))

Next



Ken

----------------------

MSysobjecst is not permiitted to access from outside.
What do i have to do to access it and get the table names

USMAN