What can't Access do? (1 Viewer)

[G37Sam]

As the title suggests, when does MS Access run out of breathe?

There's nothing my clients wanted that I couldn't do or work around.. have you ever encountered a situation where you just had to reply: "I'm sorry, but this can't be done with MS Access"

 

[PNGBill]

If this was in the Water Cooler section but I guess you are looking for serious responses

 

[Banana]

Do laundry, pay bills, file income taxes.

Okay, I'm done being facetious.


Good question, nonetheless. However, I think it bears defining it more clearly The thing is that Access is very versatile and scale well when designed correctly. For example, if we run into 2 GB limitation or a need to connect across WAN or better data security, we can just swap in a different backend and pesto a great front-end client! I've also had written an Access application that made use of web service, sending and receiving SOAP messages and processing upon them. Because Access VBA can access API and many other libraries, it's very easy to extend Access.

One long standing limitation was the problem of deployment - if it was required that there be a zero-install application... accessible via a web browser, we would be out of luck here. That's no longer the case as of Access 2010, not to mention other services that make use of Access on web (e.g. EQLData or AccessTables for example).

Next point would be to do with what are available off the shelves. I would sooner recommend, for instance, a payroll or tax software somewhere other than Access for simple reason that kind of application is widely well developed, has a team of accountants and develop backing (and clobbering up the one small guy easily). Furthermore, several of software can be also integrated with Access so for where there is a specific business process that's not addressed adequately, Access can come to rescue while leaving the rest of accounting to well, accounting software.

Another weak spot is where visual layout is a require. Suppose they want to be able to see a graphic layout with lot of drag'n'drop. That is probably easier to implement in a different development environment where one has more control over the elements. I suppose one could conceivably get away with it with some help of ActiveX and maybe a lot of VBA but I'm not sure this will pass cost/benefit analysis. I should also point out that there is no reason why it can't be done as a .DLL that can then allow Access to call it and thus leave the graphic lifting to that .DLL and concentrate on only the data changes in response to user's interaction.

So as you can see, it's always possible to use Access, very much especially when it is allowed to work in conjunction with other piece of software to do just about anything. Cross-platform compatibility is possibly the only point where it can't be used at all -- one would have to turn to FileMaker or OpenOffice Base. Even so, that's unlikely as typically when cross-platform compatibility is wanted, web-capable solution usually satisfy that requirement anyway.

I also suspect that Access becomes inappropriate when the front-end client has numerous specific requirements and a specific manner of processing data in where unbound forms is appropriate (e.g. using stored procedures exclusively and never interacting with tables directly for example). When a large % of forms are unbound, it's probably best done in other developmental environment. The selling point of Access is its RAD rather than the ability to fine-tune every possible dials on the board.

HTH.

 

[G37Sam]

I appreciate you taking the time to write that up Banana

How about working with hardware? Card readers for instance to log employee's sign in/sign out?

 

[Banana]

Good question. I can't say I had any personal experience but if you search the forums, you should see few posts reporting using card readers or bar scanners. Typically, when they use some kind of hardware, the vendor gives out a library file that allows Access to work with it. The key point is that the hardware library file has to support COM automation to work quite well.

OTOH, mobile devices are probably problematic since they are not running true Windows OS but rather a truncated version that would almost certainly require a different solution.

 

[G37Sam]

I worked with BarCode Readers before, those basically acted like a normal keyboard when it came to data entry, there wasn't any need for extra library files etc..

But once I was asked if I could come up with a database to manage to a snooker place and switch table lights on/off from within Access. That's when I suggested they used an off the shelf solution haha

 

[The_Doc_Man]

The things that Access can't do are the same things that other packages can't do. That is, read your mind. It is so damned good that sometimes it seems as though it really COULD read your mind, but that isn't the case.

But if you want to talk limitations, just remember that Access is based on a small business model so will run into issues of scale. Yes, you can upscale the data section, but at some point your systems will run out of power if you try to do too much and still stay within Access. That's not a design flaw in this sense: The product's popularity causes many people to try to "stretch" it to do more. But it is still a pseudo-compiled system involving "soft" structures that get drawn on-the-fly and code that is interpreted rather than executed (for the most part).

 

[Banana]

But if you want to talk limitations, just remember that Access is based on a small business model so will run into issues of scale. Yes, you can upscale the data section, but at some point your systems will run out of power if you try to do too much and still stay within Access.

FWIW, I don't really see that as issue but maybe I'm just not understanding. See, when I think of Access as a front-end client to a RDBMS server, I tend to think that scalability pretty much fall squarely on the server's shoulder, not on the Access which is really no different from a plain old executable or even a web client in this context. Yes, if we insist on using Jet/ACE we will run into limitation pretty fast, but that's easily solved when we change the backend.

I would probably be more concerned with say, the need to control every aspect and working at lower level than Access expect us to work or maybe the need to cope with poor networking as more significant factor in not choosing Access, but scalability? Not really an issue?

 

[The_Doc_Man]

Banana, I'm talking about how many things you do behind a form, how many controls with complex events you can manage on a form, how many dynamic changes you make on a form, how many things you can do on the fly - in VBA.

If you had true compilation, some of the run-time actions done through VBA would not be possible, mostly having to do with late binding. Which is why the issue of scale comes in. The more you try to do behind the scenes, the more you need to consider building something that uses VB rather than VBA. It is a programming limitation that derives from using interpretive (pseudo-compiled) code - as for example, VBA.

Don't get me wrong, I'm not trying to slam Access for using VBA. It is merely my opinion that a wise man knows when to stop using a hand-held hammer to drive pilings. Know when it is time to invest in a steam hammer with a crane attachment.

 

[Banana]

Thanks for clarification. Yes, I see where you're coming from. I was thinking of scalability in different terms which was why I was confused by your original remarks. I guess I typically don't see the fact that we're operating on interpreted** platform as a significant issue but I would definitely agree that if a front-end was doing a lot of stuff behind the curtains.. doing intensive calculations for instance, it may be an issue. Of course, we always have the option of offloading this into a DLL so we can eat cake and have it by having Access handle the UI and the heavy computation tasks inside a DLL using the appropriate language best suited for the purpose. Of course, this assumes the DLL is compatible with COM to be usable inside Access.

** I think there was a thread few months ago where it was pointed out that VBA isn't a pure interpreted language - it can be compiled into p-code... sorta like how Java compiles into bytecode which is then executed by a runtime engine. Else, we wouldn't be able to have a *DE file where code is permanently compiled. So there's that.

 

[gemma-the-husky]

one other thing is, is that VBA syntax, and form management appears a little easier to use, than the equivalent in VB, (from the limited experience I have of VB, anyway)

As a result I find it easier to use Access as a platform for non-database purposes - eg, I used it to write a file re-naming utility, when I was trying to sort out the names of a stack of jpegs I was moving around.