msyscolumns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why can't I see/use the defined MSysColumns table within Access2000? Can use
the MSysObjects to obtain a list of tables within the database, now trying to
obtain the fields and data structure for a given table with SQL.
 
mhedenberg said:
Why can't I see/use the defined MSysColumns table within Access2000? Can use
the MSysObjects to obtain a list of tables within the database, now trying to
obtain the fields and data structure for a given table with SQL.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The table syscolumns is in .adp files. Is that what you're looking for?

Try using ADO OpenSchema method of the Connection object. Or, using
pure SQL you can use the INFORMATION_SCHEMA. E.g.:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Accounts'

In .mdb DBs use DAO & the TableDefs objects to find the columns per
table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiaXMoechKqOuFEgEQJGIgCeJUESBfl7i1CO+gKAQXw721nKRb4AnRcz
bV+a86QY+rBMyeDMlcJS/vOZ
=aYVv
-----END PGP SIGNATURE-----
 
Thanks MGFoster! I appologies for not getting back sooner as I never did
recieve notice of a posting an just happend to come back an look.

My issues was with an .mdb DB an the TableDef object pointed me in the right
direction. I was using a query on the MSysObjects table within Access and
from all mention on the web there is also a MSysColumns table and I couldn't
find it. By reviewing your message and looking into the TableDefs I came
across the help I needed.

I've included the sample help code I found for anyone else who may come
across this discussion.

Thanks again for all your help, very much appreciated!!!!

Sample VB.Net catelog code.

Dim cn As New OleDb.OleDbConnection
Dim schemaTable As DataTable
Dim i As Integer

'Connect to the Northwind MSAccess Database on local drive
cn.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='c:\Northwind.mdb'"

cn.Open()

'Retrieve schema information about tables.
'Because tables include tables, views, and other objects,
'restrict to just TABLE in the Object array of restrictions.
schemaTable = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})

'List the table name from each row in the schema table.
For i = 0 To schemaTable.Rows.Count - 1
Debug.WriteLine(schemaTable.Rows(i)!TABLE_NAME.ToString)
Next i

'Retrieve schema information about columns.
'Restrict to just the Employees TABLE.
schemaTable = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "Employees", Nothing})

'List the column name from each row in the schema table.
For i = 0 To schemaTable.Rows.Count - 1
Debug.WriteLine(schemaTable.Rows(i)!COLUMN_NAME.ToString)
Next i

'Explicitly close - don't wait on garbage collection.
cn.Close()
cn = nothing
schemaTable = nothing
 
Back
Top