GetOleDbSchemaTable(OleDbSchemaGuid.Columns,...

hello outside,

when i retrieve column information on an acces table:
by using: GetOleDbSchemaTable(OleDbSchemaGuid.Columns,

I get something like the following:

field TABLE_CATALOG="" TABLE_SCHEMA="" TABLE_NAME="articles"
COLUMN_NAME="ID" COLUMN_GUID="" COLUMN_PROPID="" ORDINAL_POSITION="1"
COLUMN_HASDEFAULT="False" COLUMN_DEFAULT="" COLUMN_FLAGS="90"
IS_NULLABLE="False" DATA_TYPE="3" TYPE_GUID="" CHARACTER_MAXIMUM_LENGTH=""
CHARACTER_OCTET_LENGTH="" NUMERIC_PRECISION="10" NUMERIC_SCALE=""
DATETIME_PRECISION="" CHARACTER_SET_CATALOG="" CHARACTER_SET_SCHEMA=""
CHARACTER_SET_NAME="" COLLATION_CATALOG="" COLLATION_SCHEMA=""
COLLATION_NAME="" DOMAIN_CATALOG="" DOMAIN_SCHEMA="" DOMAIN_NAME=""
DESCRIPTION="" /

the meaning of some of the information is obvoius, but some is not.

Can anybody point me to a documentation on this information?

thanks
Markus

hi Markus,

Markus Ohlenroth wrote:
when i retrieve column information on an acces table:
by using: GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
Why don't you use the DAO.TableDefs collection?

Can anybody point me to a documentation on this information?
Maybe

http://msdn.microsoft.com/de-de/libr...le(VS.80).aspx

?


mfG
-- stefan --

hi Stefan
thanks for your information.

Maybe I was inaccurate in my question.

But actually I was looking for documentation on the information returned,
like e.g.
COLUMN_FLAGS="90"
and not on how the GetOledbSchemaTable works



"Stefan Hoffmann" wrote:

hi Markus,

Markus Ohlenroth wrote:
when i retrieve column information on an acces table:
by using: GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
Why don't you use the DAO.TableDefs collection?

Can anybody point me to a documentation on this information?
Maybe

http://msdn.microsoft.com/de-de/libr...le(VS.80).aspx

?


mfG
-- stefan --

hi Markus,

Markus Ohlenroth wrote:
Maybe I was inaccurate in my question.
Yes, maybe .)


But actually I was looking for documentation on the information returned,
like e.g.
COLUMN_FLAGS="90"
and not on how the GetOledbSchemaTable works

http://support.microsoft.com/kb/309681

see the links at the end. Maybe also

http://msdn.microsoft.com/en-us/library/kcax58fh.aspx


mfG
-- stefan --

hi Stefan,
which link do you mean?
I could not find one piece of documentation on the values the returned field
named
COLUMN_FLAGS can take.
mfg
Markus


http://support.microsoft.com/kb/309681

see the links at the end. Maybe also

http://msdn.microsoft.com/en-us/library/kcax58fh.aspx


mfG
-- stefan --

hi Markus,

Markus Ohlenroth wrote:
which link do you mean?
I could not find one piece of documentation on the values the returned field
named
COLUMN_FLAGS can take.

http://msdn.microsoft.com/en-us/library/bb677266.aspx


mfG
-- stefan --

hi Stefan,
thank you for your answer

Forgive me, maybe I am just too knew to ms access but I cannot see the
connection between the links you are offering and the question I have

For instance the last link you offer:
http://msdn.microsoft.com/en-us/library/bb677266.aspx

It refers to SQL Server Date/Time and Schema Rowsets - as fas I can see -
not to MS access. Can you give me a clue how your links have to do with the
values the field COLUMN_FLAGS can take

I figured out that the
The values COLUMN_FLAGS - as one example - can take when reading from access
via GetOledbSchema has to to with autoincrement and type properteis of a
field.

But where is the documentation from Microsoft for these values?
Or how do I connect the information you offer with my question?

thanks
Markus
This thread does not seem to get us anywhere




"Stefan Hoffmann" wrote:

hi Markus,

Markus Ohlenroth wrote:
which link do you mean?
I could not find one piece of documentation on the values the returned field
named
COLUMN_FLAGS can take.

http://msdn.microsoft.com/en-us/library/bb677266.aspx


mfG
-- stefan --

hi Markus,

Markus Ohlenroth wrote:
Forgive me, maybe I am just too knew to ms access but I cannot see the
connection between the links you are offering and the question I have
And I don't see the connection to Access. Can you provide a complete
code example with a brief description of your goal(s)?


mfG
-- stefan --

hi Stefan,
I work with visual studio, C# and read out metadata from access databases
via oledb

one method I use is : GetOledbSchemaTable
cf:
http://msdn.microsoft.com/en-us/libr...mata ble.aspx


the returned values are stored in a datatable:
DataTable schemaTable;
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,.. ..


if you query an access database and write the datatable to xml you get
something like:

field TABLE_CATALOG="" TABLE_SCHEMA="" TABLE_NAME="articles"
COLUMN_NAME="ID" COLUMN_GUID="" COLUMN_PROPID="" ORDINAL_POSITION="1"
COLUMN_HASDEFAULT="False" COLUMN_DEFAULT="" COLUMN_FLAGS="90"
IS_NULLABLE="False" DATA_TYPE="3" TYPE_GUID="" CHARACTER_MAXIMUM_LENGTH=""
CHARACTER_OCTET_LENGTH="" NUMERIC_PRECISION="10" NUMERIC_SCALE=""
DATETIME_PRECISION="" CHARACTER_SET_CATALOG="" CHARACTER_SET_SCHEMA=""
CHARACTER_SET_NAME="" COLLATION_CATALOG="" COLLATION_SCHEMA=""
COLLATION_NAME="" DOMAIN_CATALOG="" DOMAIN_SCHEMA="" DOMAIN_NAME=""
DESCRIPTION="" /


now my question once again. Where can I find documentation on the values
each field can take?
thanks Markus


You wanted a complete code example:

Below I have added my first try on reading metadata from an access database.
It builds xml from all tables and fields in an access database. It is quick
and dirty, based on a number of assumptions (e.g. dbmetadata.xml file should
exist, linq namespace, xml namespace, etc.).


private void btnGetOledbSchemaTable_Click(object sender, EventArgs e)
{

XElement newTables = new XElement("tables", null);
DataTable schemaTable;
String ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source='d:\\temp\\Database.mdb'";
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();

// add all table names to newTables
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });


for (int i = 0; i schemaTable.Rows.Count; i++)
{
newTables.Add(new XElement("table",new
XAttribute("Name",schemaTable.Rows[i].ItemArray[2].ToString())));
}

schemaTable.Clear();
XNode node;
XNode fieldNode;
node = newTables.FirstNode;
do {

//retrieve all field names to the schemaTable
schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
new Object[] { null, null, (node as
XElement).Attribute("Name").Value, null });
(node as XElement).Add(new XElement("fields",null));

//get the node just added
fieldNode = (node as XElement).LastNode;

schemaTable.WriteXmlSchema("D:\\temp\\schemaTable. xsd");


for (int k = 0; k schemaTable.Rows.Count; k++)
{
((node as XElement).LastNode as XElement).Add(new
XElement("field",null));

for (int l = 0; l schemaTable.Columns.Count; l++)
{
((fieldNode as XElement).LastNode as XElement).
Add(new
XAttribute(schemaTable.Columns[l].ColumnName,schemaTable.Rows[k].ItemArray[l].ToString()));
};
}
schemaTable.WriteXml("d:\\temp\\schemaTable.xml");
schemaTable.Clear();

}
while ((node = node.NextNode)!= null );
con.Close();
XDocument po = XDocument.Load("d:\\temp\\dbMetaData.xml");
XElement oldTables = new XElement("tables", null);

try
{
oldTables =
po.Root.DescendantsAndSelf().Elements("tables").Fi rst();
oldTables.ReplaceWith(newTables);
}
catch (System.InvalidOperationException)
{
po.Root.Add(newTables);
}
finally
{
XmlWriter writer = XmlWriter.Create("d:\\temp\\dbMetaData.xml");
po.WriteTo(writer);
writer.Flush();
writer.Close();
}

}
}





"Stefan Hoffmann" wrote:

hi Markus,

Markus Ohlenroth wrote:
Forgive me, maybe I am just too knew to ms access but I cannot see the
connection between the links you are offering and the question I have
And I don't see the connection to Access. Can you provide a complete
code example with a brief description of your goal(s)?


mfG
-- stefan --

hi Markus,

Markus Ohlenroth wrote:
I work with visual studio, C# and read out metadata from access databases
via oledb
Now, your source gives me the clues I was missing, take a look at these:

http://msdn.microsoft.com/en-us/libr...52(VS.85).aspx

and

http://msdn.microsoft.com/en-us/libr...04(VS.85).aspx


mfG
-- stefan --