Access XP 2002

[Tony]

XP - vs 2002 - vb 2002 - win forms

Anyplace I can look at an example of how to extract the following from an
access mdb
Table name
Table Description
Field Name
Field description
Field type
Field size


Thanks
Tony

 

[J L]

Here's how I do it:

cn = CreateConnection()
cn.Open()
' get list of tables
Dim myNull() As Object = {aNull, aNull, aNull, "TABLE"}
' cast connection so we can use GetOleDbSchemaTable method
dt = CType(cn,
OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Tables, myNull)
Dim dr As DataRow
For Each dr In dt.Rows
TableList.Add(dr("TABLE_NAME"))
Next
' read table data
cmd.Connection = cn
For i = 0 To TableList.Count - 1
strTableName = TableList(i)
cmd.CommandText = "SELECT * FROM " & strTableName
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
dt = myReader.GetSchemaTable()
strPKList = ""
Dim fieldList As New ArrayList
For Each dRow In dt.Rows
aField.FieldName = dRow("ColumnName")
aField.OrdinalPosition = dRow("ColumnOrdinal")
aField.DataType = dRow("DataType")
aField.Size = dRow("ColumnSize")
fieldList.Add(aField)
If dRow("IsKey") Then
' remove the last ;
strPKList += dRow("ColumnName") & ";"
End If
Next
If Len(strPKList) > 1 Then
strPKList = strPKList.Remove(Len(strPKList) - 1, 1)
End If
TableFields.Add(strTableName, fieldList)
TablePrimaryKeys.Add(strTableName, strPKList)
myReader.Close()
Next

John

 

[Tony]

Thanks John,
I have a couple of questions for you if you don't mind.
I've decleared the following,

Dim cmd As OleDbCommand
Dim drdReader As OleDbDataReader
Dim dt As DataTable
Dim TableList As New ArrayList()
Dim fieldList As New ArrayList()
Dim strTablename As String
Dim strPKList As String

Created the connection string

I cannot figure out how to declear the following

aNull
dRow
afield

TableFields creates error
TablePrimaryKeys creates error


Thanks for the code and your help
Tony