Populating your architecture from Microsoft Access

Navigation: Populating your architecture > Populating from Microsoft products >

Populating from MS Access

You can export your architecture to Microsoft Access to perform general purpose calculations, manipulations or reports on your architecture. You can then import the architecture back into ABACUS.

Note

The Open from Access / Export to Database feature requires that Microsoft Access 2000 or newer is installed on the computer.

To export your architecture to Microsoft Access

1.Ensure you have an ABACUS file open, the Explorer displayed, and an architecture added.

2.Select File | Export | To Database.

3.Take note of the warning dialog as described above and click Yes to continue.

4.Select a location and filename for the database file.

To import the manipulated architecture into ABACUS

1.Ensure you have ABACUS open.

2.Select File | New and select From Access from the dialog that appears and click OK.

3.Browse for the database file you wish to import.

ABACUS’ MS Access Database Schema

ABACUS has at its heart an ontology / meta-metamodel made up of Components, Connections and Constraints. This "CCC" approach was derived from various standard approaches developed from the 1960’s onwards culminating in international standard IEEE 1471, which Avolution’s founders contributed to. The following primitives apply;

1.ABACUS contains Libraries of Standards (or Standards), Component and Connection Types, Viewpoints and many Architectures.

2.Standards, Types and Viewpoints may be logically Grouped for convenience.

3.Architectures may contain many descendant Architectures (or Evolutions).

4.Each Architecture contains many Components and Connections of certain Types and various Views.

5.Components and Connections can be decomposed into Sub-Components and Sub-Connections.

6.Each Component is attached to any number of Connections but each Connection can only be attached to a maximum of two Components.

7.Components and Connections can have Standards with Properties such as Information, Performance, Reliability, Financial etc.

8.Architectures, Components and Connections can have specific Properties such as Information, Performance, Reliability, Financial etc.

9.Views are visual representations of Components and Connections.

The following MS Access Entity Relationship Diagram (ERD) documents the core Architectures, Evolutions, Component/Connection Types (and Groups), Components/Connections and Sub Component/Connection concepts.

The Core database schema

Note

Referential integrity has not been enforced on the relationship between the Components table and the Connections table (for the 'attachment' between a given Source or Sink Component and a Connection). This is because a connection can exist without component attachments. We could have added an 'attachment' table but in the interests of simplicity decided against that. Of course this means that any editing of Component Ids or deleting of Component rows all together will result in incorrect data. ABACUS will handle any incorrectness on import by ignoring the incorrect data.

The following MS Access ERD documents the Properties concepts for Architectures, Component/Connection Types and Components/Connections.

Properties database schema

The following MS Access ERD documents the Standards concept for Architectures, Component/Connection Types and Components/Connections.

Standards database schema

The following MS Access ERD documents the Diagrams concept for Diagrams and their Components/Connections.

Note

You will not be able to re-import diagrams as they were when exported.

Diagram database schema

ABACUS’ MS Access Queries

Included with the MS Access Database are several useful queries as follows;

•Architecture Properties - This query lists all the architectures and their properties in the project.

•Architecture Query - This query lists all the architectures and their basic information in the project.

•Component Properties - This query lists all the components and their properties in the project. Use this query to observe specific properties.

•Component Properties From Standards - This query lists all the components standard properties in the project. Use this query to observe the use of standard properties in the project.

•Component Type Count - This query lists all the component types and includes the number of occurrences of each type. Use this query to determine the popularity of each type in the project.

•Standards Count - This query lists all the standards and includes the number of occurrences of each standard. Use this query to determine the popularity of each standard in the project.

•Standard Properties - This query lists all the standard properties and includes their specific properties. Use this query as a standards catalogue.

A note on working with Memo fields in MS Access

The Components, Connections and Standards Name fields in the tables are defined as memo fields to allow for an unlimited number of characters, as per ABACUS. For the Components and Connections this causes few issues because they also have a unique ID field for referencing, but for the Standards this causes a few problems when trying to join tables because you can’t join memo fields in MS Access. Alternatively, you can set a Where clause criteria to achieve the same effect as shown in the Visual Query Builder and corresponding SQL snippets below that list out all the inherited Standards properties for all the Components in the exported ABACUS project.

Memo field ‘joins’ in Visual Query Builder

SELECT Components.Architecture_Id, Components.Id, Components.Name, ComponentStandardLinks.Name, StandardProperties.Type, StandardProperties.Name, StandardProperties.Value, StandardProperties.Unit

FROM Components INNER JOIN ComponentStandardLinks ON (Components.InternalId = ComponentStandardLinks.Id) AND (Components.Architecture_Id = ComponentStandardLinks.Architecture_Id), Standards INNER JOIN StandardProperties ON Standards.Id = StandardProperties.Standard_Id

WHERE (((ComponentStandardLinks.Name)=[Standards].[Name]));

Memo field ‘joins’ in SQL

A note on automatically generating ABACUS IDs in MS Access

Various ABACUS MS Access tables require a unique long integer ID so a simple VB method is provided to aid in the generation of these. Two methods ResetCounter() and GetNextCounter() are provided within ABACUS' MS Access DB and their use is as follows; ResetCounter() will set the counter back to 0. GetNextCounter() will return the next counter in the sequence.

The methods exploit an MS Access 'quirk' that even if you call the ResetCounter() method for every row insert (i.e. when you also run the GetNextCounter() function) because the ResetCounter() function doesn't have any parameters it just runs it once and therefore doesn't reset the counter for each row insert. And also because of the same 'quirk' you have to pass any old column variable to the GetNextCounter() function to fool it into running each time.

There is also a trick to being able to run the ResetCounter() function in an insert into SQL statement because you cant just have it sitting there as a column on its own, like if it was a select query. It is recommended to make the ResetCounter() call part of one of the other fields, by concatenating it to the end, as it always returns null anyway.

Below is a typical SQL use of the methods to add a list of Component Types from a table/query called 'Component Type List'.

INSERT INTO ComponentTypes ( Id, Name, Description )

SELECT GetNextCounter([Component Type List].name) AS Id, [Component Type List].name AS Name, [Component Type List].description & ResetCounter() AS Description

FROM [Component Type List];

ABACUS ID / Counter generation in SQL