Tom said:
Yes, I believe I can. If you'd like to send me a small test database that
includes some tables, views, procedures, queries, etc., I will run Total
Access Analyzer and SpeedFerret on it, and send you a copy of the results.
Thank you, this is a generous offer.
Below is an existing script I had to create a test database with three
tables, two views ('stored queries') and two procedures ('parameter
queries'). I appended a DROP TABLE, just to make things interesting
<g>. The objects were originally for testing purposes i.e. I'm not
trying to pass this off as a typical database.
Things I hope a documenter would reveal, beyond the obvious, also being
things that I have noted some documenters do not do:
1) The data type, precision and scale of each column/parameter.
2) The default value of each proc parameter.
3) That TestTable1 has six CHECK constraints, two column-level, two
row-level and two table-level.
4) The tables and columns used in each CHECK constraint.
5) The table precedence e.g. TestTable0 > TestTable1.
6) That TestView1 uses base tables TestTable0 and TestTable1.
7) That TestView2 uses virtual table TestView1 and now-dropped
TestTable2.
8) That TestProc1 uses base tables TestTable1 and virtual table
TestView1, hence requires TestTable0.
That TestProc1 uses base tables TestTable1 and now-dropped TestTable2.
Perhaps you could post the results or a summary?
Sub tomtest()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\JamieTest.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE TestTable0 ( " & _
"test_col1 DECIMAL(19, 4) NOT NULL " & _
") " & _
";"
.Execute _
"ALTER TABLE TestTable0 ADD " & _
"CONSTRAINT pk__TestTable0 " & _
"PRIMARY KEY (test_col1) " & _
") " & _
";"
.Execute _
"CREATE TABLE TestTable1 ( " & _
"test_col1 DECIMAL(19, 4) NOT NULL " & _
") " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT pk__TestTable1 " & _
"PRIMARY KEY (test_col1) " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT fk__TestTable1__TestTable0 " & _
"FOREIGN KEY (test_col1) " & _
"REFERENCES TestTable0 (test_col1) " & _
"ON DELETE SET NULL " & _
"ON UPDATE CASCADE " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"COLUMN test_col2 DECIMAL(19, 4) NOT NULL " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT uq____TestTable1 " & _
"UNIQUE (test_col2) " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT TestTable1__check__columnlevel1 " & _
"CHECK(test_col1 > 0) " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT TestTable1__check__columnlevel2 " & _
"CHECK(NOT (test_col1 " & _
"BETWEEN 5.0000 AND 9.9999)) " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT TestTable1__check__rowlevel1 " & _
"CHECK(test_col1 < test_col2) " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT TestTable1__check__rowlevel2 " & _
"CHECK((test_col1 - INT(test_col1)) " & _
"> test_col2 - INT(test_col2))) " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT TestTable1__check__tablelevel1 " & _
"CHECK(5 >= ( " & _
"SELECT COUNT(*) FROM TestTable1 AS T1))) " & _
";"
.Execute _
"ALTER TABLE TestTable1 ADD " & _
"CONSTRAINT TestTable1__check__tablelevel2 " & _
"CHECK(TestTable1.test_col1 > ( " & _
"SELECT COUNT(*) FROM TestTable0 AS T1) " & _
") " & _
";"
.Execute _
"CREATE TABLE TestTable2 ( " & _
"test_col1 DECIMAL(19, 4) NOT NULL " & _
") " & _
";"
.Execute _
"ALTER TABLE TestTable2 ADD " & _
"CONSTRAINT fk__TestTable1__TestTable1 " & _
"FOREIGN KEY (test_col1) " & _
"REFERENCES TestTable1 (test_col1) " & _
"ON DELETE SET NULL " & _
"ON UPDATE CASCADE " & _
";"
.Execute _
"CREATE VIEW TestView1 " & _
"AS " & _
"SELECT T0.test_col1 AS col1, " & _
"T1.test_col2 AS col2 " & _
"FROM TestTable0 AS T0 " & _
"LEFT JOIN TestTable1 AS T1 " & _
"ON T0.test_col1 = T1.test_col1 " & _
";"
.Execute _
"CREATE VIEW TestView2 ( " & _
"col1, col2 " & _
") AS " & _
"SELECT T2.test_col1, V1.col2 " & _
"FROM TestView1 AS V1 " & _
"LEFT JOIN TestTable2 AS T2 " & _
"ON V1.col2 = T2.test_col1 " & _
";"
.Execute _
"CREATE PROCEDURE TestProc1 ( " & _
"arg_col1 DECIMAL(19, 4) = 12.3456, " & _
"arg_col2 DECIMAL(19, 4) = 65.4321 " & _
") AS " & _
"INSERT INTO TestTable1 " & _
"(test_col1, test_col2) " & _
"SELECT DISTINCT arg_col1, arg_col2 " & _
"FROM TestTable1 AS T1 " & _
"WHERE NOT EXISTS ( " & _
"SELECT * " & _
"FROM TestView1 AS V1 " & _
"WHERE V1.col1 > arg_col1 " & _
") " & _
";"
.Execute _
"CREATE PROCEDURE TestProc2 ( " & _
"arg_col1 DECIMAL(19, 4) = 12.3456, " & _
"arg_col2 DECIMAL(19, 4) = 65.4321 " & _
") AS " & _
"INSERT INTO TestTable1 (test_col1, " & _
"test_col2) " & _
"SELECT DISTINCT arg_col1, arg_col2 " & _
"FROM TestTable0 AS T0 " & _
"WHERE NOT EXISTS ( " & _
"SELECT * " & _
"FROM TestTable2 AS T2 " & _
"WHERE T2.col1 > arg_col1 " & _
") " & _
";"
.Execute _
"DROP TABLE TestTable2 " & _
";"
.Close
End With
End With
End Sub
Thanks,
Jamie.
--
