Forward engeneering to MSAccess

[mcastagnaa]

I'm pretty much sure this topic has been covered elsewhere but I searched the forum for a while without an answer.
I have my beautifully crafted EA data model and I'd like to export it to MSAccess. I understand I have to use the Generate DLL function but then I'm stuck with the .SQL file it generates ... what do I have to do in Access??
Tks

[thomaskilian]

Execute the SQL?

[mcastagnaa]

I was expecting something like that actually and that was what I tried to do. Problem is I'm receiving error messages and chance is I'm executing that SQL in the wrong place (I opened a query design and pasted the SQL code there, press the execute button and, saddly, receving an error message) or I'm building that code the wrong way. Find Below a piece of that code I'm trying to use

CREATE TABLE tblEntityRoleEnum (
EntityRoleCode Text(10) NOT NULL,
EntityRoleDescription Text(50)
) ;

CREATE TABLE tblFund (
ShortName Text(20) NOT NULL,
LegalName Text(100),
LegalFundEntity Text(20),
IsManaged YesNo,
IsPlatform YesNo,
StrategyFields Integer,
SubStrategyByPC YesNo,
MainStrategyByPC YesNo,
Administrator Text(10)
) ;

CREATE TABLE tblFundMapLegalEntity (
FundShortName Text(15),
EntityCode Text(10),
InvolvedInstrumentGroup Text(5),
LegalEntityRole Text(10)
) ;

Should that work the way I mentioned above?

Tks

[«Midnight»]

Are you submitting the your code as separate statements, or all at once.

I don't think Access will accept more than a single statement at one time. If you have not done so, you will need to break the code into individual table definitions etc. Consider writing a short Access program (perhaps a quick bit of VBA) to read, parse and submit the statements.

[thomaskilian]

David might be right. You should try to execute one CREATE after the other and of course the error message would be helpful in order to trace the cause

[mcastagnaa]

Yup ... solution is to separate the SQL statements and, maybe, use them in a VBA module like this one I used:

Public Sub CreateDB()
   DoCmd.RunSQL ("sql statement 1")
   DoCmd.RunSQL ("sql statement 2")
   ...
End Sub

No that handy I would say: just imagine a serious database structure with lot of CREATE TABLE , lot of ALTER TABLE and so on ... I wonder if it's possible to create a better forward engeneering to MS Access .... :)

[Paolo F Cantoni]

Yup ... solution is to separate the SQL statements and, maybe, use them in a VBA module like this one I used:

Public Sub CreateDB()
    DoCmd.RunSQL ("sql statement 1")
    DoCmd.RunSQL ("sql statement 2")
    ...
End Sub

No that handy I would say: just imagine a serious database structure with lot of CREATE TABLE , lot of ALTER TABLE and so on ... I wonder if it's possible to create a better forward engineering to MS Access .... :)

FWIW,
Embarcadero ER/Studio actually writes a VBA program which you execute as an Access module.

For the usual marketing reasons, Microsoft decided it should make it REALLY hard to talk to MS Access via SQL scripts.

(And this is from an ACCESS devotee - a much underrated RDB!)

HTH,
Paolo

[mcastagnaa]

Tks all!
I'll give Embarcadero a try. I'm using MSAccess as a test device for stuff that our developers will deploy on SQL Server. I'm a humble analyst at this stage and I can't pretend to reach those peaks ...  

[«Midnight»]

It sould not be too difficult to write a short program that would break up the output file from EA and execute the SQL statements one at a time.

I did this kind of thing some time ago with complete success. This was before my EA days (around 2000 or 2001 I think), but the imput file was from a tool that produced SQL for a variety of platforms, including Access, always as a single text file.

Can't remember exactly what I did, but it strike me that if you search for unquoted semicolons you should have the commands broken out. Next, submit them one at a time through some kind of command interface. I think that if you go through OLEDB or later you will want to strip the semicolon off the end of each command before you send it through.

You could run into two problems, neither a show-stopper if you are prepared:
- Access has a lot of reserved words, and will do the strangest things if you use one in a name; often there will be no specific error code or message when this occurs through a command interface, and sometimes no error is flagged. Have EA escape all names with [] if you can, otherwise write a short text script to handle it. Names appear in very regular patterns, so you should be OK.
- Some command interfaces won't let you pass all Access commands through - you are essentially talking to the interface engine rather than the Access engine. In this case you should be able to have access accept the command as a query definition; you can then make access run the query. With this method you should create a single 'scratch' query object and rebuild it with each command, then delete it when you are done. This avoids having a lot of garbage left over at the end.

I don't have the old code handy, but I do remenber it was quite successful. I was using ADODB and ADOX for the application. One thing I liked was that I could read any of the supported SQL dialects (I think there were 5 or 6), connect to the appropriate engine, and fire off the commands. This allowed building a schema on the fly. [We then built data from various sources and went straight into loading it up. You will have other uses] Bottom line was that we paid back the time invested almost immediately.

[Paolo F Cantoni]

Tks all!
I'll give Embarcadero a try. I'm using MSAccess as a test device for stuff that our developers will deploy on SQL Server. I'm a humble analyst at this stage and I can't pretend to reach those peaks ...  

I'd only recommend that if you have many thousand dollars to spare... (per seat!)

Also, it may not work that way any more.  I don't think they saw MSAccess as a big market.

Also, ER/studio is an Entity Relationship modeller - not UML.

Paolo

[thomaskilian]

Tks all!
I'll give Embarcadero a try. I'm using MSAccess as a test device for stuff that our developers will deploy on SQL Server. I'm a humble analyst at this stage and I can't pretend to reach those peaks ...  

Can't you use SQL Server on your machine? There's also a lite version I use on my workstation. It's just limited in performance (you're the only user anyway). Sou you have the same base as your developers. Why bother with MS Access?