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.AccessConnection, adOpenDynamic,
adLockOptimistic
For Each varLoopVar In CurrentData.AllTables
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.AccessConnection, adOpenDynamic,
adLockOptimistic
For Each varLoopVar In CurrentData.AllTables
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*********************@i3g2000cwc.googlegroups.c om>:

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.Name
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*********************@i3g2000cwc.googlegroups.c om>:

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