Programmatically determining recordset names in database

[Barb Reinhardt]

I'm very new to Access programming and can find the individual tables
(recordsets) this way

Set rst = db.OpenRecordset("PS", dbOpenDynaset)

but I need to know the name of the recordset. Is there some way to figure
that out programmatically?

Thanks,

Barb Reinhardt

 

[Daniel Pineault]

What do you mean by recordset names? You are opening recorset 'PS', is the
name not PS?

If you mean you wish to list the fields within a recordset, then take a look
at:
http://www.devhut.net/index.php?lang=en&pid=0000000002#lstTblFlds

If you wish to list the tables within a db, then take a look at:
http://www.devhut.net/index.php?lang=en&pid=0000000002#lstTbls

If this isn't what you meant, please explain further.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Barb Reinhardt]

I'm sure I've got some of the terminology wrong, but I think this will do
what I want. I'm having issues with the help function of the Object Model
for Access (or maybe some just doesn't exist). Do you have any suggestions
where I should go for more info in the future? I checked some of the MVPS
pages with no success.

Barb Reinhardt

 

[Allen Browne]

This example shows how to enumerate the tables in your database:

Function ShowTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "MSys*") Then
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name
End If
End If
Next
Set db = Nothing
End Function

If you then want to enumerate the fields in the tables, see:
http://allenbrowne.com/func-06.html

If you prefer, you can query the MSysObjects (hidden, system, undocumented)
table. It contains names of the objects in your database (tables, queries,
forms, ...) So this query would list your tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

 

[Stuart McCall]

If Not (tdf.Name Like "MSys*" Or tdf.Name Like "MSys*") Then

Er.. shouldn't that be:

If Not (tdf.Name Like "MSys*" Or tdf.Name Like "USys*") Then

 

[Allen Browne]

Thanks Stuart. What I intended was:
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then

 

[Stuart McCall]

Thanks Stuart. What I intended was:
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then

Ah yes, the controlsource queries. I always forget to include them (mental
block I think) - they show up rather obviously in testing though, thank
goodness.