2

How to get the table structure in MS Access with a SQL query?

Using the following query:

SELECT name FROM MSysObjects

Results in the following exception:

Exception: [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'.

Sully
  • 14,672
  • 5
  • 54
  • 79
ess
  • 663
  • 3
  • 9
  • 17
  • 1
    Are you after table names and column names? – NoChance Dec 26 '13 at 06:02
  • You shouldn't add "urgent" to your questions, it has the opposite effect - it makes people ignore the question instead. Have a read through [this paper](http://www.catb.org/~esr/faqs/smart-questions.html) on how to better ask technical questions. – Tobias Roland Dec 26 '13 at 06:03
  • yes kareem......i actually neeed the table structure so that i can create the access db to postgres – ess Dec 26 '13 at 06:04
  • These may help: http://harborsparrow.blogspot.com/2009/05/c-code-to-get-schema-of-access-table.html and http://stackoverflow.com/questions/1699897/retrieve-list-of-tables-in-ms-access-file – NoChance Dec 26 '13 at 06:05
  • I just noticed the Java tag, whereas the provided links are for c#. Sourry about that. Good luck. – NoChance Dec 26 '13 at 06:11
  • kareem.... i using java...can u help me in java – ess Dec 26 '13 at 06:12
  • Unfortunately I don't know java. – NoChance Dec 26 '13 at 06:17
  • its ok kareemm....thanks.... – ess Dec 26 '13 at 06:18

3 Answers3

1

To resolve the permissions issue for MSysObjects, see these similar questions: no read permission on 'MSysObjects', or Run C# queries against (hidden) system tables in Access?

Community
  • 1
  • 1
FreeText
  • 339
  • 3
  • 7
0

Use This

SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0

Ms Access has several system tables that are, by default, hidden from tables list. You can show them.

In Ms Access 2007 do a right click on tables list and select Navigation Options. At the bottom of the form you will find Show System Objects check box. Check it and system tables will show up in tables list. They all start with MSys. Alternatively, options form can be activated from application menu - click button Access options -> select Current Database and there is Navigation Options button.

Now you can examine structure and contents and generate queries of all system tables with MsAccess tools.

Source

Community
  • 1
  • 1
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • vignesh...previous link does nt give the table structure....i actually neeed the table structure so that i can create the access db to postgres....through java.... – ess Dec 26 '13 at 06:27
  • 1
    PerfectParadigm.com is for sale, the "This" link does not work. – Sam Hobbs Oct 01 '16 at 20:57
0

Set up an ODBC connection for your postrgres database and then call the transfer database command for each of your tables.

Cari Sayler
  • 1