09-19-06, 15:44 #1Registered User
- Join Date
- Jan 2004
Unanswered: SQL Server Front End - VB/MDB/ADP?
Below is a post by blindman from my other "mentoring" thread (http://www.dbforums.com/showthread.php?t=1608569). I wanted to respond to it but not in that thread:
Yeah, but if he's going to go unbound he might as well develop in .Net rather than Access. You lose half the convenience of MSAccess as a front end if you use unbound forms and controls.
Besides, he's trying to learn SQL Server, not front-end development. Learning both at the same time may be a bit much. I'd suggest he stick with his idea of converting the Access database to SQL Server back-end.
But, you should definitely convert the Access front-end to a Microsoft Access Data Project, rather than a classic mdb file.
Going unbound would increase your application performance, but probably not noticably unless you have scores of uses, and since you are currently using straight MS Access I'd guess you have no more than five or six simultaneous users, right?
I actually use ADO quite a bit in my current DB app and bound controls as well so it really wasn't a new concept. However using strictly ADO produces alot more work and code. And like blindman says it pretty much takes all the ease of Access away and VB.NET for that matter too. I mean all the wizards are useless. So I have two questions:
1. When going to all ADO you are pretty much recreating what Access already does for you right? Coding in ADO is more work right? I mean you have to setup connections and fill fields and listboxes manually in code right? I don't mind the extra work I just want to make sure if I'm going to be doing all this extra coding that it is the right thing to be doing.
2. MDB vs. ADP - From what little I've read Access Data Projects are made for use with SQL Server. It is even more useful if you don't/can't actually work in SQL Server. But again if I'm using ADO it strips all the usefulness of the ADP's doesn't? I don't need ADP's to create views or sprocs. I can do all of that through the management studio. So does it really matter if I use an ADP or MDB as my front end if that is all it is going to be is a front end?
Lastly I wanted to use VB 2005 as the front end but we utilize a ton of reports. As far as I know Crystal Reports or something like it has to be used with VB 2005. Or does VB 2005 have some built in reporting capabilities? I thought I saw some report designer for VB6. Anyway I decided to stick with Access because I am familiar with its reporting capabilities.
I should note that I have VB 2005 Express and SQL Server Express. Right now my company is not putting any money into a higher version.
Let me know what you all think.
Last edited by gwgeller; 09-19-06 at 16:01.
09-19-06, 16:44 #2another indirection layer
- Join Date
- May 2004
In professional UI design, you should never bind controls to the underlying data storage engine. Why? because then the UI is tightly coupled to the data, and any change to the schema will break your UI. Also it makes the UI code very difficult to test and maintain.
I would recommend coming up with a wrapper class for each object in your database, where each wrapper knows how to populate and update itself from the underlying tables. To further insulate your database from clients, you should limit all access to the db to stored procs (I don't think they exist in access though, so if you stick with access, can't do this). That way you can change tables around and clients won't break (unless you forget to fix the sprocs they are using!)
One huge benefit of doing things this way is that the wrapper classes can be tested separately from the UI with a test harness framework like NUnit. You can't test UI code in an automated way very easily, so if you mix the UI code with the data access code, testing will be much more painful and tedious, and likely will never cover all the cases.
Another benefit is that if you decide to move from Access to SQL Server after the UI is written, your UI won't break - just fix your wrappers to select/insert/update/delete data from SQL Server instead of Access and your app will work!
If you separate data access into wrapper classes, the UI would only deal with the wrapper classes. UI code can get very messy - it only gets messier if you mix data access code with UI code.
Last edited by jezemine; 09-19-06 at 16:49.
09-19-06, 17:12 #3Window Washer
- Join Date
- Nov 2002