Query untuk Menampilkan Seluruh Table di Database MS Access

by A Rahman

Lagi ada kerjaan untuk membandingkan daftar parameter di database lama dengan database baru yang sudah dilakukan pengembangan, tentunya akan lebih cepat kita keluarkan saja daftar tabel dari kedua database, baru kita bandingkan.

Setelah browsing sejenak ketemu disini, ternyata query nya gampang aja buat nampilin semua tabel yang ada di dalam database Ms Access.

Query nya hanya begini :

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE MSysObjects.Name Not Like “MsyS*” AND MSysObjects.Type=1
ORDER BY MSysObjects.Name;

The above SQL will return all ‘Non System’ Tables

Bagaimana kalau mau menampilkan seluruh query di dalam database Ms Access?

Sintaksnya begini :

SELECT [Name]
FROM MSysObjects
WHERE [Type] = 5 AND Left([Name],1) <> “~”
ORDER BY [Name]

Kalau kita lihat di kedua sintaks SQL tersebut ada type=1 dan type=5.  Lalu apa maksudnya?  Ternyata disini ada jawabannya, yang saya copy paste disini :

Access uses its own database engine to keep track of the objects in your
MDB, i.e. the tables, queries, forms, reports, etc. The names are stored in
a hidden system table named MSysObjects. You can see the table (but not
modify it) if you show hidden and system objects (Navigation Pane options in
A2007, or Tools | Options | View in previous versions.)

In MSysObjects, the Name field contains the name of the object, and the Type
field defines its type, e.g.:
   1           = table
   4           = ODBC linked table
   6           = linked table
   5           = query
   -32758 = form
   -32764 = report
   - 32761 = module

Deleted objects (to be removed next compact), and hidden queries (for the
RecordSource of a form or the RowSource of a combo/listbox) start with “~”.
The Microsoft system object names start with MSys.

Therefore, your query lists the names of the local tables and the queries in
the current database, excluding hidden and system objects, sorted by name

Oke semoga bermanfaat !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,551 other followers