Database Schema

[vanny]

Has anyone seen any code ASP or SQL (or a combination of both) that allows you to test a current database against a setoff schema's to make sure that the match.

The reason I am asking as in my application i have 100's of seperate "ms access" databases. Basically everytime I add some functionality I have to manually update the tables, however if I could automatically check the database against a template and add/remove etc where necessary that would be good.

And for the time being lets not go down the move to another database system, as currently Im stuck with access until I can afford to move to SQL server. :(

Thanks

[russell]

How are you modifying the databases? Manually? DAO? ADO? Point is, why not keep a seperate database with a table that lists all the other dbs. Then when you need to modify them have a function that loops through and does it programmatically. You can issue Create Table, Alter Table, Drop Table etc commands through DAO or ADO.

Check out tabledef (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/odc_jetdatabaseengine20ausersoverview.asp) as well.

To answer the actual question, i've never seen an MS Access Schema checker, but it shouldn't be too difficult to create your own, as Access exposes all of the properties and methods to check and modify the schema.

[vanny]

Basically I have a template database, which when new objects in the system are create are used, however when I add new functionality I have to update all the old tables.

I am currently using a DSN ADO connection type, (a legacy from the previous programmer) but am in the process of migrating from

oCon.ConnectionString = "DSN=test"

to

oCon.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & strDataFolder & "\convene_login.mdb"

I have some small code used to check if one field is there and use alter table query to update it, I just thought there must be a better way.

Maybe i'll have to write my own com object to do it.

[russell]

Maybe i'll have to write my own com object to do it.
good plan. post back if u need help with the code. you gonna do it in VB? C++?

[vanny]

Unfortunately my C++ is very dodgy, i'm not a fan of OO maybe i started at uni in the wrong language (smalltalk) not by my choice so I will be doing in VB, I may just add the method to my current dll or maybe create another one.