Using Access and SQL Server 2000 in n-tier architecture?

[glgcag]

I currently have a large database in Access 2000 that is beginning to give hints of not being able to manage the 15-20 concurrent users and the hundreds of thousands of records it contains.

I am looking at converting the database to a true client/server architecture. Since I'm familiar with Access programming, I've thought about making the database an Access project file (instead of a .mdb file) and convert the existing tables to Microsoft SQL Server 2000.

Though Access can interact with SQL 2000 directly I was thinking that creating a truly modular design by implementing n-tier architecture might be better as I want to be able to create data access to the SQL Server data via the web in the future.

Since Access would be the client (and would call the middle tier functions/stored procedures?) and SQL Server would contain the data, what would I use to best create the middle tier containing the business logic? I've talked with people who have used VB 6 to create a COM object, but since I'm not altogether familiar with this I just thought some of you experts out there could give some advice. Also, if you have any references on the web you could suggest, I would appreciate it.

Thanks!

GLGCAG

 

[psemianonymous]

I hope someone else will chime in, giving a better answer than me, but:

Basically, a middle-tier eliminates all benefits you get from using Access. Everything in Access is bound to a data source, and using an n-tier architecture hides the entire data source from the UI layer. Which is Access.

So if you want to use bound forms, continuous forms, or Access reports, you have to be able to see the data directly via table links or stored procedures or ADODB.Connection objects or whatever. And if you're going to go to a true n-tier environment, again this is going to eliminate every useful Access feature.


If you're doing this for speed, then there are ways to speed up your performance. The big ones are: a)don't use bound forms on large tables, b)Check your table indexes and query design for obvious optimizations on the slower queries.

Both of the above can and do help for either Access backends or for server backends.


Anyway. The bottom line of this post is to say "Access is n-tier incompatible!" And then point you to "Access is reasonably client/server friendly" pages.


As for links:

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

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

Pete

 

[glgcag]

OK, so using Access as a client is a bad idea? I wanted to stay in the Access environment only for the speed of developing forms, reports, etc. Going to VB or a web client would require more programming time.

Due to the large number of users I currently have I no longer program forms with bound data anyway, though there are a lot of forms left over from the original designers that are bound. I'll check out your suggested sites!

 

[psemianonymous]

Yeah. The whole deal with n-tier is that it is a more rigid approach to developing applications, thus you code is guaranteed* to be higher quality. So you are allowed to do some crazy things with Access, like allow an empty "ADDRESS" to be inserted in one form, and require the "ADDRESS" on a separate form--with an n-tier architecture, you are guaranteed that the address will be required, wherever they try to insert it from. With small applications this doesn't matter, but with huge enterprise-level apps, this is a big deal.

So yeah, go to VB or whatever programming environment suits you, and yeah, a server DBMS will handle more users better.



*guaranteed within reason

 

[BNPMike]

If you have Access on each client, your data on SQL Server, amd your Access code sitting on a network, you've got 3-tier. It may not be like other 3-tiers you have seen (because the presentational logic is also in the middle tier) but tell me why that's a problem?

You've also got seriously question whether fiddling about with some fancy abstract middle layer would ever save time over just straight migation if you wanted to move to an http model.

 

[glgcag]

You guys are definitely putting me to the test as far as asking myself what I'm trying to do . . .

You're right about using a network based middle tier Access database as the repository for all my code, etc. That really does make it three tiers if I use a separate client. AND, it's pretty easy to add web connectivity to the data and just create my forms using html or Visual Studio . . .

I can see that a large enterprise would want more fine grain control of HOW the data gets entered, but I do too. All of that can be controlled via Access to a degree as well, so I guess just converting the existing Access db to a project and optimizing it to utilize the faster and more robust features of SQL is the way to go.

GLGCAG

 

[psemianonymous]

I'm just fascinated with the entire paradigm of Access database development--as I'm a college CS student, I'm learning about the 'proper' way to design databases is through the n-tier system. In fact, I have an Oracle DB admin class in which it recommends that you run Application Servers (uppercase intentional) that are the ONLY COMPUTERS able to physically connect to the Oracle backend. Then you pool your users into separate application groups, in such a way that you reap benefits from caching their data accesses.

So when you consider an n-tier architecture, imagine the Application Server in uppercase. Do you need that kind of setup? Or do you just need something that's faster?

Oh, and one last thing: the Access front-end is the first tier. The definition of the n-tier architecture is where the UI can never interact with the database tier, which Access violates early and often. The whole point of doing "n" tiers is that you can build very complex business logic in "any number of " middle tiers, in one big heap of business logic objects. I'm both fascinated and horrified by the thought of this sort of system. I've worked as a user of an enterprise system (the system was IDXtend,

http://www.idx.com

), and I can see where these sorts of systems can be useful. The "Address" example I gave above was a real example from the IDXtend system--one form required the address, another form let you even delete the address entirely.

 

[glgcag]

Since I'm self-taught and I run ALL the technology at a company of 25 employees, it's tough to know when to do things the "right" way, especially when it's not always the most sensible way. Looking at the n-tier architecture I'm drawn by the structure of it because it separates the various parts of the whole data entity, but ultimately I like the ease of using Access and the quick deployment of new programming.

Besides, I'm not building something new- I will be using the table structure and the data in them exactly as they are now, as well as about 80% of the forms and reports, so no sense reinventing the wheel if I can just harness a more robust back end.

I just got a Developer Handbook series from Sybex that includes a book on developing Access 2000 apps for enterprises, so I'll read that and move ahead with the upgrade to SQL 2000 on the back end, and Access 2000 on the middle and front ends.

GLGCAG

 

[MichaelRed]

I would think that using ADO (disconnected) recordsets W/ Ms. A. (.MDB) FE, MS. A. "middleWare"(?) and an "Industrial Strength" sever would easily qualify as at least a 3-tier, while providing most any all of the benefits of the MS. A. convenience. Passing the disconnected recordsets around shouldn't be any different than the traditional , so the "efficiency" of the "Back End" (Server) is maintained, the "Convenience" of bound forms, Report generation, etc is maintained, and the "business rules" are implemented in the middleware.

As a truly minor variation, you could use VB for the middle ware.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[glgcag]

But if I'm using disconnected recordsets in the FE then I'm not using bound forms. (Which is my preference anyway.) The existing database is pretty much all bound forms and I'd like to move away from them when I convert to SQL Server 2000. In your suggested architecture would I still link the "middleware" .mdb to the SQL Server tables and then link the FE to the middleware? Or are you suggesting not linking the FE and just create a connection to the middleware .mdb using the ADO connection object but have the middleware .mdb linked to the SQL Server?

I'm just learning how to work with SQL Server and I think I'd prefer to call stored procedures to return recordsets, so couldn't I also just call them using ADO from the FE and bypass using a middleware .mdb too? (Though something tells me there are some benefits to having a middleware .mdb for certain things, though I can't think of what they may be . . .)

Ultimately I like this direction as it gives me better transaction handling and it seems like it would be a better environment for a large group of users.

 

[psemianonymous]

I wrote a message and then had to erase most of it. Let me just emphasize, n-tier isn't the keyword you're looking for, performance is. If you upsize to SQL Server, you'll be doing better immediately. If you do some performance tuning to see what queries/SQL statements are running the slowest, and tweaking the queries/SQL, you'll be doing better.

Switching to n-tier is not the issue.

I agree with everything MichaelRed said above except for his definition of n-tier: but here we get into a semantic argument. Again, Access reports & bound forms and direct use of ADODB/DAO recordsets (disconnected or not) violate the n-tier structure, so a true n-tier setup would use NONE of the above.

So whatever. Here's a neatish link to one definition of n-tier, for anyone who has managed to read this far:

http://www.15seconds.com/issue/011023.htm

Enjoy your day.


Pete

PS--if you're already not using bound forms, at all, the only thing keeping you in Access are the reports, and any queries you use to generate those reports.

 

[psemianonymous]

MAJOR SUMMARY AFTER RE-READING THIS ENTIRE THREAD

Do what suits you best. The only help I've given you is to say that most Access features violate the n-tier architecture. But I got too into the whole semantics. So let me SUGGEST some things:

1. Keep everything in Access, so long as it works, unless you have to do a lot of rewriting anyway.

2. Use your existing validation code that loads the forms and updates the database. If you want to prepare for some sort of move to an n-tier system, you can place the 'business logic tier' in separate code module(s) in your database, and make sure all your form code does NOT directly connect to the backend. Basically, pretend that you're connecting to a middle tier, even if the middle tier is sitting in the frontend.

3. For reports, just connect to the backend, or queries, or whatever. You're using Access for a reason, and one of those reasons is the great reports. This violates all the rules of n-tier, etc, but that's semantics. Who cares.


So anyway. We've gone far away from your original goal, which was to (1) speed up your database performance, and (2) strengthen the business rules consistency/general security of the database. I think the above suggestions (from this post) can really help you with #1 and #2, if such is your goal.

 

[glgcag]

Thank you foolio12, that was a good explanation and points to what is really a semantic argument as you said. I really don't want to go the n-tier route now that I've gotten all this feedback, and your idea of putting all of the business logic in separate code modules in the Access front end and then call the modules from the form code is great. This makes everything more modular and, like you said, easier to upgrade to a true 3 or n-tier architecture down the road.

I appreciate all of the feedback everyone has given me to straighten out the direction I need to go! Thanks!

GLGCAG

 

[MichaelRed]

And WHY can't you use "disconnected recordsets" for forms / reports? The ARE still recordsets, ans (as far as I know) can be passed around like unto any (other) "Object" in the pseudo object orientated archiciture. I haven't done it (or even tried to) as the spindly stuff I work on theese days hardly qualifies for the 'kiddie' strength of Ms. A. -much less- the industrial strength dbs.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[glgcag]

I think I mis-typed my post- I DO use disconnected recordsets, which is my preferred method of handling data. (Albeit, I only use DAO now, though I am studying up on ADO.) I am not into the bound forms method just because it has greater possibility for corruption and less transaction handling.

Using ADO and code modules for all data handling and SQL Server 2000 on the back end seems to me to be an "industrial strength" use of MS Access.

 

[psemianonymous]

MichaelRed:

It boils down to a semantic argument, not an argument on any other level. I disagree that your solution would be classified as n-tier. But I definitely agree that you can get most/all of the benefits using such a setup, and basically copied/forwarded those recommendations in my "MAJOR SUMMARY" post. So in the future, I'll try and be more careful when writing the word 'argument' in a post.

Anyway, enjoy your day.


Pete