MS Access Generation

[fballem]

As far as I can tell (having looked for several hours), Microsoft Access cannot use a DDL Script to make changes to the structure of the Database. There is a facility to DDL, but it executes one command at a time.

Given the expertise that exists within Sparx, I wonder if it is possible to create an Addin to EA that will take a DDL script from a Database model in EA and create/modify the structure of an MS Access database.

I have no objection if we have to create an empty Access database to start the process, but it would be nice if we could use the DDL Script that is generated to manage the database.

Thanks

[sargasso]

Strange as it may seem, this is not a trivial exercise.  

It is fairly easy to create a script or VBA or whatever that will loop through a run DDL/check result process for a list of DDL queries.  However, generation of the list in the correct order is the problem.

I have been working with Access since v1.0 and have yet to come across an external tool that gets the order correct every time.  (This includes heavyweights such as Embardero and LogicWorks - the latter of which gets it right 96% of the time)

Nor am I aware of a published algorithm for parsing a data model and deciding the optimal creation sequence.

regards
bruce

[Paolo F Cantoni]

Strange as it may seem, this is not a trivial exercise.  

It is fairly easy to create a script or VBA or whatever that will loop through a run DDL/check result process for a list of DDL queries.  However, generation of the list in the correct order is the problem.

I have been working with Access since v1.0 and have yet to come across an external tool that gets the order correct every time.  (This includes heavyweights such as Embarcadero and LogicWorks - the latter of which gets it right 96% of the time)

Nor am I aware of a published algorithm for parsing a data model and deciding the optimal creation sequence.

regards
bruce

I will echo bruce's thoughts here.  I, too, have been using MS Access since v1 and part of the problem is that since you can create cascade loops; in those situations, you can't come up with any sequence that will work.

My solution has been to create the raw tables, add any reference data (I use a theory called Exception Values to prepopulate tables with NULL replacements - so I NEVER have NULLs (well virtually never) in my joins), then add the foreign key relationships.

The problem isn't restricted to MS Access.  Any RDBMS that properly implements cascade update and cascade delete will suffer from this problem.  It's just that MS Access has done this properly since v1.0  .

Paolo

[fballem]

So tell me why the DDL works with Oracle, SQLServer, and others. The problem with Access is that the DDL facility that they provide will execute only a single command at a time, as opposed to the others that will allow the DDL script to be executed in sequence.

What is needed, for Access is a 'parser' that will read the DDL in order, including comments, and execute the DDL script one statement at a time.

Since EA uses Access as its database, I had thought that they might have the experience and expertise to execute the DDL Script.

[Paolo F Cantoni]

So tell me why the DDL works with Oracle, SQL Server, and others. The problem with Access is that the DDL facility that they provide will execute only a single command at a time, as opposed to the others that will allow the DDL script to be executed in sequence.

What is needed, for Access is a 'parser' that will read the DDL in order, including comments, and execute the DDL script one statement at a time.

Since EA uses Access as its database, I had thought that they might have the experience and expertise to execute the DDL Script.

Yes, the lack of a DDL script engine is a problem for MS Access.  But, I guess for most of us during most of MS Access's lifetime, its been the best MS Access Modelling tool around!

In fact, I used it to model my SQL Server DB, I wrote some code that would validate the Access model and only then automatically upsize to SQL Server.

It's only recently that UML profiles for RDBMS have been even moderately useful.  Nevertheless, the points that bruce and I have made are still relevant.  If you use cascading in your RDBMS designs (and you have loops) then ALL of the DDLs will fail unless you use (somethng like) the techniques I outlined.

I don't know the problem will be solved anytime soon.  It is interesting to note that Embarcadero ER Studio, mentioned by bruce, did NOT elect to take the route you mentioned.  They actually emit a VBA script (or at least they used to - bruce do you know any better?) that builds the DB in MS Access - from the physical model.

Sorry that this doesn't help, but it maybe worth understanding the nature of the problem better.

Paolo

[fballem]

Thanks

[wsargent]

I have a perl script that will resort tables in the correct order.  It works for Solid, but could be easily tweaked to work with Oracle or Access.

Try this URL:

http://tersesystems.com/post/5400058.jhtml

Will.