Developing In Access

[pa2]

Currently I develop in Access systems to suit the business. I use master and replica and synchronise them and the users access them through Server and Citrix.

For best results and the direction to move in for the future should I: -

1. Change to Visual Studio and redevelop my existing db's in VB. (I see no benefits here)

2. Convert my db's to ADP in which case I believe I will need to use SQL server as a backend (I have SQL Server and believe this is the best option to date)

3. Or is there a better solution?

 

[xlbo]

To be honest, you are better off asking this question in 1 of the 7 dedicated MS Access fora

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fneily]

When you post in the other Access forums, you might want to make yourself a little clearer. You state you use a master and replica and then say the users go throught citrix. You don't need to do a master and replica with a citrix server.
You're asking for a solution to what? The best way for remote users to cross the Internet??
Also, include how many users, are they just looking at data or input/editing data, how often, etc.

 

[pa2]

Hi fneily:
There are 20 to 30 users looking at, inputting & editing data on a daily basis. Master & Replica's are used because that is the approach that has always been used to date.

As Access is not a server client database but a file-based database I thought converting to ADP and using SQL server as the backend would suit the access applications better.

A colleague of mine suggested Visual Studio was the way to go, using the VB to write the Forms, Reports etc., however I see no true benefits here, just a whole lot of work in VB; I thought ADP & SQL better, do you agree?

 

[fneily]

Yes. The MSDE is structured to be a client/server engine whereas Jet isn't. The major benefit is where data is processed - Jet locally, MSDE on the server. This affects data transfer. Also, you can then try the Access Upsizing Wizard to convert your Jet into ADP. Might same you some time.
Getting back to citrix, I worked for HCFA (now CMS) - Medicaid/Medicare - and we used citrix so all the insurance companies, doctors, hospitals across the U.S. and its territories, could dial in to a local database on the citrix server. Again, you might want to consider that. See

http://www.citrix.com

Now, any more answers will be given in the right Access forum.

 

[BobRodes]

As I see it, the main benefit of moving to VB (meaning VB6 rather than .Net) is that you decouple the front and back ends to a large degree. Also, you can leverage your VBA knowledge to minimize the VB learning curve.

I found the upsizing wizard to be useful in an access-to-sql conversion project that I got involved in, but there were lots of odd little problems that I wound up having to correct. Still, as fneily says, it saved a lot of time. In that particular case, the application used VB and DAO to work with the MDB files.

 

[JoeAtWork]

I've basically come to the conclusion that Access is not the choice of the professional programmer. I can't think of one reason why I should choose to develop a new system with Access when there are more robust development tools available.

Access's niche is to provide a simple database system that can be put together by the non-professional.

ADP + SQL Server is better than pure Access MDB files. But why stick with the Access UI at all? I wouldn't recommend going to VB6 as that is quickly becoming obsolete. Have you considered .NET?

 

[bjb123]

One of the benefits of an Access front end is that if you allow users the ability to write their own select queries with the QBE they can develop a greater understanding of the data and the relationships between tables.

At my company some of the users have become so proficient with the QBE that they handle more than 50% of the data request that used to fall on me. Now I'm only needed for very complex SQL.

These users would not be comfortable poking around in query analyzer, nor would I want them to.

We don't have Crystal Reports, other than what is integrated in Visual Studio, and we don't need it. Access handles all the reports that we need.

Another benefit is the speed in which you can roll out new applications, forms, reports etc.

Some of the downsides I didn't see mentioned yet: security of the data and needing an MS Access license on every station if you don't have the developer's edition of Access.

For your professional development it is far better to develop in .net, C# or VB with a SQL Server back end. Just do a quick search for VB programmers vs VBA/Access programmers on careerbuilder.com. I live near Milwaukee and there is one position for VBA/Access developer at present. There are 39 positions (including some recruiter repeats) for VB or C# developers.

 

[BobRodes]

<Access's niche is to provide a simple database system that can be put together by the non-professional.

Well, Access is also very convenient as a back end to a for-sale application, given that all you need to do to have it is ship an mdb file. That means that you can ship an install package that will run out of the box on a clean machine.

 

[Remou]

Access is useful when an application is required quickly. It is also suitable for developing small, intermediate applications; for small business applications; and as a staging post for larger applications.

 

[JoeAtWork]

Access is useful when an application is required quickly. It is also suitable for developing small, intermediate applications; for small business applications; and as a staging post for larger applications.

Those used to be my justifications for using Access. But Visual Studio has become so sophisticated, with database integration, intellisense, etc., that I think Access no longer holds any advantage in RAD (rapid application development) any more.
I think nowadays, a good .NET programmer can produce a solid application almost as quickly as a good Access programmer. But the difference is with .NET the application can have a scaleable architecture, which is impossible with Access. And we all know how those small and intermediate applications quickly grow into big ones.

 

[BobRodes]

Well, I was talking about VB6 as a RAD tool. .Net is another animal entirely, and I can't say I have enough expertise to comment.

 

[MichaelRed]

many here decry the use of Ms.A. as ' ... just a toy ... ' citing it's lack of features.

The best justification I have seen for this is the one about future employment.

The other 'objections' are easily destroyed by serious programmers.

MS. A. readily accepts and uses the advantages of SQL Server - or any other industrial strength database engine *as notedm MSDE which is 'free' for use with Ms. A. IS SQL Server, albeit without the T-SQL interface in the MMC (at least without significant work on hte developer's part). This destroys the argument for upsizing to whatever.

Ms. A. already includes a reasonable report writer, thus saving the need and expense of the popular third party tool for report generation. While such tools may have some capability which Ms. A.'s version lacks, I have not yet encountered a report requirement which required any of htese 'extensions'. Perhaps I've led a sheltered life, but have have written reports (w/ Ms. A.'s report engine) which ran to the thousands of pages with up to at least seven levels of sorting and grouping.

Some say that they can develop as quickly in other platforms. This may easily be true, but I believe that it generally reflects more about the tool(s) which the developer is most familiar with than the quality of the tools. To paraphrase the old adage, " ... If I had a hammer ... everthing would look like a nail ... ".

As far as leveraging your knowledge of VBA to the use of VB.*, I have used VB4-6 extensively and VB.Net at least to the extent of feeling comfortable applying for employment (both full-time and 'consulting' and find that they generally have little new to offer the programmer - at the instruction level. Of course, I also need to admit that FORTRAN IV was the third or fourth programming language I 'learned', so it is possible that I am a bit jaded in considering the learning of yet another programming language.

I admit do not quite understand what development features exist in these other development environments which are so mush better than Ms. A., but would like to hear about these is some detailed compariaion.




MichaelRed

 

[JoeAtWork]

I admit do not quite understand what development features exist in these other development environments which are so mush better than Ms. A., but would like to hear about these is some detailed compariaion.

Mostly it is a matter of not being able to design a well-architected application. The UI, business logic, and data layers are all mashed together.

This makes an Access application unscaleable (just because you can make it an interface to SQL Server does not make it scaleable, in my opinion).

There are also matters of code maintainability, an important issue for "serious" programmers. A language that fully supports object oriented programs allows me to write core objects that I can base all my other objects on (through inheritance). This is extremely useful if you want to design an application where the business rules need to be applied consistently.

Access used to be the choice when you needed to get a small or medium sized app up and running ASAP. But I don't think Access is the RAD king any more, and thus that excuse for using it no longer exists.

Access' niche should be for businesses that cannot afford a professional programmer. But as a professional programmer, I've run out of reasons to choose Access over Visual Studio.

 

[MichaelRed]

seperating the "business logic" into a seperate layer and / or creating classs modules is easily done in Ms. A.

Ms. A. does not exclude object orientated methods, a fact reasonably demonstrated by at least one post in these fora (albeit some time back). I do not remember the thread, only that it included a LINK to another site which had a demo app using Ms. A. and object orientated code.

thus the "meshing together" aspect is a matter of design choice, and dictated by Ms. A. (at least in my opinion).

maintainability (of code and other objects) is (again my opinion) a matter of discipline in the generation and modification of the code. using a seperate module (mde?) provides ample opportunity to place and keep reuseable code in a seperate container.

I am currently working on an app written (mayhap scribbled?) in Ms. A. vb / jet engine. The "FE" is currently at ~~ 50MB, with a BE of ~~ 100MB. It generally has between five and fifteen concurrent users. While there are occassional glitches, mostly they are of the nature of " ... error between chair and keyboard ... " (mostly mine) arising from hasty development changes. The project (app) was started in 1994 (not by me!) and has a great deal of legacy code (circa Ms. A, '97) and some number of obsolete (e.g. retired from active duty but not removed from the app) 'objects'. With some minor exceptions, I believe that it would comfortably support close to fifty concurrent users. It is a close functional approximation of a complete 'CRM' application. I do not know of another approach which offers the same degree of integration and flexability as the Ms. A. platform.

Again, I'm quite willing (actually even anxious) to be shown the error of my ways and thoughts and encourage the community at large to provide me with the information.




MichaelRed

 

[AlexCuse]

This all comes down to the right tool for the job. I use access mainly for single user, data processing applications (with a SQL Server back end), where I can take advantage of the local data area that access offers.

I tend to agree with Remou and JoeAtWork, that access should not be used for multi-user enterprise applications if at all possible. I'm not saying it can't deal with concurrency issues, but using 'real' vb or one of the .net languages it becomes much less of a headache.

Just my .02

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[BobRodes]

<Ms. A. IS SQL Server [...] This destroys the argument for upsizing to whatever.

From Microsoft:

Unlike a file-based database engine, a server-based database engine such as Microsoft SQL Server processes all of the multiple client requests to a database at the server. The server keeps track of these requests in a transaction log. If, for any reason, a request cannot be fulfilled, the server rolls back or does not process the request. This reduces the possibility that the database will be left in an incomplete or corrupted state.

<I'm quite willing (actually even anxious) to be shown the error of my ways and thoughts and encourage the community at large to provide me with the information.

I shall take you at your word, Michael, and see what I can manage:

From Microsoft:

"While Microsoft Jet is consciously (and continually) updated with many quality, functional, and performance improvements, it was not intended (or architected) for the high-stress performance required by 24x7 scenarios, ACID transactions, or unlimited users, that is, scenarios where there has to be absolute data integrity or very high concurrency." -KB #222135

"Microsoft Jet can only handle a limited number of sessions. If your application uses a large number of ADO Data controls, Jet may run out of resources." -KB #225048

"Microsoft Jet has a read-cache that is updated every PageTimeout milliseconds (default is 5000ms = 5 seconds). It also has a lazy-write mechanism that operates on a separate thread to main processing and thus writes changes to disk asynchronously. These two mechanisms help boost performance, but in certain situations that require high concurrency, they may create problems." -KB #240317

"Jet can support up to 255 concurrent users, but performance of the file-based architecture can prevent its use for many concurrent users. In general, it is best to use Jet for 10 or fewer concurrent users."

http://msdn2.microsoft.com/en-us/library/ms811092.aspx

Some more links from Microsoft:

http://support.microsoft.com/default.aspx/kb/154869

http://support.microsoft.com/default.aspx/kb/299973

http://support.microsoft.com/default.aspx/kb/299974

Some links from the community at large:

http://www.granite.ab.ca/access/corruptmdbs.htm

http://www.15seconds.com/Issue/010514.htm

http://databases.aspfaq.com/database/what-are-the-limitations-of-ms-access.html

http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_17034&sliceId=2

HTH

Bob

 

[JoeAtWork]

seperating the "business logic" into a seperate layer and / or creating classs modules is easily done in Ms. A.

Would I be able to write a business layer that can be used by both a windows UI and a web UI? Could I deploy this business layer on a separate machine from the UI and database?

Ms. A. does not exclude object orientated methods

VBA doesn't support inheritance, and thus lacks two of the key advantages of OO code:

1. Code reuse
2. Consistency in behaviour
In a true OO language, I could write a class called Dinosaur, and give it all the properties and methods that all dinosaurs have. Thus, a Dinosaur can eat, walk, run, etc. I could then make more specific classes like TRex and Pterodactyl that inherit from Dinosaur (code reuse). And if at some point in the future I find out there is a bug in the Dinosaur.Run method, I fix the bug in Dinosaur, which automatically fixes TRex and Pterodactyl (consistency in behaviour).
In VBA, the best I could do is cut & paste code from Dinosaur into TRex and Pterodactyl, and fix bugs individually in each dinosaur class (quite a chore if my virtual Jurassic Park has grown to 100 species of dinosaurs).

I don't mean to sound anti-Access. As I said before, it fills in a very important niche - a database program for small and medium sized businesses that do not yet have professional programming support. Even if the layperson doesn't have a clue about normalized databases, the result will still be better than databases a la spreadsheets.

My arguments are this:
1. Other tools are more robust (I have yet to "fix" an EXE by compact & repair or decompile/recompile)
2. Other tools are scaleable
3. Other tools are fully object oriented
4. MS Access has no advantages over the other tools, but is disadvantaged in the first 3 points mentioned

 

[jrbarnett]

Those people reading this thread may be interested in a book I am reading at the moment (business, not pleasure).

Visual Basic 6 Business Objects: Enterprise Design and Implementation.
By Rockford Lhotka, Wrox Press, 1998.
ISBN 1-861001-07-X

Although the topic of the book relates specifically to VB 6, there is no reason that some of the techniques described cannot be implemented in VBA in Access or Excel, for example.

John

 

[BobRodes]

<some detailed comparison

I wrote "some detailed comparison" before Joe's post, and then couldn't post the response. I'll respond to Joe's first, and then put what I wrote.

<In VBA, the best I could do is cut & paste code from Dinosaur into TRex and Pterodactyl

Joe, you can do a little better than that. You can create a reusable component in Access, by doing the following:
1. Create a separate Access application (say "ServerApp") to hold the reusable class.
2. Add a class module or modules. Set their instancing property to PublicNotCreatable.
3. Add a standard module. In this module, create a method for each class module that returns an instance of it.
4. In applications that are using these classes, add a reference to the ServerApp application.
5. Instantiate the classes in the standard manner.

VB is often chastised as not being an OO language because it doesn't have inheritance. If the definition of OO is that it include inheritance, then so be it. However, I define OO as having primarily the characteristic of functional encapsulation. Encapsulation means that you place boundaries around some sort of functionality, and then expose that functionality in a consistent manner through a known interface.

Now then...

<separating the "business logic" into a separate layer and / or creating classs modules is easily done in Ms. A.

It really depends on how you are going to define "separating". Class modules in Access aren't capable of having a public interface, relying instead on the public interface of the Access EXE itself. So, while you can go into the Access VBA IDE and create a class module, you can't completely separate it from the application that created it.

Access class modules are actually specializations of the Access.Application object, meaning that your compiled business logic component must include the entire Access Application interface (see

https://msdn2.microsoft.com/en-us/library/aa207979(office.10).aspx).

This tends to contradict the point of view that the business and presentation logic are really separated into different layers. While they may have the appearance of being separated in the IDE, they're really all piled together in the same component.

Another point of comparison is that you are limited to interprocess (out-of-process) communication, which, while it has some security advantages over intraprocess (DLL-based) communication (a fatal error in a dll will hang its associated exe, but a fatal error in an exe won't hang another exe), is also much slower owing to marshaling requirements. While EXE components have their place, DLL components find much broader application than EXE components.

So, you can't create a separate DLL using Access Class modules, and can't create any objects at all that have a context that's completely separate from an Access application exe. Therefore Access classes are less strongly encapsulated "under the hood" than the average class, and this tends to limit reusability. I would say that they work best in a situation where they are fairly strongly associated with a single Access application. While you can go through a couple of hoops and create a class that is reusable by other applications, the result carries a relatively huge footprint. If you want to create a component that is used by multiple applications, consider creating an ActiveX DLL, using a development tool such as VB6.

HTH

Bob