Compare Database Tables 1

[JustineB]

Hi!
I have not got much experience using VB so wil appreciate any help that I can get!

I have a database that has changes made to its structure almost daily. These changes can include fields added to tables, fields deleted from tables, datatype changes, field name changes etc.

I would like to write something that I can run each week to see what changes have been made in each table. This will help with report writing which is an ongoing project. Currently, we have to run each report that has been written (in Cognos and Crystal Reports) and verify the database. This tell us which tables have been changed, but not what changes have been made to them.
If anyone can help - either with some sample code, or with ideas of how to best go about this, I would appreciate it very much.

Justine.

 

[sunaj]

Dim StrCon As String
Dim i As Integer
Hi,

You could loop through all the tables and save the information about the tables to a file.
Next week, read the file and compare the content to the new db structure to get the differences.

---------------------------------------------------
'Connection string
StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\tmp\test.mdb"
'Open connection
Set con = New ADODB.Connection
con.Open StrCon
'Set the catalogs connection
Set Cat = New ADOX.Catalog
Cat.ActiveConnection = StrCon
'Loop through the tables
For i = 0 To Cat.Tables.Count - 1
If Left(Cat.Tables(i).Name, 4) <> &quot;MSys&quot; Then
'save the properties of the table here.
'E.g. loop through Cat.Tables(i).Keys to get all the keys
End If
Next i
--------------------------------------------------------

Sunaj

 

[JustineB]

Thank you Sunaj - I will try your suggestion - it has given me something to work with!