Solved: List all tables & fields in a database

[Ken Puls]

Hey guys,

I've just opened up a brand new ODBC connection to a database I never had access to before. It has about 90 tables and a huge list of fields as well. The programmer basically left me with the "the names are intuitive, so good luck!" approach.

What I want to do is build a report that lists each table name with the name and datatype of each field it holds.

I'm thiking I could write an easy loop to do this, but I don't know the object model well enough. Can anyone help me with this?

[Ken Puls]

Okay, got some of it. This part lists all the tables and fields. Now I just need to get it into Excel or Word...

Option Compare Database
Option Explicit

Sub ListTablesAndFields()
Dim lTbl As Long
Dim lFld As Long
Dim dBase As Database

Set dBase = CurrentDb

On Error Resume Next
For lTbl = 1 To dBase.TableDefs.Count
For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count
Debug.Print dBase.TableDefs(lTbl).Name & vbTab & _
dBase.TableDefs(lTbl).Fields(lFld).Name & vbTab & _
dBase.TableDefs(lTbl).Fields(lFld).Type
Next lFld
Next lTbl
On Error GoTo 0
End Sub

[Ken Puls]

Okay, crude but works!

Option Compare Database
Option Explicit

Sub ListTablesAndFields()
Dim lTbl As Long
Dim lFld As Long
Dim dBase As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim lRow As Long

Set dBase = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.workbooks.Add

On Error Resume Next
For lTbl = 1 To dBase.TableDefs.Count
If Left(dBase.TableDefs(lTbl).Name, 2) = "01" Then
For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count
lRow = lRow + 1
With wbExcel.sheets(1)
.range("A" & lRow) = dBase.TableDefs(lTbl).Name
.range("B" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name
.range("C" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Type
End With
Next lFld
End If
Next lTbl
On Error GoTo 0

xlApp.Visible = True

Set xlApp = Nothing
Set wbExcel = Nothing
End Sub

Something tells me that I should have written it to an ADO or DAO recordset and pasted the whole thing... or maybe to an array. But what the heck.

[MOS MASTER]

I'm losing my head over here (A good thing beeing a Moose and all)...Now Ken is solving his own ACCESS question!!??? :eek:

Please tell me you're going to nightschool getting a tutorial on all of this? :tease:

No trully...again I'm baffeld!

[Ken Puls]

:rotlaugh:

Nope, no night school! I do know a teeny bit of access, but so far I've only built a couple of databases. My coding experience has pretty much been limited to what comes with a commandbutton when you create an event procedure!

At any rate... I need to make a minor adjustment to the code above. I've replaced the If Left statement with an If Not Left statement. When I first ran this, I had a table which was not exposed to the Database window... a temporary one? It started "~"...something.

Option Compare Database
Option Explicit

Sub ListTablesAndFields()
Dim lTbl As Long
Dim lFld As Long
Dim dBase As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim lRow As Long

Set dBase = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.workbooks.Add

On Error Resume Next
For lTbl = 1 To dBase.TableDefs.Count
If Not Left(dBase.TableDefs(lTbl).Name, 1) = "~" Then
For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count
lRow = lRow + 1
With wbExcel.sheets(1)
.range("A" & lRow) = dBase.TableDefs(lTbl).Name
.range("B" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name
.range("C" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Type
End With
Next lFld
End If
Next lTbl
On Error GoTo 0

xlApp.Visible = True

Set xlApp = Nothing
Set wbExcel = Nothing
End Sub

All my tables started with "01", but this should be a little more robust if someone else needs it.

Come to think of it... I think I may just clean this up a bit and submit it to the KB... :think:

[xCav8r]

Here's another approach. You might wanna pull the relationships too. Requires reference to DAO.

Sub TestTableDefs()

Dim dbs As DAO.Database
Dim MyTable As DAO.TableDef
Dim MyField As DAO.Field
Dim MyProperty As DAO.Property
Dim MyRelation As DAO.Relation

Set dbs = CurrentDb

For Each MyTable In dbs.TableDefs
Debug.Print "Table Name: " & MyTable.Name
For Each MyField In MyTable.Fields
Debug.Print " Field Name: " & MyField.Name
Debug.Print " Type: " & MyField.Type
For Each MyProperty In MyField.Properties
Debug.Print " Prop Name: " & MyProperty.Name
Debug.Print " Prop Type: " & MyProperty.Type
Next MyProperty
Next MyField
Next MyTable

For Each MyRelation In dbs.Relations
Debug.Print "One: " & MyRelation.Table & "." & MyRelation.Fields(0).Name & vbCr _
& "Many: " & MyRelation.ForeignTable & "." & MyRelation.Fields(0).ForeignName & vbCr
Next MyRelation

dbs.Close
Set dbs = Nothing
Set MyTable = Nothing
Set MyField = Nothing
Set MyProperty = Nothing
Set MyRelation = Nothing
End Sub

[MOS MASTER]

Hi Ken, :yes

Ahums..you know there is a better/easier way to get all that info from the tables (or other objects) you want.

Do you now the "Documenter"?

If not go to: menu item Tools > Analyze > Documenter

Tick the tables you want and press OK

Voila a beautifull report! :p

[xCav8r]

lol! Maybe his version of access is to old to have that. :rotlaugh:

[Ken Puls]

:rant:

I use 2003. Ahems, Joost... you could have mentioned that BEFORE I did all that work!

:rotlaugh:

Okay, seriously, though... didn't know it was there. Does print nice reports, though, yes... but... There is a TON of info in there. All I want (at this stage) is a quick list of table names with their fields so that I can scan them quickly to see if I think it is the right table to start exploring.

Marco, neat idea on the realtionships. :yes Only thing is that as this is an ODBC link and I haven't had time to set them yet... well... there are no relationships defined at this point. I am going to have to take care of that as I start using it to pull info from the DB.

I had to abandon the Immediate window for results too. Apparently it can't handle 4500 lines of fields. Who knew? :dunno

[xCav8r]

Really? Are you sure there are no relationships in a db with 90 tables?

[MOS MASTER]

lol! Maybe his version of access is to old to have that. :rotlaugh:

Nah..Ken is a up to date Dragon! :devil:

[MOS MASTER]

:rant:

I use 2003. Ahems, Joost... you could have mentioned that BEFORE I did all that work!


Mention it before....!!!??

Not in your life time buddy..you nead the practise! :rofl: :rotlaugh:

[Ken Puls]

Really? Are you sure there are no relationships in a db with 90 tables?

I think we're talking about things from a different angle here.

There are consistent fields between the tables, yes. But when you link your table in your current database to an external database via ODBC, the relationships need to be defined in Access.

In the host program (Jonas in this case) the relationships exist. In Access, though, they don't come with the linkage. I have to define it on my own. So now I need to look at two tables, decide which field should link, and set up the realtionships myself. Could be a long job. Fortunately only needs to be done once though! :)

[Ken Puls]

Mention it before....!!!??

Not in your life time buddy..you nead the practise! :rofl: :rotlaugh:

You're a nasty man, Joost! :rotlaugh:

[xCav8r]

You can connect to the other db with dao and read all the relationships and then write them to your own. A code is example that works with Northwind demonstrates this. It's included with your help documentation. :clever:

[MOS MASTER]

You're a nasty man, Joost! :rotlaugh:

A Moose gotta do...what a Moose gotta do! :devil:

[Ken Puls]

You can connect to the other db with dao and read all the relationships and then write them to your own. A code is example that works with Northwind demonstrates this. It's included with your help documentation. :clever:

Now you see, Joost? THIS is how you tell someone. BEFORE they do all the useless work! :rotlaugh:

Thanks, Marco! I'll have to look into that!

[MOS MASTER]

Now you see, Joost? THIS is how you tell someone. BEFORE they do all the useless work! :rotlaugh:


Why do you think Marco waited 3 post to answer the question??

Simple..he feels just like me .... you need the practise! :rotflmao: (Mista prankster...:jester: )

[xCav8r]

Who's your daddy?

http://support.microsoft.com/default.aspx?scid=kb;en-us;210302

Function ImportRelations(DbName As String) As Integer
'------------------------------------------------------------------
' PURPOSE: Imports relationships where table names and field names
' match.
' ACCEPTS: The name of the external database as a string.
' RETURNS: The number of relationships imported as an integer.
'------------------------------------------------------------------

Dim ThisDb As DAO.Database, ThatDB As DAO.Database
Dim ThisRel As DAO.Relation, ThatRel As DAO.Relation
Dim ThisField As DAO.Field, ThatField As DAO.Field
Dim Cr As String, i As Integer, cnt As Integer, RCount As Integer
Dim j As Integer
Dim ErrBadField As Integer

Cr$ = Chr$(13)
RCount = 0

Set ThisDb = CurrentDb()
Set ThatDB = DBEngine.Workspaces(0).OpenDatabase(DbName$)

' Loop through all existing relationships in the external database.
For i = 0 To ThatDB.Relations.Count - 1
Set ThatRel = ThatDB.Relations(i)

' Create 'ThisRel' using values from 'ThatRel'.
Set ThisRel = ThisDb.CreateRelation(ThatRel.Name, _
ThatRel.table, ThatRel.foreigntable, ThatRel.Attributes)

' Set bad field flag to false.
ErrBadField = False

' Loop through all fields in that relation.
For j = 0 To ThatRel.Fields.Count - 1
Set ThatField = ThatRel.Fields(j)

' Create 'ThisField' using values from 'ThatField'.
Set ThisField = ThisRel.CreateField(ThatField.Name)
ThisField.foreignname = ThatField.foreignname

' Check for bad fields.
On Error Resume Next
ThisRel.Fields.Append ThisField
If Err <> False Then ErrBadField = True
On Error GoTo 0
Next j

' If any field of this relationship caused an error,
' do not add this relationship.
If ErrBadField = True Then
' Something went wrong with the fields.
' Do not do anything.
Else
' Try to append the relation.
On Error Resume Next
ThisDb.Relations.Append ThisRel
If Err <> False Then
' Something went wrong with the relationship.
' Skip it.
Else
' Keep count of successful imports.
RCount = RCount + 1
End If
On Error GoTo 0
End If
Next i

' Close databases.
ThisDb.Close
ThatDB.Close

' Return number of successful imports.
ImportRelations = RCount

End Function

[MOS MASTER]

Who's your daddy?


Well you ain't!

Very nice you browsing maniak! :clap:

[Ken Puls]

LOL!

Okay, so now comes a big question for you, Marco. I never browsed anything to find the location of the database. We installed a specific ODBC driver, (System DSN,) and just linked the tables to it. I did set up the database path in the DSN connection though.

So the question... do I just use that database path? Should be as easy as calling the function at that point?

[xCav8r]

Just use the database path when you set the dao.database object.

[xCav8r]

More help: http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/web/011.asp

[Ken Puls]

Just use the database path when you set the dao.database object.

Hehe. If only it was that easy...

Here's what I've got... I have no idea what the name of the database file actually is... Below is a picture of what was set up as a System DSN in my ODBC connections screen. From here, I just go into access, and link tables to this ODBC source. I never have to choose the name of the database.

I know that this database has a kind of weird structure... it's flatfile. They write everything to text files in some manner or other. Not sure if that makes any difference or not.

[xCav8r]

Oh, I thought you had created a DSN to an access database. This is making more sense now. Can't say that you'll be able to dao to it though. My guess is no.

[Ken Puls]

LOL!

Nope. Nothing's ever easy here!

I think I'll just create the references as I need them. :yes

[ddcoy]

The fields with primary key's are not show ....
?


Sub ListTablesAndFields()
Dim lTbl As Long
Dim lFld As Long
Dim dBase As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim lRow As Long

Set dBase = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.workbooks.Add

On Error Resume Next
For lTbl = 1 To dBase.TableDefs.Count
If Left(dBase.TableDefs(lTbl).Name, 2) = "01" Then
For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count
lRow = lRow + 1
With wbExcel.sheets(1)
.range("A" & lRow) = dBase.TableDefs(lTbl).Name
.range("B" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name
.range("C" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Type
End With
Next lFld
End If
Next lTbl
On Error Goto 0

xlApp.Visible = True

Set xlApp = Nothing
Set wbExcel = Nothing
End Sub

[ddcoy]

Ok, simple
On Error Resume Next
'''' ITbl = 0 instead of 1 ''''

For lTbl = 0 To dBase.TableDefs.Count
If Left(dBase.TableDefs(lTbl).Name, 2) = "01" Then
For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count