423,818 Members | 2,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,818 IT Pros & Developers. It's quick & easy.

list of tables in mdb file

P: n/a
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.

Aug 14 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.
Aug 14 '06 #2

P: n/a
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.
Aug 14 '06 #3

P: n/a
"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
Aug 14 '06 #4

P: n/a
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
Aug 15 '06 #5

P: n/a
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
Aug 15 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.