MS Access help...Code

[ryanpch]

Hello,

I'm trying to write some code to take all the tables I have created and put their names in a list/table. I was able to do this with form names but not table names.

Dim obj As AccessObject, dbs As Object
Dim formsdata1 As Recordset
Dim i As Long
i = 0
CurrentDb.Execute " DELETE [All Tables].* FROM [All Tables] "
Set formsdata1 = CurrentDb.OpenRecordset("All Tables")
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in Tables collection.
With formsdata1
For Each obj In dbs.Alltables <--HERE IS THE PROBLEM
.AddNew
![TableName] = obj.Name
.Update
i = i + 1
Next obj
End With

What else can I put in place of Alltables? When I did it for Forms, I put dbs.AllForms and it worked no problem.

Any suggestions?

Thanks,

Ryan

 

[HarleyQuinn]

If you add a reference to 'Microsoft DAO 3.X Object Library' you can use the TableDefs collection, for example:

Sub ListTables()

Dim obj As DAO.TableDef

    For Each obj In CurrentDb.TableDefs
            Debug.Print obj.Name
    Next obj

End Sub

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[dhookom]

You could also try something like the following to append the table names:

INSERT INTO [All Tables] ( TableName )
SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name) Not Like "Msys*") AND ((msysobjects.Type) In (1,4,6)));

Duane
Hook'D on Access
MS Access MVP