To Find Number of Tables in Access2000 using VB6 Code

[plopez]

Hi,

I need to know how many tables have a Data Base in Access2000 using Code VB6.

I appreciate ur help...

Thanks

 

[dilettante]

Open it with ADOX and check Tables.Count?

You may have to exclude System Tables though.

 

[SBerthold]

You could also just simply use the Connection's OpenSchema method, where the adSchemaTables is an OLEDB requirement to be provided:

lRecCnt = Conn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE")).RecordCount

 

[SBerthold]

BTW, that does not include System/Access tables, Views, or Linked tables in the count. If you want a count of the Linked tables, then pass the string "LINK" instead of "TABLE" to the 4th dimension of the array.

 

[JerryKlmns]

Use an ADO connection and this SQL statment for a recordset

SELECT Count(*) AS myTables
FROM MSysObjects
WHERE [Name]  Not Like 'MSys%'  AND 
      [Type]=1
UNION
SELECT Count(*) AS myLinkedTables
FROM MSysObjects
WHERE [Type]=6;

 

[SBerthold]

"...where the adSchemaTables is an OLEDB requirement to be provided" meaning by all OLEDB providers.

So, the code I provided for OpenSchema and adSchemaTables should work for all providers, and not provider specific.

The only thing that needs to be considered, is that if the connection's default cursor location, (used only for the recordsets opened on it), is not set to use client, then you will get a ForwardOnly cursor back, and the record count will return -1.
It should be no problem setting the default cursor location to use Client prior to opening it (Conn.CursorLocation = adUseClient), but if it is still not desired, then just set a recordset object to the Conn.OpenSchema and loop until EOF, using a counting variable.

 

[plopez]

Hi,

I'm doing what you say but I need also the name of each table...Is that possible?

Thanks for your help

 

[JerryKlmns]

For my way

SELECT [Name] AS myTables
FROM MSysObjects
WHERE [Name]  Not Like 'MSys%'  AND
      [Type]=1
UNION
SELECT [Name] AS myLinkedTables
FROM MSysObjects
WHERE [Type]=6;

For SBerthold 's way

Dim rst As ADODB.Recordset
Set rst=Conn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
Do While not rst.EOF
   Debug.Print rst.Fields("TABLE_NAME")
   rst.Movenext
Loop

Take a look at MSAccess help on OpenSchema Method Example (VB)

 

[plopez]

Hi JerryKlmns & SBerthold

I use your tips and my App. works so good.

I appreciate your help,thank you very much

PLopez