By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,417 Members | 1,088 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,417 IT Pros & Developers. It's quick & easy.

Selecting all tabels from an access db.

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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

Nov 20 '05 #2

P: n/a
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

Nov 20 '05 #3

P: n/a
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)
Nov 20 '05 #4

P: n/a
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)

Nov 20 '05 #5

P: n/a
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)
Nov 20 '05 #6

P: n/a
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)

Nov 20 '05 #7

P: n/a
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
Nov 20 '05 #8

P: n/a
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)
Nov 20 '05 #9

P: n/a
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)

Nov 20 '05 #10

P: n/a

"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)
Nov 20 '05 #11

P: n/a
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!
Nov 21 '05 #12

P: n/a
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!
Nov 21 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.