Using Access Objects vs. Pure VBA Code (2 Viewers)

[Capo]

I am wondering what you all think about designing front end applications via Access and whether one should use Access objects or simply hard-code everything via VBA in forms.

Essentially, what I'm asking is whether one of the two following routes have a preference, and why. Route 1 would be creating your Access FE application with linked tables, queries, modules, macros, etc... essentially using all the built in object features in access.

Or for route 2, simply creating your forms in access but doing all queries, linked tables, etc. via connection strings. Essentially, making the entire Application VBA based.

Are there advantages to either route? Could you please elaborate on some short-comings for either?

Thanks!

 

[Banana]

1) SQL is faster than VBA.
2) Access objects is already tested & tried and therefore less likely to cause errors or undesired results, or at least bugs are already known and can be worked around.
3) It's much, much less work to simply use the objects and you can get your product to market faster.

VBA exists to allow you to extend *beyond* what the objects are capable of. If you are going to write everything in VBA, you might as well drop Access like a hot potato and do it in .NET environment with SQL server. Access's selling point is rapid application development, and if you can accomplish several things using the built-in objects and fill the gap in with VBA, then you'd have saved considerably more than if you went the .NET/SQL Server route.

You need to know what your specifications and contraints are; Access is not always the best fit for everyone's needs. Only you can decide if your requirements can be served well by Access or by other solution.

HTH.

 

[The_Doc_Man]

Banana is absolutely right. I'll say it a different way but we are in agreement.

Using VBA entirely, you "roll your own" application at the nuts and bolts level.

Using a mix of Access and VBA, you take advantage of the fact that someone else has already written some general code using industry-standard techniques to provide a very capable rapid application development environment.

Let's say that Access did 65% of what you wanted straight up, no craziness.

Which would you rather do in VBA? 100% of your project or just the 35% of your project that Access won't already do for you. (And I'd be AWFULLY surprised if the numbers were as bad as 65/35. More often, unless you have the damnedest problem any of us have ever seen, you might see 90% for Access and 10% for you.)

 

[Capo]

Good points, thank you for sharing.

I'm curious to hear other viewpoints and/or additions, if there are any.

Thanks!

 

[Dennisk]

Another Pro to consider when using all vba is that you can use a disconnected database system. So you only connect when you read/write data. This may well result in more robust dbs when running on a network with many users.

I've just had to fix a currupted backend that had duplicate autonumbers, orphaned records, Referential Integrity removed etc. If this was a disconnected db it may never have got to state it was in this morning.

 

[neileg]

I can't see the point of using 100% VBA since you still need the Access container to run it. Now VB is a different matter since you can compile it and you don't need Access.

 

[Banana]

Another Pro to consider when using all vba is that you can use a disconnected database system. So you only connect when you read/write data. This may well result in more robust dbs when running on a network with many users.

I've just had to fix a currupted backend that had duplicate autonumbers, orphaned records, Referential Integrity removed etc. If this was a disconnected db it may never have got to state it was in this morning.

While it is a benefit, it is something I wouldn't do in VBA.

Writing a program in VBA is much, much more slower and pain in neck than if you wrote a program in other proper language (including VB.NET or maybe at least VB6, although I doubt VB6 is that much better).

VBA is extensible; it is there to allow you to add functionality to objects. Once you get into realm of writing objects on your own, you're in worse state of affairs, as there are several other IDEs out there that will do things more easier, faster, and better than VBA.

It's essentially a package deal; you either use everything in Access to get benefit of using it, or you don't use Access and roll out your own application in <insert your programming language> to get what Access can't do for you.

 

[Brianwarnock]

I seem to remember Pat Hartman replying to a similar post a couple of years ago and saying that you could never right code to match the performance of the built in facilities.

I would also hazard a guess that you would be less likely to fall foul of Microsoft changes as Barry recently did with his Undo.

Brian

 

[Banana]

who's Barry and what did he do with Undo to bring down Microsoft's wrath?

 

[Brianwarnock]

Barry is a regular on the forum and code that worked fine in Access 97 failed in 2003, he posted on the forum and apparently the rules had changed according to more knowledgeable posters, I think it was Rural Guy who explained it but I could be wrong. The important point I was failing to make was that own grown systems are more prone to upgrade problems than going with the supplier not that there was a none code way to tackle the Undo.
Does that ramble make any sense?

Brian

 

[Banana]

Yessir, clear as mud now!

Thanks for the background.

 

[Brianwarnock]

Yessir, clear as mud now!
QUOTE]

I'm noted for my eloquence

Brian

 

[Capo]

If you all don’t mind, I’d like to touch a bit more on the performance issues. Let’s say we have a form that auto-populates several fields by pulling data from an ODBC connection. Now, which route would be more beneficial?

1) Having the table linked in via Access, building a query in access and then referencing that query in the form

Or

2) Create a connection string in VBA, query the table via a SQL script in VBA and then populate the results

Basically, what I’m getting at is will it be more beneficial to run your SQL queries in Access, or via VBA?

 

[DJkarl]

If you all don’t mind, I’d like to touch a bit more on the performance issues. Let’s say we have a form that auto-populates several fields by pulling data from an ODBC connection. Now, which route would be more beneficial?

1) Having the table linked in via Access, building a query in access and then referencing that query in the form

Or

2) Create a connection string in VBA, query the table via a SQL script in VBA and then populate the results

Basically, what I’m getting at is will it be more beneficial to run your SQL queries in Access, or via VBA?

I can only speak about my own experience in this area but I would say that linked tables and stored queries seem to run slightly faster than using connection strings and pure VBA.

I have used and still use both methods depending on what I'm developing. If I am developing an automated process that does not need a user interface I will use whatever method gives me the best combination of reliability and performance. If however I'm developing a front-end for a user then I'm far more concerned with security than performance, I will usually opt for the VBA method because it prevents adventurous users from messing with linked tables or stored queries.

 

[Banana]

Few points to consider.

It is difficult to answer your question, because it depends on what is more important to you.

If you want something that just works, then ODBC is more than adequate for the job. IINM, Access doesn't exactly support disconnected connection, (but I was told that it could be done in ADO... Anyone?). Therefore, connection strings would be the answer.

However, I don't think overhead will change that much. With ODBC, you're using a driver to communicate between two servers (Access's Jet engine and the server backend). With connection client, you're using a front-end client, if only a CLI client, for the server to pass along the string. I haven't done any benchmarking but can't imagine performance would be that drastically different between those two methods. Hence my assertion that it depends more on what you need out of ODBC/Connection string.

 

[Pat Hartman]

I've been "programming" for more than 30 years. It doesn't frighten me. However, I feel that I've written my million lines of code and I don't need the practice. I would never consider writing code to replicate what Access does for me unless my client fully understood the ramifications and wanted to pay for the effort necessary to reproduce existing functionality. Know your tool and use it to its advantage. If you want to write a lot of code, go with VS and one of the .net languages. You can write code and debug it to your heart’s content. I'll be sipping pina coladas by the pool while you are burning the midnight oil because I'm going to use bound forms and take advantage of everything Access does for me and you're wasting your time with a philosophical discussion of which is better - using a tool or writing your own.

 

[Len Boorman]

You can write code and debug it to your heart’s content. I'll be sipping pina coladas by the pool while you are burning the midnight oil.....

That swings the discussion for me

Len

 

[Simon_MT]

Personally, One of the advantages of Access has been its interface particularly in designing Queries. SQL Queries should not be underestimated, they are extremely powerful and will take you right into the world of web based applications, if required.

I personally don't see the need to re-invent the wheel when it comes to Forms and Reports although you do grow out of the wizards. I would also write all your scripts in a Module outside the Form or Report, rather than using Macros or Sub Routines. There is better error handling, collaboration and debugging facilities with VB.

The only problem with linked tables is the performance with FE and BE deployments, this can be resolved with an "always open" Form bound to a Query or Table at the BE.

Simon

 

[The_Doc_Man]

I'll be stronger than Simon on the form/report wizards. REGARDLESS of the fact that they are as dumb as a box of rocks, the aforementioned wizards can be used to give you a VERY good starting point for a basic form. Then you customize from there.

There is only one reason to not use the wizards and that is when you are trying to do something for which Access wasn't really designed.

 

[Pat Hartman]

I have to confess to using wizards. While I don't like the results in all cases, I do use them as a starting point. I generally start by creating a query that has the fields in the order I want to see them. That way, the form/report wizard end up with the fields in the order I want them so I have the minimum reformatting to do.