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, type –AutoSize |
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