June 27, 2005 at 9:50 am
Hello all,
We are using a local MS-Access database as the backup for the configuration, log, etc... from an application we are developping. The DB is accessed through ADO.NET (OLE driver).
Our problem is that :
- the database schema evolve quite often during the developpement process, and we would like to keep track of those evolutions.
- the data in the database change from one configuration of our soft to another, and we need to test the soft in all configurations : therefore, we must be able to generate easily the various versions of the Access database.
We are looking for an easy way to create/upgrade the Access databases for all developpers, so that evolutions of the DB can be readily integrated with he application source.
I came up with the idea of having a SQL Server 2000 DB holding the schema (all evolutions to the schema being made on this server), then generate the Access base from SQL Server, and use additionnal scripts to fill the Access database with data for various configurations. Unfortunately, I couldn't find an easy way to automaticaly generate an Access DB from an SQL server schema : the creation script created by Enterprise Manager is not Access compatible, and doing the import from Access cannot be easily automated.
Does anyone have experience/feedback/best-practices for this kind of requirements? Thanks in advance
Arnaud
MVP - VC (yep, SQL is not really my speciality... 😉
June 27, 2005 at 10:06 am
Can't you just keep multiple versions of the database within SQL Server and take Access out of the equation? If you script all of the db changes you make, you should be able to roll versions around fairly simply.
A lot of the things SQL Server does cannot be done by Access, even if you do find a way of making SQL Server scripts work in Access (I once wrote something in Excel to attempt this - sorry, don't have it any more).
Regards
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 27, 2005 at 10:10 am
No sorry : Once deployed, our app won't have network access, and it needs a local database. For various (bad) reasons, using anything but Access on the final app is just a no-no.
I planned to use SQL server only during developpement.
Anyway, thanks for the input!
Arnaud
June 27, 2005 at 10:17 am
If you are not going to use SQL Server in production, why use it during development? Surely you're just storing up a big headache for yourself at go-live when you try and downsize to Access and find that features available to you in SQL Server are not available, or are implemented differently, in Access?
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 27, 2005 at 10:33 am
Well, if you have a solution to easily :
- track changes of schema changes made by various people and propagates them to all developpers,
- regenerate a "clean" database with controlled content at any time from this schema,
... all of this using only Access, I'll be really glad to use it! Our DB is relatively simple and we don't need any of the SQl Server specific functionnalities : I planned to use it merely as a central schema repository, wich could keep track of it' changes.
Arnaud
June 27, 2005 at 2:27 pm
If those changes are performed from a data modeling tool you should have no problem!
ex:
If you use VISIO for example to model the db schema you can at later time compare your model with what the DB has and easyly synch one or the other. The key is the MODELING TOOL!
hth
* Noel
June 28, 2005 at 7:37 am
Why not use MSDE on the disconnected machines (free lite version of SQL)? You can have them run scripts to update their local schema as it changes....
June 29, 2005 at 7:25 am
The MSDE is a good tool and will run locally.
But, what you want to do is really simple in Access. Open Access, create a new database. Create an ODBC connection to teh SQL Server. Import the recent schema and data into this new database. Now, you have everything from SQL Server.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply