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!

--
Lasse

[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

"Lasse Eskildsen" <Le******************@webspeed.dk> wrote in message
news:ez*************@TK2MSFTNGP10.phx.gbl...

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!

--
Lasse

[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

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

"Lasse Eskildsen" <Le******************@webspeed.dk> wrote in message
news:ez*************@TK2MSFTNGP10.phx.gbl...
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!

--
Lasse

[Paul Clement]

On Sun, 25 Jul 2004 17:49:52 +0100, "One Handed Man \( OHM - Terry Burns \)" <news.microsoft.com>
wrote:

¤ 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 ~~~ pc******@ameritech.net
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" <Us***********************@swspectrum.com> wrote in message
news:la********************************@4ax.com...

On Sun, 25 Jul 2004 17:49:52 +0100, "One Handed Man \( OHM - Terry Burns \)" <news.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Mon, 26 Jul 2004 16:00:48 +0100, "One Handed Man \( OHM - Terry Burns \)" <news.microsoft.com>
wrote:

¤ Cant say Ive ever run in to that problem yet

You're probably using an older version of Access.
Paul ~~~ pc******@ameritech.net
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

"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:0s********************************@4ax.com...

On Mon, 26 Jul 2004 16:00:48 +0100, "One Handed Man \( OHM - Terry Burns \)" <news.microsoft.com> wrote:

¤ Cant say Ive ever run in to that problem yet

You're probably using an older version of Access.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[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]

On Tue, 27 Jul 2004 07:12:30 +0100, "One Handed Man \( OHM - Terry Burns \)" <news.microsoft.com>
wrote:

¤ 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 ~~~ pc******@ameritech.net
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

"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:c2********************************@4ax.com...

On Tue, 27 Jul 2004 07:12:30 +0100, "One Handed Man \( OHM - Terry Burns \)" <news.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Lasse Eskildsen]

"Ken Tucker [MVP]" <vb***@bellsouth.net> skrev i en meddelelse
news:ea*************@TK2MSFTNGP11.phx.gbl...

Hi,
Another method.

[....]

Thanks, just what I've been looking for :o)

[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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[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

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

"muhammad usman" <ro*****@hotmail.com> wrote in message
news:eB**************@TK2MSFTNGP15.phx.gbl...
MSysobjecst is not permiitted to access from outside.
What do i have to do to access it and get the table names

USMAN

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!