Coding a database in only VBA, Advisable? 2

[Sh0jin]

I recently read "Beginning Access 2003 VBA", and most of the examples of databases were self coding without the use of any of access's capabilities. Basically the form were designed in access but the connections and logic came solely from VBA code.

After a month of programmming (from example), i keep asking myself the same question, would it of been easier and less time consuming to have used access's functions, macros, query creator or relationship tool to create this database?

I have created classes in thinking that it would be easier as my background is in OOP with C++ but it takes a hell of a long time to declare the one class. Should i bite my tongue and endure the pain as the results would be good or should i throw in the towel and start again.

My database is split, so i have a function that connects to a data-table through a string path name. I am thinking that all i would do to upgrade is change the string to the sql server path. Is this correct?

What is a better approach for upgrading to SQL server?

I'm a little frustrated and would like professional advice. If any of you guys out can help, any advice is better than none.

 

[VBslammer]

The power of Access is its ability to quickly prototype applications using wizards and drag/drop. The heart of Access is Forms. When you put code into a Form module, you're actually extending a "Class" module - so even though it feels like procedural programming, it's all classes.

Using bound forms takes most of the work out of developing a file/server frontend, so creating classes for each entity in your system may be overkill. Many Access developers will use the form object as a kind of quasi-entity, so most of the logic for dealing with that entity is contained in the form's class module. That works great for the Access app, especially since the users can't tell the difference between a componentized app and a procedural app anyway - as long as it works. But if you decide later to broaden the scope of the app or go client/server, you can't exactly compile the form into a .dll and intall it on a business server.

If you want to develop a real client/server app, then it would be better to create your entity classes now using an Access Project (ADP) or a full-blown development platform like Visual Studio.Net. I don't think I've ever seen a .mdb project that was fully implemented using entity classes. Considering that a .mdb is a "fat-client" (does its own processing), if you had a large user base and needed to install an update, you'd have to spend the time to update each user. With a client/server app, you can put more of the processing at the server, especially if you go 3-tier and install the business logic on a separate business server. An update to a COM object on a server will cascade down to all the users instantly.

One thing to consider about Access is that any code you add to standard modules will be loaded upon first use and remain resident in memory until Access shuts down, which is one good reason to encapsulate your reusable routines in class modules, since they can be created and destroyed as needed.

Access does have an updgrade wizard that will convert a .mdb to a .adp, so it's worth experimenting with it to see how it changes your file/server app to a client/server app.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Sh0jin]

Hey cheers for that.
Do you know of any good books, that i could get an example of good programming habits and approaches towards full blown application development with access (client/server technology)?

Sh0jin

 

[VBslammer]

I have several books about building Access Projects, but I wouldn't say that any of them are "good" at teaching client/server programming. They assume that the reader is already an advanced Access programmer making the move from DAO to ADO and SQL Server, so they just review the common problem areas that veteran Access developers have when migrating to the new platform.

Very few books actually improve upon the MSDN documentation, and some merely parrot it, and I've learned as much from studying the example programs Microsoft provides as I have from books.

When I had to develop a start-to-finish SQL Server app, I bought a book for Professoinal SQL Server development because the database design was actually more involved and important than the front end. If you build the database right, your app only has to call the stored procedures properly and all the work gets done on the database server. It should be just as easy to use the database from a Web page as it is from Access.

Here are some of the books I use:
[ul]
[li]Professional SQL Server 2000 Database Design by Davidson (Wrox)[/li]
[li]SQL: Access to SQL Server by Harkins and Reid (Apress)[/li]
[li]Microsoft Access Developer's Guide to SQL Server by Chipman and Baron (Sams)[/li]
[li]Access 2002 Developer's Handbook (Set of 2) by Litwin, Getz, and Gilbert (Sybex)[/li]
[li]Mastering Visual Basic .NET Database Programming by Petroutsos (Sybex)[/li]
[li]ASP.NET Website Programming: Problem/Design/Solution C# Edition by Bellinaso and Hoffman (Wrox)[/li]
[/ul]
The last one is an excellent tutorial for building an OOP SQL Server app using the complete design process. It utilizes Visual Studio .Net and C#, but it is also available in the VB.Net version.




VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Sh0jin]

Thanks for that information, i am really thankful for your help. I realised that i was designing an application for a 3 tier system, which was a little overkill for what i needed to do. So i will be redesign and experimenting with conversion tools in access.

Cheers