RE: MSAccess
RE: MSAccess
- Subject: RE: MSAccess
- From: "Kieran Kelleher" <email@hidden>
- Date: Mon, 12 May 2003 12:38:50 -0400
Chuck .... you are correct!! I was thinking narrow-mindedly of the MS Access
integration with MySQL.
Romeo, I recommend MySQL, however your Primary Key strategy needs to be
thought out based on the usage scenario. Our company is implementing a
similar architecture. (MySQL, WebObjects, and MS Access/VBA/ODBC for
'legacy' support. Here is our plan)
If a database being used to deliver read-only information to Web via
WebObjects, then you can use MySQL auto_increment on tables and have pretty
seamless connectivity from MS Access using ODBC.
If WebObjects is INSERTing data and MS Access is needed for reporting only,
then use WebObjects in the standard way with no AUTO_INCREMENT primary key
fields and create a user/password just for linking tables to MS Access
ensuring that there is no INSERT privileges (SELECT, DELETE and UPDATE
permitted)
If both WebObjects and MS Access are doing INSERTing, then you have to
conform to the EO_PK_TABLE method for generating primary keys and have
INSERTions done by custom VBA code utilities from the MS Access side.
Basically, if you are going to INSERT 7 rows into table MYTABLE, in code you
do the following:
1) LOCK the EO_PK_TABLE
2) retrieve the last primary key value used for MYTABLE from the
EO_PK_TABLE, let's say 'x'
3) UPDATE the PK value for MYTABLE in EO_PK_TABLE by adding 7 to it. (This
reserves these PK numbers for your INSERTs)
4) release LOCK on the EO_PK_TABLE so it is now available for WebObjects to
use it the next time it saves the object graph
5) INSERT your 7 rows into MYTABLE one at a time using the unique primary
keys from 'x+1 thru 'x+7'
On a final note, decide carefully on the MS Access implementation as to
whether the users are knowledgeable enough to work directly with your
WebObjects managed tables without messing up the integrity of data. You may
want to have SELECT only user/password for reporting or something like that,
or else custom password-protected Visual Basic utilities using embedded
user/password to do other tasks from Access.
Giving users "back door" full connectivity from 'Access' to the database
after using WebObjects EO Framework to elegantly manage and remove the
complexity of the database from your code is like giving the keys of your
BMW 745i to your 13 year old son as you leave for the weekend with your wife
saying "Son, only use these keys to get your football from the trunk of the
car if you need to" ;-)
Kieran
-----Original Message-----
From: email@hidden
[mailto:email@hidden]On Behalf Of Chuck Hill
Sent: Saturday, May 10, 2003 1:12 PM
To: email@hidden; 'Romeo Mariani'
Cc: email@hidden
Subject: RE: MSAccess
Is that going to work? I don't think that EOF uses the AUTO_INCREMENT
feature for PK generation. If EOF does not use it and MSAccess does then
you are going to somehow need to ensure that they generate PKs in different
parts of the possible range so as to avoid PK conflicts.
Chuck
At 11:55 AM 10/05/2003 -0400, Kieran Kelleher wrote:
> Seriously though the MyODBC connector works great with Ms Access. I am
>planning a solution where WO is the app, MySQL is the enterprise DB and Ms
>Access can link to all the tables using the MyODBC connector. If you use
the
>AUTO_INCREMENT feature of MySQL, you can link to the tables from Access and
>treat the tables like MS Access tables. Even when you use MS Access APPEND
>of multiple rows, it works because MySQL creates the auto_increment primary
>keys on the fly. Your customer can use Access to do reports, etc.
>
>Kieran
>
>-----Original Message-----
>From: Romeo Mariani [mailto:email@hidden]
>Sent: Friday, May 09, 2003 6:01 PM
>To: email@hidden
>Cc: email@hidden
>Subject: Re: MSAccess
>
>
>Kieran, thank you so much.
>
>Unfortunately I'm not free to use another db like MySql or FrontBase.
>It's a "political" must. Normally I use WO on OsX with FrontBase for my
>projects.
>A win customer "need" to have MSAcces like db.
>
>So you think there is no escape?
>
>Romeo
>
>> Romeo, I would strongly advise you not to use MS Access even if you
>> get it
>> working for the following reasons
>>
>> 1) "The version of SQL created by Microsoft is called Jet SQL and it
>> is the
>> database engine behind Microsoft's Access.
>>
>> Jet SQL has certain limitations compared to the other versions of SQL.
>> In
>> general, Jet SQL is not designed to manage a database, but rather, it
>> is
>> used to retrieve information from a database"
>> --
>> http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html
>>
>> 2) It is not a supported configuration for WebObjects:
>> http://docs.info.apple.com/article.html?artnum=72598
>>
>> I recommend you try MySQL which is FREE (www.mysql.com), move your
>> Access
>> tables to MySQL and then use WebObjects for Web-based applications to
>> the
>> data and you can still link to the MySQL tables from directly within MS
>> Access for internal legacy Access applications using the MyODBC
>> connector,
>> also FREE at www.mysql.com
>_______________________________________________
>webobjects-dev mailing list | email@hidden
>Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/webobjects-dev
>Do not post admin requests to the list. They will be ignored.
>
--
Chuck Hill email@hidden
Global Village Consulting Inc. http://www.global-village.net
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.