How to Compare Tables, Fields, & Field Properties 
Author Message
 How to Compare Tables, Fields, & Field Properties
Hi,
Looking for some pointers on how to compare the tables, fields, and field
propeties of two databases.  I am pretty sure you can do something like this
using ADOX, but have yet to find a good example.  Sometime the development
and production database structures get out of sync and to be able to run a
routine sure would be nice!   Any help would be appreciated.
Thanks,
    Michael Murphy



Tue, 13 Aug 2002 03:00:00 GMT
 How to Compare Tables, Fields, & Field Properties

You will probably find the info you need in this sample using the
OpenSchema. It does require that you are using a provider that supports the
methods you use (AdSchemaColumns, Tables, Etc) This sample uses the Pubs
database in SQL server. To use with an Access DB you would need to change
the connection string to the Access string provided.( Be sure to change the
Path to a valid mdb file)

Place 3 Datagrids on a form. DataGrid1, DataGrid2, and DataGrid3

Set a project reference to Microsoft ActiveX DataObjects 2.x library.

Paste this code into a form.

----------------------------------------------------------------------------
---------

Dim adoPrimaryRS As Recordset

Private Sub Form_Load()
  Dim db As Connection
  Dim RS1 As Recordset
  Dim RS2 As Recordset
  Dim RS3 As Recordset

  Set db = New Connection
  db.CursorLocation = adUseClient

'For SQL Server use following connection
  db.Open "PROVIDER=sqloledb;driver={SQL
Server};server=Darkover;uid=sa;pwd=;database=Pubs;"

'For Access Database use following connection
  'db.Open "PROVIDER=Microsoft.Jet.Oledb.4.0;data Source=e:\Northwind.mdb;"

  Set RS1 = db.OpenSchema(adSchemaColumns)
  Set DataGrid1.DataSource = RS1
  Set RS2 = db.OpenSchema(adSchemaIndexes)
  Set DataGrid2.DataSource = RS2
  Set RS3 = db.OpenSchema(adSchemaTables)
  Set DataGrid3.DataSource = RS3
End Sub

----------------------------------------------------------------------------
--------



Sat, 17 Aug 2002 03:00:00 GMT
 How to Compare Tables, Fields, & Field Properties
Hi,
Thanks for you responses.
After some more searching, I found a great little utility (VB source code
included) on Planet-source-code.com posted by Joel Isenstadt.
Joel did a great job. I added my own routines and have saved a tremendous
amount of time. The Posting Name is Compare Identical Database Tables.  It
was posted on 12/24/99 and has a 4 1/2 star user rating (I agree).
Hope this helps anyone who needs such a tool.
Regards,
    Michael Murphy



Quote:
> Hi,
> Looking for some pointers on how to compare the tables, fields, and field
> propeties of two databases.  I am pretty sure you can do something like
this
> using ADOX, but have yet to find a good example.  Sometime the development
> and production database structures get out of sync and to be able to run a
> routine sure would be nice!   Any help would be appreciated.
> Thanks,
>     Michael Murphy




Sat, 17 Aug 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. How to Compare Database Tables, Fields, and Properties

2. PDOX4.5--Comparing fields between records (except ONE field)

3. Comparing a Null field with a char field

4. Comparing field by field

5. Using Field Events to update Field Properties

6. Copying DAO.Field VALUES, as well as the field def.s / properties

7. Fields property for field description?

8. Adding Field to Access db - need to make indexed property of field No Duplicates

9. Comparing Tables: Can you do a join on more than 1 field

10. field compares on table across multiple servers

11. Compare the Fields in the 2 different TABLES in the 2 different DATABASE

12. Compare 2 tables with memo field


 
Powered by phpBB® Forum Software