By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Getting the data types and sizes from Access

P: 1
I would like to write an SQL query that would bring back a list of all the tables, fields, data types and sizes in Access. I can do this in SQL server but cannot find the field information in the hidden tables.

Any ideas would be appreciated.

Kind regards

PeterF
Oct 15 '06 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
If you just need the information you can retrieve it using the documenter.

Tools - Analyse - Documenter

Highlight the tables you want and the documenter will create a report showing this information. Make sure you change the options.

If you need an actual table you will need to use a function similar to the one below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function TableStructure(tblName As String)
  3. Dim db as Database
  4. Dim rs as Recordset
  5. Dim i as Integer
  6.  
  7.     Set db = CurrentDb
  8.     Set rs = db.OpenRecordset("TableToStoreValues")
  9.  
  10.     For i = 0 To db.tabledefs(tblName).Fields.Count - 1
  11.         rs.AddNew
  12.         rs!TableName = tblName
  13.         rs!FieldName = db.tabledefs(tblName).Fields(i).Properties(4).Value
  14.         rs!FieldSize = db.tabledefs(tblName).Fields(i).Properties(3).Value
  15.     Next i
  16. End Sub
  17.  
  18.  
I would like to write an SQL query that would bring back a list of all the tables, fields, data types and sizes in Access. I can do this in SQL server but cannot find the field information in the hidden tables.

Any ideas would be appreciated.

Kind regards

PeterF
Oct 15 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.