Retreiving list of table & field names from Access into Excel using VBA
 

I am using VBA in MS Excel to create tables in MS Access and se
relationships between the tables as I have large amounts of relate
data. Now in order to do that, I need to know how to list the names o
the tables and fields in the tables in the database. This would help m
to choose tables and further choose fields in those tables to se
relationships between them. How do I do that? Please help.

Peace,

Shivbo

--
shivbo
-----------------------------------------------------------------------
shivboy's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=55160

Retreiving list of table & field names from Access into Excel usin
 

two approaches:

http://tinyurl.com/fpvvr

http://tinyurl.com/p45cj



--
Regards,
Tom Ogilvy

"shivboy" wrote:


I am using VBA in MS Excel to create tables in MS Access and set
relationships between the tables as I have large amounts of related
data. Now in order to do that, I need to know how to list the names of
the tables and fields in the tables in the database. This would help me
to choose tables and further choose fields in those tables to set
relationships between them. How do I do that? Please help.

Peace,

Shivboy


--
shivboy
------------------------------------------------------------------------
shivboy's Profile: http://www.excelforum.com/member.php...o&userid=35137
View this thread: http://www.excelforum.com/showthread...hreadid=551602

Retreiving list of table & field names from Access into Excel using VBA
 

Tom, thanks a ton for the help. But there is still a small issue I a
facing with the output.

Everytime the code retrieves the table names, it also retrieves th
following tables as well:

MSysAccessObjects
MSysACEs
MSysObjects
MSysQueries
MSysRelationships

What do I do to avoid displaying these tables as the output?

Please help on this one too.

Peace,

Shivboy
Tom Ogilvy Wrote:
two approaches:

http://tinyurl.com/fpvvr

http://tinyurl.com/p45cj



--
Regards,
Tom Ogilvy

"shivboy" wrote:


I am using VBA in MS Excel to create tables in MS Access and set
relationships between the tables as I have large amounts of related
data. Now in order to do that, I need to know how to list the name
of
the tables and fields in the tables in the database. This would hel
me
to choose tables and further choose fields in those tables to set
relationships between them. How do I do that? Please help.

Peace,

Shivboy


--
shivboy

------------------------------------------------------------------------
shivboy's Profile
http://www.excelforum.com/member.php...o&userid=35137
View this thread
http://www.excelforum.com/showthread...hreadid=551602



--
shivbo
-----------------------------------------------------------------------
shivboy's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=55160

Retreiving list of table & field names from Access into Excel using VBA
 

Try code like the following. Set a reference to DAO 3.6 and run
the following code:

Sub AAA()
Dim DB As DAO.Database
Dim Tbl As DAO.TableDef
Dim Rng As Range
Dim Fld As DAO.Field
Set Rng = Range("A1")
Set DB = DAO.OpenDatabase("H:\Test\Test.mdb")'<<< CHANGE

For Each Tbl In DB.TableDefs
If Tbl.Attributes = 0 Then
Rng.Value = Tbl.Name
Set Rng = Rng(2, 2)
For Each Fld In Tbl.Fields
Rng.Value = Fld.Name
Set Rng = Rng(2, 1)
Next Fld
Set Rng = Rng(1, 0)

End If
Next Tbl
DB.Close
End Sub

"shivboy"
wrote in message
...

I am using VBA in MS Excel to create tables in MS Access and
set
relationships between the tables as I have large amounts of
related
data. Now in order to do that, I need to know how to list the
names of
the tables and fields in the tables in the database. This would
help me
to choose tables and further choose fields in those tables to
set
relationships between them. How do I do that? Please help.

Peace,

Shivboy


--
shivboy
------------------------------------------------------------------------
shivboy's Profile:
http://www.excelforum.com/member.php...o&userid=35137
View this thread:
http://www.excelforum.com/showthread...hreadid=551602

Retreiving list of table & field names from Access into Excel using VBA
 

Hi Chip,

Thanks for the code, but my problem still persists. The tables listed
above still appear in addition to the tables created by me. What do I
do?

Peace,

Shivboy

Chip Pearson Wrote:
Try code like the following. Set a reference to DAO 3.6 and run
the following code:

Sub AAA()
Dim DB As DAO.Database
Dim Tbl As DAO.TableDef
Dim Rng As Range
Dim Fld As DAO.Field
Set Rng = Range("A1")
Set DB = DAO.OpenDatabase("H:\Test\Test.mdb")'<<< CHANGE

For Each Tbl In DB.TableDefs
If Tbl.Attributes = 0 Then
Rng.Value = Tbl.Name
Set Rng = Rng(2, 2)
For Each Fld In Tbl.Fields
Rng.Value = Fld.Name
Set Rng = Rng(2, 1)
Next Fld
Set Rng = Rng(1, 0)

End If
Next Tbl
DB.Close
End Sub

"shivboy"
wrote in message
...

I am using VBA in MS Excel to create tables in MS Access and
set
relationships between the tables as I have large amounts of
related
data. Now in order to do that, I need to know how to list the
names of
the tables and fields in the tables in the database. This would
help me
to choose tables and further choose fields in those tables to
set
relationships between them. How do I do that? Please help.

Peace,

Shivboy


--
shivboy

------------------------------------------------------------------------
shivboy's Profile:
http://www.excelforum.com/member.php...o&userid=35137
View this thread:
http://www.excelforum.com/showthread...hreadid=551602



--
shivboy
------------------------------------------------------------------------
shivboy's Profile: http://www.excelforum.com/member.php...o&userid=35137
View this thread: http://www.excelforum.com/showthread...hreadid=551602