How do I find out how many tables there are in a database using SQL or ADO????
Thanx,
Squirrelly1
Printable View
How do I find out how many tables there are in a database using SQL or ADO????
Thanx,
Squirrelly1
This will list them, will that help?VB Code:
Private Sub GetDbTables() Dim Cnxn As ADODB.Connection Dim rstSchema As ADODB.Recordset Dim strCnxn As String Set Cnxn = New ADODB.Connection strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; " Cnxn.Open strCnxn Set rstSchema = Cnxn.OpenSchema(adSchemaTables) Do Until rstSchema.EOF List1.AddItem "Table name: " & rstSchema!TABLE_NAME & vbCr & "Table type: " & rstSchema!TABLE_TYPE & vbCr rstSchema.MoveNext Loop ' clean up rstSchema.Close Cnxn.Close Set rstSchema = Nothing Set Cnxn = Nothing End Sub
Is there any way to show just the tables where the table type is "TABLE". All of the other tables are irrelavent...
thanx again,
Squirrelly1:p
actually, I need to be able to loop through them all and pull data from them based on a criteria string in sql...
This is some cool code, but now that I think of it, it won't really help me.
Do you know how to do what I need?
squirrelly1
Well, I never had the need to do this before, but I should work.
Modify the code I gave you and use rstSchema!TABLE_TYPE = 'Whatever'