Categories

Getting Access table definitions

So far we have only created a single table but in a database with a number of tables we need to be able to view the table definitions.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
##
## connect to database
##
$conn = New-Object -ComObject ADODB.Connection
$mydb = "c:\test\test03.mdb"
$conn.Open("Provider = Microsoft.JET.OLEDB.4.0; Data Source = $mydb")
$cat = New-Object -ComObject ADOX.Catalog
$cat.ActiveConnection = $conn

## view tables
## note user tables are of type TABLE
$cat.tables | select Name, DateCreated, DateModified

## view columns
$table = $cat.Tables | where {$_.Name -eq "test1"}
$table.Columns | Format-Table  Name, DefinedSize, typeAutoSize

 

Unfortunately we can’t use the SQL methods we have been doing so far.  We need to dip into ADODB and ADOX to get this information.

MAKE SURE THAT THE DATABASE IS CLOSED BEFORE ATTEMPTING TO DO THIS

Create an ADODB connection and open the database using the JET provider and the path to your database.

We then create a ADOX catalog and set its ActiveConnection property to the connection

We have a tables collection where we can view basic table information. For an individual table we can view the table definitions

Name      DefinedSize Type
----      ----------- ----
DOB                 0    7
FirstName         255  130
ID                  0    3
LastName          255  130

 

Next we need to dig into the type definitions so we can understand the listing and see how we can dump this information for all our tables

Leave a Reply