list of tables in mdb file

[Javed]

Hi,
is there a way to get a list of tables in MDB file? Ideally I want to
write an SQL similar to
'select * from tab' of oracle.

[Nick 'The database Guy']

Hi Javed,

I don't know of any way that you can get all the table names in SQL,
however it is quite easy in VBA.

' Will build a list of tables that be safely deleted
Public Sub BuildTableList( )
Dim strTable As String
Dim rs As New ADODB.Recordset
Dim sql As String
Dim intTables As Integer
Dim varLoopVar As Variant
sql = "DELETE * FROM tblTable"
With DoCmd
.SetWarnings False
.RunSQL sql
.SetWarnings True
End With
sql = _
"SELECT TableName " & _
"FROM tblTable"
rs.Open sql, CurrentProject. AccessConnectio n, adOpenDynamic,
adLockOptimisti c
For Each varLoopVar In CurrentData.All Tables
strTable = varLoopVar.Name
With rs
.AddNew
!TableName = strTable
.Update
End With
Next
Set rs = Nothing
End Sub

This code will delete everything from tblTable and insert the name of
every table contained within the current database.

Good luck

Nick

Javed wrote:

Hi,
is there a way to get a list of tables in MDB file? Ideally I want to
write an SQL similar to
'select * from tab' of oracle.

[Javed]

Thanks Nice. That helps

- Javed.

Nick 'The database Guy' wrote:

Hi Javed,

I don't know of any way that you can get all the table names in SQL,
however it is quite easy in VBA.

' Will build a list of tables that be safely deleted
Public Sub BuildTableList( )
Dim strTable As String
Dim rs As New ADODB.Recordset
Dim sql As String
Dim intTables As Integer
Dim varLoopVar As Variant
sql = "DELETE * FROM tblTable"
With DoCmd
.SetWarnings False
.RunSQL sql
.SetWarnings True
End With
sql = _
"SELECT TableName " & _
"FROM tblTable"
rs.Open sql, CurrentProject. AccessConnectio n, adOpenDynamic,
adLockOptimisti c
For Each varLoopVar In CurrentData.All Tables
strTable = varLoopVar.Name
With rs
.AddNew
!TableName = strTable
.Update
End With
Next
Set rs = Nothing
End Sub

This code will delete everything from tblTable and insert the name of
every table contained within the current database.

Good luck

Nick

Javed wrote:

Hi,
is there a way to get a list of tables in MDB file? Ideally I want to
write an SQL similar to
'select * from tab' of oracle.

[RoyVidar]

"Javed" <ja******@gmail .comwrote in message
<11************ *********@i3g20 00cwc.googlegro ups.com>:

Hi,
is there a way to get a list of tables in MDB file? Ideally I want to
write an SQL similar to
'select * from tab' of oracle.

I don't know anything about Oracle, but you can get a list of tables
in an mde through something like

SELECT Name
FROM MSysObjects
WHERE Type In (1,4,6) AND Left([Name],4)<>"MSYS";

Where type = 1 denotes native Access tables.

--
Roy-Vidar

[Nick 'The database Guy']

Hi Roy,

How silly of me to forget about the MSysObjects table.

However I would update the SQL to, please see below, that will also
avoid temporary tables as well as system tables appearing on the list.

Thanks for the reminder.

Nick

SELECT MSysObjects.Nam e
FROM MSysObjects
WHERE (((MSysObjects. Type) In (1,4,6)) AND ((Left([Name],4))<>"MSYS")
AND ((Left([Name],1))<>"~"));

RoyVidar wrote:

"Javed" <ja******@gmail .comwrote in message
<11************ *********@i3g20 00cwc.googlegro ups.com>:

Hi,
is there a way to get a list of tables in MDB file? Ideally I want to
write an SQL similar to
'select * from tab' of oracle.

I don't know anything about Oracle, but you can get a list of tables
in an mde through something like

SELECT Name
FROM MSysObjects
WHERE Type In (1,4,6) AND Left([Name],4)<>"MSYS";

Where type = 1 denotes native Access tables.

--
Roy-Vidar

[Tim Marshall]

Javed wrote:

Hi,
is there a way to get a list of tables in MDB file? Ideally I want to
write an SQL similar to
'select * from tab' of oracle.

Is this for Oracle? FWIW, you can run select * from tab via a pass
through query. You'll get a listing of tables, abtype and clusterID for
all tables and views that were created in the user ID in which your DSN
was created.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me