List Out The Tables Name Exists In Database!!! 1

[nicholasting]

language:vb6
database: ms access

i would like to list out all the tables name that are exits in my databse. i know got a way is to check the table whether is exist. is yes, return true. if not, return false.

beside this way, is that any other way?
i woulid like to display the tables name that are exists in the database to a list box.

thanks~~~

 

[johnwm]

Hi and welcome to Tek-Tips. To get the best from these forums read faq222-2244 carefully.

For this question, run a query on the MSysObjects table (it's a hidden table which you can see in Tools|Options|View|System Objects). The Type for a table is 1, so your query will look something like:
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like 'MSys*') AND ((MSysObjects.Type)=1));

That is straight from Access so you'll need to change the * to % if you're using ADO

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Golom]

Just to be picky

SELECT Name, Type

FROM MSysObjects

WHERE Name Not Like 'MSys*' 
  AND Type [COLOR=red]IN (1,6)[/color];

Type 1 gives tables in the database and type 6 is linked tables from other databases.

 

[nicholasting]

connection Method:
microsoft activex data objecs 2.6 library

connection:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\User\Desktop\Casa Vino Database\Coding Database.mdb;Mode=Read;Persist Security Info=False;jet oledb:database password=88888

SQL:
SELECT Name, Type FROM MSysObjects WHERE Name Not Like 'MSys*' AND Type IN (1,6);

OR

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like 'MSys%') AND ((MSysObjects.Type)=1));

Error Found:
recods cannot be read; no read permission on 'MsSysObjects.'

the error occur when the time i execute the SQL. is it something wrong with my connection?

thanks~

 

[petermeachem]

I use this
lstTables.Clear

Set rs = cnnnew.OpenSchema(adSchemaTables)
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF
If UCase$(rs(3)) = "TABLE" Or UCase$(rs(3)) = "LINK" Or UCase$(rs(3)) = "VIEW" Then
lstTables.AddItem rs(2)
End If
rs.MoveNext
Loop
End If

 

[JerryKlmns]

nicholasting

Can you read from the table inside the access enviroment and not through connection? Create a query (a Picky or Steam Roller) or simply open the table.

The error would be different if your connection string were wrong. To check the connection use

Select case cnn.State ' ObjectStateEnumerators
Case 0 'adStateClosed
MsgBox "Connection Closed"
Case 1 'adStateOpen
MsgBox "Connection Open"
Case 2 'adStateConnecting
MsgBox "Connection Connecting to Data Source"
Case 4 'adStateExecuting
MsgBox "Connection Executing Command"
Case 8 'adStateFetching
MsgBox "Connection Retrieving Data"
End Select

 

[Swi]

You could always use ADOX:

thread222-921824

or DAO

Dim otable As DAO.TableDef
For Each otable In dbCurrent.TableDefs
MsgBox otable.Name
Next otable
Set otable = Nothing

Swi