Database as stand alone

[emillard]

Looking for some advice on Access and VB.

I have been working with a local non-profit affiliate on the development of a database to automate one of the clinical programs. We finally have a finished product – using a front end/back end – with multiple users entering simultaneously. We have been beta testing with volunteers and all seems to be going well.

My concerns are…
First, as I have been looking around the web at various forums, tech sites and Microsoft access is often referred to as a “toy”, a personal vs. business application, a limited db lacking in power and stability. I do not want to implement this complete change-over from the current manual system to automated if MS Access can’t handle it.

Second, the project has grown from almost a “let’s see if we could do this” to now having the National parent organization showing serious interest in the database. That itself has brought in issues of the capabilities of Access, creating the database as a stand-alone, copyrighting the database, etc.

The only VB experience I have is with access, which I believe is not actually VB but VBA? Many forums have mentioned creating the database in VB, which is more of a powerhouse and stable format. Some have mentioned using the Office XP Microsoft Office XP 2002 Developer Edition. I do not want to mislead this organization or on the other hand – restrict any possibilities based on my limited knowledge.

Thanks for your time helping out this amateur.

 

[Mile-O]

Many forums have mentioned creating the database in VB, which is more of a powerhouse and stable format.

The FrontEnd or simulating a backend with Collections and/or the Dictionary Object?

 

[emillard]

**The FrontEnd or simulating a backend with Collections and/or the Dictionary Object?**

Sorry, not following you with this question. If this refers to my using a "front end/back end" - I used the database splitter to create a front end and back end. The front end is installed on all volunteer laptops, while the back end holds the tables on the main laptop/server.

 

[Mile-O]

I was referring to this: " creating the database in VB.

Did the people you have conferred with suggest creating the front end in VB and interfacing, when necessary, with a backend database; or were they suggesting you simulate a complete database (frontend and backend) with Visual Basic?

 

[emillard]

Oh sorry, some of the discussion was way beyond what I am familiar with... but several did mention creating the front end with
VB and manage the backend/table with access.

 

[Pat Hartman]

Let's start with the Access is a "toy" issue. If you drive a Lamborgini, you might be a little snobbish and view a Corvette as a toy. The Corvette driver, might consider my 1967 Austin Healey Sprite to be a "toy". It is simply a matter of perspective. You would never use an Access front-end if you had thousands of users and you would never use an Access back-end if you had millions of rows of data and a VB fe with an Access (Jet) be is just about the worst solution possible.

Access is an excellent solution for an application of this type because it is upsizable. If you write your Access fe with the assumption that at some point your be will need to be "upsized" to a "real" database such as DB2 or Oracle, you will be well placed. An Access fe seems to bog down when you have more than 25 simultaneous users even though you can technically have 255. The solution is to create more instances of the fe or to distribute individual copies of the fe to each user's desktop. Running the fe locally is the preferred method anyway. If you have used professional naming standards and followed the guidelines for optimizing Access fe's linked to ODBC be's, you will find it almost no work to simply swap your Jet be for a database server be. Every Access db that I design is built this way and I have never had a problem swapping a "real" db for my Jet be. And I often go back and forth to make working at home easy. I relink my fe to a Jet be to use at home and then bring it back to the office and link it back with the production Oracle, DB2, or SQL server be. Try that with a VB front-end.

Distributing an Access fe to users who do not have a copy of the correct Access version installed on their PC will require the purchase of the Office Developer (or whatever Microsoft is calling it these days). The developer will allow you to package your Access database with a copy of the runtime version of Access. The runtime version of Access will allow a user to run an existing db but not create a new one or modify any objects in an existing one. That does not secure your application though, if you need security, you need to implement it within your db.

 

[emillard]

I appreciate your comments on Access – I’m glad to hear someone stand behind Access databases. I have always been completely satisfied with the performance and function of Access databases, although those I’ve created have been relatively small and used in one location. My only concern with this current project is the possible nationwide distribution. I wanted to make sure this one would stand up over time.

If you do not mind, as a newbie on my quest for learning, I have some questions about what you wrote.
=> Using the Database Splitter I created the front end/back end. I installed the FE on each laptop and linked the table to the BE on the main laptop/server. Sounds like I’ve got that in place correctly.
=> You mentioned using professional naming standards and following the guidelines for optimizing Access fe’s linked to ODBC be’s. Could you point me to a resource that I could learn more about both the naming standards and optimizing guidelines. (And what by the way is “ODBC” be’s? Thanks) Also when you use Jet be is that interchangeable for Access be?
=>I will look into security issues, I guess I thought using the Office Developer would cover more that it actually does. Thanks for your comments.

Thanks again for helping this beginner.

 

[Pat Hartman]

Here's a link to the Microsoft kb. You can search there for the articles on optimizing your db for client/server.

;kbhowto]Microsoft Knowledgebase

ODBC = Open Database Connectivity. It is the method we use to link to back end tables in non-Access databases such as SQL Server, Oracle, DB2.

Jet is technically the database portion of Access. An application written in VB, C++, ASP, etc. can use an Access database to manage their data tables. Jet is what the database management part of Access is called. Access is only required to design/run your fe database. The be can be used by any application capable of using ODBC and/or ADO/DAO to access the data. Jet manages all database access, record locking, and security. That's why your databases are still secure even when a non-Access application uses them. Jet is nowhere near as sophisticated as a real database server such as Oracle, etc. and that is part of the reason that some technical people think of Access as a toy. They equate Access as a desktop development environment with the limitations of Jet and lump the two together. I try to refer to the tables of an Access db as being Jet tables because I believe that it is important to understand the distinction. The graphical user interface and application design enviornment is not hopelessly tied to Jet tables as the snobs would have you believe. An Access fe is quite viable as an fe to any so called "real" database be.

Before embarking on any security, make sure you have several backups in safe places and read VERY CAREFULLY the Security FAQ that you can download from the kb. Also search here for posts of people who didn't follow that advice and jumped in without adequate knowledge.

 

[Pallooma2]

Just to clarify then...

From what I had read in this thread, I gather that Access is a perfectly capable db package (this I already knew), for providing a small number of network users a solid fe/be database. although it is necessary for all users to have the correct ver of access inst. on their pc/laptop.

Unless...

The 'developer' has the 'Developer' version of Access. in which case a cut down version is shipped with the completed db.

Therefore...

If I want to get a little bit comercial and sell my apps. for a small amout of cash the best way to go would be to pick up Access 2002 dev?

Am I right or am I wrong?

Right... Wrong

Eh?

which is it?

Thnx
Ian

 

[Kraj]

This link has a list of the most commonly used naming conventions that can help with the "professional naming standards" Pat mentioned.

http://www.mvps.org/access/general/gen0012.htm

 

[Pat Hartman]

Do a search here and read the various posts regarding the Developer's edition. Also read the articles in the knowledge base. Depending on the market your apps target, you may not need to worry about distributing a copy of the Access runtime with your app. Don't hold me to this, but I think that newer versions of Office may come with the Access runtime included. Check with Microsoft. Also if your clients are likely to already have a retail version of Access, they can use that. The only problem then becomes one of version. Therefore, unless you need some new feature of Access, distributing your .mde in the oldest version that will support your app will get you to the widest audience without difficulty.

 

[Kevin_S]

Pallooma2/Others

2 quick comments:

1 - If you going to sell and access db solution you will need developers edition to package the app with the appropriate version of Access that the db was created in. Now I know some Access loyalists will scoff at this idea and say "well if the place your making the app for uses 2000 then I can just make it in 2000!" well... this is true but not very professional. Its not professional because when the company that bought your product changes OS or MS Office to a different version now can't open your app and has to either buy new or pay to have it converted and tested. (If you package the app with a runtime version this is no longer an issue HOWEVER - check out the runtime version BEFORE YOU BUY as you do lose some functionality with the runtime... mostly ActiveX gets a little wirely)

2 - Most of time (especially on forums as these) you will have MS Access die-hards saying "Access is the best ever! down with VB" or "lets see 'em do this with VB!!!" ...or... on the other side VB die-hards will say "Access is a toy" or "is not feasible for real business applications" Truth is - its a matter of perception and with anything there is a proper solution for each individual given problem. Access has many, many positive features and is a wonder RAD tool. (RAD = Rapid Application Development) As for the interest in size of Applications with Access I recently rolled out a split FE/BE app that has over 150 client interfaces accessing network connection ranging from T1 lines to dial up connections at 16kps all connecting to a SQL Server BE here on our servers. This app works without a hiccup. We also develop apps in VB (and of recent VB.NET) for applications we share with partners who are not on our network/ don't have same OS/ and some apps in VB that are used as disconnected rs that are taken into the field and then pushed back to our central servers...

All I'm trying to say is that you should base your chosen development tools based on the required development environment and business need... Sometimes either on works - sometimes one better then the other...

HTH,
Kev