Getting names of all the tables

[Aqif]

Hi

Is it possible to

1. Get the names of all the tables in current database and append them into a table.

2. Get the names of all the tables in an external database by giving its path and appending them into current database.

By the way I am trying to develop a link table utility.

Cheers!

Aqif

 

[Quehay]

Here's a routine that I use to drop/create a table that holds tables and column defs. For an external database use DAO Databases to open an external db (there are many posts with examples of this).

[tt]
Public Sub ShowTablesFields()
'Requires function FieldType

On Error GoTo Err_ShowTableDefs

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Dim Tdf As DAO.TableDef
Dim Fld As DAO.Field

Dim strDbName As String, strTblName As String, strFldName As String
Dim strSQL As String

'Avoid run-time error generated from existing copy of table
strSQL = "DROP TABLE TablesFields;"

DoCmd.RunSQL strSQL


strSQL = "CREATE TABLE TablesFields"
strSQL = strSQL & " (TableName TEXT(30), FieldName TEXT(30), Position NUMBER, FieldData TEXT(15),"
strSQL = strSQL & "FieldSize TEXT(5), DefaultValue TEXT(30), IsPrimary TEXT(10),"
strSQL = strSQL & "IsRequired TEXT(20));"

DoCmd.RunSQL strSQL

Set db = CurrentDb()

Set Rst = db.OpenRecordset("TablesFields&quot

With Rst
For Each Tdf In db.TableDefs
If Left(Tdf.Name, 4) <> &quot;Msys&quot; Then

For Each Fld In Tdf.Fields
.AddNew
!TableName = Tdf.Name
!FieldName = Fld.Name
!Position = Fld.OrdinalPosition
!FieldData = FieldType(Fld.Type)
!FieldSize = Fld.Size
!DefaultValue = Fld.DefaultValue


If Fld.Required Then
!IsRequired = &quot;Required&quot;
End If

.Update
Next
End If
Next
.Close
End With

MsgBox &quot;DONE&quot;

Exit_Err_ShowTableDefs:

Set Tdf = Nothing
Set Rst = Nothing
Set db = Nothing
Exit Sub

Err_ShowTableDefs:
Select Case Err.Number

Case 3371

Resume Next
Case Else
MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Err_ShowTableDefs
End Select

End Sub
Private Function FieldType(v_fldtype As Integer) As String
On Error GoTo Error_FieldType

Select Case v_fldtype
Case dbBoolean
FieldType = &quot;Boolean&quot;
Case dbByte
FieldType = &quot;Byte&quot;
Case dbInteger
FieldType = &quot;Integer&quot;
Case dbLong
FieldType = &quot;Long&quot;
Case dbCurrency
FieldType = &quot;Currency&quot;
Case dbSingle
FieldType = &quot;Single&quot;
Case dbDouble
FieldType = &quot;Double&quot;
Case dbDate
FieldType = &quot;Date&quot;
Case dbText
FieldType = &quot;Text&quot;
Case dbLongBinary
FieldType = &quot;LongBinary&quot;
Case dbMemo
FieldType = &quot;Memo&quot;
Case dbGUID
FieldType = &quot;GUID&quot;
End Select

Exit_Error_Fieldtype:
Exit Function

Error_FieldType:
MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Error_Fieldtype

End Function[/tt] Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[Quehay]

The error handling is cryptic--you'll get an error if you try to DROP a table that doesn't exist. If that's the error (3371) then it's ignored. Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com