Questions on developing application using JETand/or MSDE

Our organization is developing a data acquisition application which requires an RDBMS. Because the requirements were originally somewhat unknown and/or fluid, we elected to use the Informix Dynamic Server (IDS)-Workgroup engine. Now that the requirement are firming up, we are seriously considering switching to the Microsoft Access engine. A few of these firmer/new requirements arre: - Zero database administration. - Fairly small capacity (probably less than 2GB) - Limited number of users...just the application threads while the program is executing. One to five concurrent users at other times. It seems the Microsoft Access Engine "meets" these requirements perfectly... what do you think? Also, I'd appreciate your comments/answers to the following. - I was told by a developer that the Microsoft Jet engine is freely distributable? Is this true? Can I use these engines to develop & distribute my own data acquisition & storage applications? Do you just go to the Microsoft WWW site, download and distribute it? - I've read that Microsoft is distributing 2 engines with Access 2000... one being Jet 4.0 and the other MSDE. Will the MSDE be freely distributable... similar to Jet? - Based on the limited information I've found (and assuming it is either free or not to costly), it seems that MSDE is the better choice for new development. It provides scalability to the Micorsoft 7.0 SQL server and may eventually replace Jet. Any comments? Thanks in advance, Eddie Roberts

1) There is no such thing as a RDBMS with "zero database administration". Anyone who thinks otherwise is a kook IMHO. Get used to it... no matter what system you have, someone will need to keep an eye on it and maintain it to some degree or another. 2) Yes, Jet is freely _distributable_ if you have the Office Developer's Edition. This is a long way from saying that it is free in and of itself. If you buy ODE you will be able to distribute a runtime version of Access/Jet royalty free. 3) Yes, Access2K will have the Jet engine and a scaled down version of SQL Server. Others in this ng know more of the details though. 4) Depending on your usage, you may be pushing your luck with Access once you get more than a couple hundred MB's in db size. If you are looking at going over 1GB and maybe kissing 2GB I would submit that SQL Server might give you better performance. HTH. Abbot Cooper cooper_NoSpam_ab@mediaone.net (remove "_NoSpam_" from address when sending) Eddie <edward.roberts@noaa.gov> wrote in article <36b0797f.75602871@news.rdc.noaa.gov>... > Our organization is developing a data acquisition application which > requires an RDBMS. Because the requirements were originally somewhat > unknown and/or fluid, we elected to use the Informix Dynamic Server > (IDS)-Workgroup engine. Now that the requirement are firming up, we > are seriously considering switching to the Microsoft Access engine. A > few of these firmer/new requirements arre: > > - Zero database administration. > - Fairly small capacity (probably less than 2GB) > - Limited number of users...just the application threads > while the program is executing. One to five > concurrent users at other times. > > It seems the Microsoft Access Engine "meets" these requirements > perfectly... what do you think? Also, I'd appreciate your > comments/answers to the following. > > - I was told by a developer that the Microsoft Jet engine is freely > > distributable? Is this true? Can I use these engines to develop & > distribute my own data acquisition & storage applications? Do you > > just go to the Microsoft WWW site, download and distribute it? > > - I've read that Microsoft is distributing 2 engines with Access > 2000... one being Jet 4.0 and the other MSDE. Will the MSDE be > freely distributable... similar to Jet? > > - Based on the limited information I've found (and assuming it is > either free or not to costly), it seems that MSDE is the better > choice for new development. It provides scalability to the > Micorsoft 7.0 SQL server and may eventually replace Jet. Any > comments? > > Thanks in advance, > Eddie Roberts >

Replies In-line: ------------------- Eddie wrote in message <36b0797f.75602871@news.rdc.noaa.gov>... >Our organization is developing a data acquisition application which >requires an RDBMS. Because the requirements were originally somewhat >unknown and/or fluid, we elected to use the Informix Dynamic Server >(IDS)-Workgroup engine. Now that the requirement are firming up, we >are seriously considering switching to the Microsoft Access engine. A >few of these firmer/new requirements arre: > > - Zero database administration. No such thing - The laws of the universe declare that "Enreopy" rules ! However designed properly maintenance is at a minimum and easily done. > - Fairly small capacity (probably less than 2GB) Max on Access97 is 1 Gb, Acc00 2 Gb, However performance will probably become unacceptable long before these limits are reached - Highly dependant on the *what* and *How* you are doing. > - Limited number of users...just the application threads > while the program is executing. One to five > concurrent users at other times. > Should be fine. >It seems the Microsoft Access Engine "meets" these requirements >perfectly... what do you think? Also, I'd appreciate your >comments/answers to the following. > > - I was told by a developer that the Microsoft Jet engine is freely > distributable? Is this true? Can I use these engines to develop & > distribute my own data acquisition & storage applications? Do you > just go to the Microsoft WWW site, download and distribute it? No. You need to buy the Office Developers Edition. This then allows you to create and distribute lisence free applications > - I've read that Microsoft is distributing 2 engines with Access > 2000... one being Jet 4.0 and the other MSDE. Will the MSDE be > freely distributable... similar to Jet? Unknown as yet, however as it relies on SQL Server7 Lite, it appears unlikely. ( from what the MS People told me before Xmas ) > - Based on the limited information I've found (and assuming it is > either free or not to costly), it seems that MSDE is the better > choice for new development. It provides scalability to the > Micorsoft 7.0 SQL server and may eventually replace Jet. Any > comments? Doubtfull it will replace Jet, ( I believe Jet is being built into things like Outlook ?) but certainly MS is pushing for COM directly to SQL Server, bypassing Jet for database applications. - However I don't believe that you'd want to bet your business on MSDE 'till it's been well tested ( and the first Service pack released :-). - perhaps a Jan 2000 rollout ? - Just because its *New* doesn't mean its better, or that you *have* to deploy it. - Develope in Access 97 with the upgrade path in mind and you should be able to upsize fairly painlessly. HTH Henry Craven ------------------- H_Craven@bigpond.com ---------------------------------------------------------------------------- ------------- >Thanks in advance, >Eddie Roberts

Eddie, Without more info it is a bit hard to be sure, but the parameters you quote are reasonable for Access. My only eyebrow-raise was at the '2Gb' mark - I personally have no applications at this capacity, so I cannot offer qualified comment, but I do have apps running at 50Mb+ without problems and other correspondents to this ng talk of hundreds of Mb. Access97 and earlier are not true client/server: you can store the data tables on a server and link them to a front end application, but the front-end does all the grunt work of running queries etc, so you end up transferring more data over the network that you would with a true c/s engine like SQL Server. This is usually not a problem for the sort of Apps which are typically written in Access, but your mileage may vary. As you say, the upside is the vastly reduced sysadmin requirements. I cannot comment on A2000, except that it is not released yet, so planning would be a bit hazy - no-one knows what will be in the final release. HTH DOug Eddie wrote in message <36b0797f.75602871@news.rdc.noaa.gov>... >Our organization is developing a data acquisition application which >requires an RDBMS. Because the requirements were originally somewhat >unknown and/or fluid, we elected to use the Informix Dynamic Server >(IDS)-Workgroup engine. Now that the requirement are firming up, we >are seriously considering switching to the Microsoft Access engine. A >few of these firmer/new requirements arre: > > - Zero database administration. > - Fairly small capacity (probably less than 2GB) > - Limited number of users...just the application threads > while the program is executing. One to five > concurrent users at other times. > >It seems the Microsoft Access Engine "meets" these requirements >perfectly... what do you think? Also, I'd appreciate your >comments/answers to the following. > > - I was told by a developer that the Microsoft Jet engine is freely > > distributable? Is this true? Can I use these engines to develop & > distribute my own data acquisition & storage applications? Do you > > just go to the Microsoft WWW site, download and distribute it? > > - I've read that Microsoft is distributing 2 engines with Access > 2000... one being Jet 4.0 and the other MSDE. Will the MSDE be > freely distributable... similar to Jet? > > - Based on the limited information I've found (and assuming it is > either free or not to costly), it seems that MSDE is the better > choice for new development. It provides scalability to the > Micorsoft 7.0 SQL server and may eventually replace Jet. Any > comments? > >Thanks in advance, >Eddie Roberts

Abbot Cooper (cooper_NoSpam_ab@mediaone.net) wrote: : 1) There is no such thing as a RDBMS with "zero database administration". : Anyone who thinks otherwise is a kook IMHO. Get used to it... no matter : what system you have, someone will need to keep an eye on it and maintain : it to some degree or another. This was driven home for me two days ago. I had built a set of compact and repair tools for a client of mine, and set up a series of procedures for them to follow to maintain their replicated backend data. Well, guess what? A worstation crashed and took down the main data file. When they compacted: 1. they didn't use my custom built tools, which back up the file before compacting it (so that I could diagnose, or roll back) 2. they didn't check the compacted file to insure that it was still replicable (one of the defined procedures) 3. they didn't run through the checklist of morning "server" duties (check that the overnight backup ran successfully; check that the main data file is still replicable after the overnight compact process; run morning synchronization with branch office; use administrative tools to check for conflicts) Now, it takes less than 15 minutes a day to keep things running smoothly. But when they don't do all of those things, they end up paying me for 6 hours of work transferring the data updated in the non-replicable file into a valid replica. Nice little chunk of pocket change that I wasn't expecting. [snip] : 4) Depending on your usage, you may be pushing your luck with Access once : you get more than a couple hundred MB's in db size. If you are looking at : going over 1GB and maybe kissing 2GB I would submit that SQL Server might : give you better performance. Two gigs in Access requires multiple back end MDB files. Perhaps the architecture of the app would work quite well with this. Perhaps not.

David W. Fenton <dXXXfenton@bway.net> wrote in article <nIas2.743$xU1.1876@news6.ispnews.com>... > Abbot Cooper (cooper_NoSpam_ab@mediaone.net) wrote: > : 1) There is no such thing as a RDBMS with "zero database administration". > : Anyone who thinks otherwise is a kook IMHO. Get used to it... no matter > : what system you have, someone will need to keep an eye on it and maintain > : it to some degree or another. > > This was driven home for me two days ago. I had built a set of compact > and repair tools for a client of mine, and set up a series of procedures > for them to follow to maintain their replicated backend data. Well, guess > what? A worstation crashed and took down the main data file. When they > compacted: > > 1. they didn't use my custom built tools, which back up the file > before compacting it (so that I could diagnose, or roll back) I have this same issue (or at least the potential for it) with one of my apps. Fortunately it has not needed a repair yet (after 9 months), but I am wary of the issue. Do you know of any way to prevent people from compacting using the standard Jet method? I want to avoid going through what you did by forcing them to use my routine. Any ideas? <SNIP> > > : 4) Depending on your usage, you may be pushing your luck with Access once > : you get more than a couple hundred MB's in db size. If you are looking at > : going over 1GB and maybe kissing 2GB I would submit that SQL Server might > : give you better performance. > > Two gigs in Access requires multiple back end MDB files. Perhaps the > architecture of the app would work quite well with this. Perhaps not. > I think the original poster was referring to A2K, which allows up to 2GB. Abbot Cooper cooper_NoSpam_ab@mediaone.net (remove "_NoSpam_" from address when sending)

Abbot Cooper (cooper_NoSpam_ab@mediaone.net) wrote: : David W. Fenton <dXXXfenton@bway.net> wrote in article : <nIas2.743$xU1.1876@news6.ispnews.com>... : > 1. they didn't use my custom built tools, which back up the file : > before compacting it (so that I could diagnose, or roll back) : : I have this same issue (or at least the potential for it) with one of my : apps. Fortunately it has not needed a repair yet (after 9 months), but I am : wary of the issue. Do you know of any way to prevent people from compacting : using the standard Jet method? I want to avoid going through what you did : by forcing them to use my routine. Any ideas? Keep a connection to it always open on the server, and have your custom routine shut down that connection? : > : 4) Depending on your usage, you may be pushing your luck with Access : once : > : you get more than a couple hundred MB's in db size. If you are looking : at : > : going over 1GB and maybe kissing 2GB I would submit that SQL Server : might : > : give you better performance. : > : > Two gigs in Access requires multiple back end MDB files. Perhaps the : > architecture of the app would work quite well with this. Perhaps not. : : I think the original poster was referring to A2K, which allows up to 2GB. If you're app is likely to hit the upper limit, even of A2K, I'd say you should definitely be looking at a different back end. -- David W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc

Eddie wrote: > Our organization is developing a data acquisition application which > requires an RDBMS. Because the requirements were originally somewhat > unknown and/or fluid, we elected to use the Informix Dynamic Server > (IDS)-Workgroup engine. Now that the requirement are firming up, we > are seriously considering switching to the Microsoft Access engine. A > few of these firmer/new requirements arre: > > - Zero database administration. > - Fairly small capacity (probably less than 2GB) > - Limited number of users...just the application threads > while the program is executing. One to five > concurrent users at other times. > > It seems the Microsoft Access Engine "meets" these requirements > perfectly... what do you think? Also, I'd appreciate your > comments/answers to the following. Don't know about Access, but I'd say Informix SE (Standard Engine) meets these requirements perfectly, and much better than IDS (much as I love IDS :-) From what I've seen of Access, I'd say it's very limiting, but I'm no expert in Access. June