|
Subject:
|
From Access VBA to ....where?
|
|
Posted By:
|
swhite
|
Post Date:
|
9/18/2003 2:47:55 PM
|
Hi all,
I am a beginning VBA programmer and have written some pretty cool apps. But what I want to know is what is the next step in expanding and building on my skills as an Access developer. What if there are possibly 50 users that can access the app at once and therefore Access is not the best tool for the job? What would be the next tool to use? Would I use SQL server to store the data and just use Access or a VB app as the front end to query the data?
I want to learn more, but want to know what is the next logical step. I would appreciate advise that anyone can offer.
Thanks,
Sean
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
9/18/2003 4:07:02 PM
|
Why postpone the inevitable? Make the leap to the bleeding edge today! Install SQL Server Developer Edition, IIS, and start kicking out ASP.NET Web Apps in C#. That's how I'm spending my weekend (clawing my way up yet another precipitous learning curve). I have an interesting interview shaping up for next week, and thats what these folks are up to. They're still using some VB6 for in-house stuff, but their window to their clients is ASP.NET/C# web apps.
A few months back MS dropped the cost of SQL Server Developer Edition to $49.95. Its a must have. IIS installs on Windows 2000 up from the Add/Remove programs dialog in the Windows control panel. It runs as a Windows Service. Definitely do .NET.
Bob
|
|
Reply By:
|
swhite
|
Reply Date:
|
9/18/2003 4:21:35 PM
|
Bob -
Thanks for the advise! I'm not sure I can implement that technology in my current organization b/c I think, and please excuse my ignorance, I need to have the .NET framework. Or am I totally off base?
I am trying to move more towards a programmer position in my company and some of their fears on moving me forward is ... what happens when they need something that Access can't handle? What should I turn to? If a possible answer is VB6, then what is the front end and what is the backend?
I'm not sure how clear I am here, but if you can offer a little more advise, I would appreciate your opinions.
Thanks,
Sean
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
9/18/2003 4:48:34 PM
|
VB6 makes a fine front-end for SQL Server (or Access for that matter). VB6 front-ends that use ADO to execute stored procedures on SQL Server are exetremely common. VB6 will also allow you to compile nice middle-tier components (like data access classes, etc.) that promote reuseability, and hence your productivity (your boss will love that!)
With the next release of the Windows operating system, we'll all have the .NET Framework. So I wouldn't put off learning VB.NET or C# just 'cause the framework isn't installed at your shop yet. You'll be ready when it arrives.
Bob
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
9/18/2003 4:55:26 PM
|
...and it's easy enough (and free) to obtain.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
|
Reply By:
|
swhite
|
Reply Date:
|
9/18/2003 5:31:02 PM
|
Bob - thanks again for the advise. If I can ask you another question regarding VB...I understand you need to compile an .exe to use. Is it creating a user interface to use with the stored procedures on the server? And if I may, further show how little I know about VB's (outside of MS Office) practicle usage...because I really just use VBA in Access - how are the procedures stored on the server.
Bob, any help or explanation you can give would certainly help me get a better understanding on the process of creating a database, using VB to make it alive, and storing the data and procedures on the server. I would like to start reading on VB6, get compiler and go forward from there. The best book to get (b/c I see Wrox has several) and an inexpensive (or free) compiler suggestion would help too.
Thanks for helping,
Sean
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
9/18/2003 6:46:42 PM
|
Hi Sean,
You can create a bunch of project types with VB6. Probably the two most common are ActiveX .exe's (your standard executable file) and ActiveX .dll's (user-defined class libraries). The primary difference is that an .exe file is a true stand-alone appication (i.e., it runs in it's own Windows process), where as a .dll has to be loaded into an .exe's process to run. You can select both types of project template from the VB6 design environment. Also, VB6 ships with its own compiler (vbc.exe?, at least that's the VB.NET compiler). Compiling a program is as simple as selecting 'Create .exe' or 'Create .dll' from a menu. Its really pretty easy once you get the hange of it. You can then reference your custom .dll's in your custom exe's throught the project references dialog, just like in Access. Only catch is, unlike .NET components, the components you compile with VB6 are COM components (not IL assemblies managed by the .NET Framework) so they'll have to be registered on any client machine you deploy them to. Just pick up a good installer program. Also, the Data Environment report designer in VB6 is pretty lame, so pick up a good report writer app like a complete version of Crystal Reports. VB6, installer, report writer and you're good to go. Oh, and SQL Server of course.
What most people mean by a 'front-end' is an .exe file that contains your user interface elements (forms, etc.). Code behind the forms instantiates things like ADO connection and recordset objects (which are, of course, classes stored in there own registered COM .dll) that enable connecting to, and reading and writing to and from, SQL Server. Stored procedures can be easily executed in a variety of ways, the most common being the use of ADO Command objects. The stored procedures themselves are simply compiled Transact SQL statments that are saved on, and run on, SQL Server. They are created by writing and executing CREATE PROCEDURE statements in SQL Server's Query Analyzer client tool. They enable all query processing to occur on the server, and applications can fetch only the data they need using them. Stored procedures can also include a lot of procedural logic which is something you can't do with Access SQL.
Best book on SQL Server/VB6 development (in fact, one of the best development books I ever read) is:
Beginning SQL Server 2000 for Visual Basic Developers
By Thearon Willis
ISBN: 1861004672 (Wrox)
It is (was) a Wrox title, so I'm not sure it's still in print, but you can still get the cool download code from the Apress site.
Hope that helps a bit. It's a little tricky getting all this stuff connected right, but you can get it all going on your local machine relatively easily, and start experimenting with 'logical' 3-tier applications, even if you don't have access to the hardware to deploy the 'physical' version. Willis' book will walk you through developing a solid data access layer (middle-tier component) and compiling it into a .dll which the project .exe can reference. Its a good walk-through of the whole process. Hope you can find a copy.
Regards,
Bob
|
|
Reply By:
|
swhite
|
Reply Date:
|
9/18/2003 10:48:57 PM
|
Bob -
Cool! I did find the title you suggested on Amazon, yet it is not Wrox, it's from Apress pubisher - same author. After addition searching I found the title and by Wrox. So, if I want to start developing databases with VB what I need is VB6 (and this comes with its own compiler), installer, and report writer. Would you recommend any installer in particular and is this expensive? Does the book you recommended teach the VB language, or would you recommend something like Programming Microsoft Visual Basic 6.0
by Francesco Balena, ISBN: 0735605580? One final question - what if I don't have SQL Server on my home PC and just want to get started learning the theory...knowing how to do it?
I really appreciate the explanation you wrote, I have read many of your responses to others and you always take the time and are thorough with your response and opinion...thanks a million!
Sean
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
9/19/2003 1:59:04 AM
|
Hi Sean,
Glad you found the title. Apress is a fairly new publisher that has recently acquired a lot of the Wrox list. So you'll see a lot of old Wrox titles coming out in Apress editions. Apress also owns most of the code downloads associated with the old Wrox titles, and you can download the code from their site:
http://support.apress.com/
Scroll to the bottom of the page. You'll see "Books formerly published by Wrox Press Limited."
Thearon Willis' book may not be the place to start if you aren't already pretty familar with VB (though I'd never discourage anyone from reading over their head a bit, I do it all the time). However, if you're familiar with VBA, you're reasonably familiar with the VB language. Also, Visual Studio ships with the Microsoft Desktop Engine (MSDE) which is SQL Server without the client tools. So if you have Visual Studio, you have SQL Server (MSDE Edition). See:
http://www.microsoft.com/sql/msde/default.asp
Willis' book is kind of a beginning SQL Server book that introduces some advanced VB concepts. Unfortunately, if you want a beginning VB database book, chances are its using Access and (mostly) DAO, like John Connell's "Beginning Visual Basic 6 Database Programming" (also Wrox). You'll need ADO to work with SQL Server.
You can't go wrong with Francesco Balena. He really knows this stuff at a low-level and makes an effort to explain it. I'd recommend his VB.NET book over any I've seen (I'm reading it now).
Finally, you could get ahold of a copy of MSDE, and then install the NorthwindCS Access Data Project that ships with Access (look in the samples folder under your MS Office installation directory). ADP's are a file type that can be used to develop client/server applications using SQL Server as a back-end and Access as a front-end. They're a clunky and limited development tool, but they do provide some handy SQL Server designer tools that even surpass SQL Server's Enterprise Manager in a few respects. ADP's would be a way to go if your primary interest is learning a bit about SQL Server database objects, like stored procedures.
Don't worry about installers and Crystal Reports for now. You can do everything you want to do at the moment without them, and they aren't cheap.
Have fun! 
Bob
|
|
Reply By:
|
pgtips
|
Reply Date:
|
9/19/2003 3:21:54 AM
|
Hi Sean,
To get a head-start on this stuff, take a look at this page:
http://msdn.microsoft.com/library/default.asp?url=/code/list/sql.asp
in particular, right at the bottom, there is
"Microsoft ADO and SQL Server Developer's Guide" which is a good overview, and
"ADO Stored Procedure Add-in" which is a VB add-in that is really easy to use and generates VB code to run SQL Stored Procedures
hth
Phil
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
9/19/2003 11:51:33 AM
|
Hi Phil,
Cool add-in. If you bump into an ADO.NET version of the same let us know. Actually doesn't look like it would be too hard to re-write. Just need to figure out which intefeace .NET add-ins have to implement instead of IDTExtensibility? or maybe it can run in a .NET app via COM interop? Thanks for the download.
Bob
|
|
Reply By:
|
swhite
|
Reply Date:
|
9/19/2003 1:04:22 PM
|
Thanks guys, I really appreciate all your help! I'm looking forward to getting some reading material and getting started.
Sean
|
|
Reply By:
|
Braxis
|
Reply Date:
|
9/20/2003 6:14:04 AM
|
Sean
Am I correct in thinking that you're not coming from a programming background?
If I am, can I recommend that you spend at least half of your learning time discovering how to program well, and all the other processes and practices that will make you a good software designer?
You can know your chosen language inside and out and still write absolutly stinking programs.
I've no book recommendations to give, as it's a long time since I did my degree - but what I learnt has been relevant all through my computing career.
Brian Skelton
Braxis Computer Services Ltd.
|
|
Reply By:
|
pgtips
|
Reply Date:
|
9/22/2003 3:06:15 AM
|
quote:
Originally posted by Bob Bedell
Hi Phil,
Cool add-in. If you bump into an ADO.NET version of the same let us know. Actually doesn't look like it would be too hard to re-write. Just need to figure out which intefeace .NET add-ins have to implement instead of IDTExtensibility? or maybe it can run in a .NET app via COM interop? Thanks for the download.
Bob
Hi Bob,
I'm not using .NET currently, but maybe this article will help you figure out how to write a .NET add-in.
http://www.fawcette.com/vsm/2002_10/magazine/columns/desktopdeveloper/
rgds
Phil
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
9/22/2003 8:32:08 AM
|
Thanks Phil,
Turns out I have that issue of VS Mag (used to be Visual Basic Developers Journal). Thanks for the code.
Bob
|
|
Reply By:
|
swhite
|
Reply Date:
|
9/22/2003 9:39:44 AM
|
Braxis -
Answering your question...No, My formal education is not in programming; it is in finance and economics. I am more inclined to agree with you on
quote:
Originally posted by Braxis
Sean
You can know your chosen language inside and out and still write absolutly stinking programs.
Since we are working with data and numbers most of the time in business to illustrate trends, manipulate, disseminate and forecast - database programming is what we rely on to accomplish business scenarios. I realized this many years ago while still at the University and working as an engineer. I started with automating Excel spreads for accounting and marketing and soon realized the potential of Access and VBA. So I figured, what more can I do as a programmer but to try and learn all of the idiosyncrasies of the language. I have written some pretty stinking programs that were not optimal, but as I learned more my apps began to run efficient through efficient code. Yet, that is not to say the programs did not achieve their objective, they did indeed. The proof was in the puddin'- the user was happy and the app did what we set out to do.
Now - I am certain that there have been apps that I have written that can be improved, but my initial question was - where do I go from here? What if I develop an application in Access that the entire staff of the organization have the potential to access at the same time. Well, then I don't think Access is the route to go. After some thoughtful advise, I think I am on the right track in learning VB.net and SQL server, together, to accomplish my database designing needs.
I bought a book several months back - Visual Basic.Net Database programming by Evangelos Petroutos and CYBEX publishing. I just started it and I find it to be very informative. I think VB.Net is the route to go, i.e. to grow as a programmer and to step up to the latest technologies.
But if I may ask, what specifically - if you would be generous enough to share - are some of the processes and practices I can implement, as I continue to learn, that will make me a good software designer?
quote:
Originally posted by Braxis
Sean
If I am, can I recommend that you spend at least half of your learning time discovering how to program well, and all the other processes and practices that will make you a good software designer?
I appreciate your advise and comments. Any thoughts you have would be a benefit to me. Thanks again for your help. 
Sean
|
|
Reply By:
|
Braxis
|
Reply Date:
|
9/22/2003 5:28:43 PM
|
Sean
Sincerest apologies if I've come across as patronising - it wasn't my intention at all. But I have had to maintain, and have undoubtedly written, some appallingly badly designed programs.
Especially, I have to disagree with this:
quote:
The proof was in the puddin'- the user was happy and the app did what we set out to do.
User happiness is an important and, all to often, elusive goal - but not always the prime one. I've never designed a system that hasn't changed & grown once released, so scalabilty and maintainability are usually near the top of the list. Even the most basic system has to be looked after by someone, and it may well be you that has to do it!
Anyway, here's a list, incomplete and in no particular order, of the things I've found useful, or wished I knew more about:
- Relational database design
- Code modularisation
- Extracting Requirements from users
- OO design principles
- Data Protection law
- Code reuse
- Disaster recovery procedures
- Collaborative software design
- Automated error tracking
- Keeping users enthusiastic
- Formal documentation
- Good user interface design
- 'Black Box' programming
- Selling technical solutions to management
- Help file authoring
- Creating test plans
- Effective data backup
- Manual fallback procedures
- Managing test teams
- Coding standards
- Naming conventions
- Network security
- Disability law
- Costing software
- Refusing user requests
- Database security
- Formal specification
- Process writing
To finish off, here's an extremely piece that covers a few of the points mentioned above http://mindprod.com/unmain.html
Brian Skelton
Braxis Computer Services Ltd.
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
9/22/2003 8:43:07 PM
|
Hi Sean,
I can’t resist the impulse to add one concept to Brian’s excellent list. It’s actually more a generalization than an addition in that it incorporates concepts like code modularization, OO design principles, code reuse (and lets not forget maintainability), and Black Box programming.
What I have in mind is the idea that well designed applications will implement a “logical n-tier architecture”, that is, they will separate functionality into well defined roles or groups in order to increase code clarity and maintainability. There are many ways to accomplish this goal, but its good to work with at least the following four divisions in mind:
- User Interface code– that accepts user input and then provides it to the business logic (this is where your windows forms or web forms live).
- Business Logic code – that includes business rules, data validation, manipulation, and processing (this is where your business objects live).
- Data Access code – that interacts with the data management tier (DBMS) to retrieve, update, and delete data (this is where ADO/ADO.NET objects live).
- Data Storage and Management – that handles the physical creation, retrieval, update, and deletion of data (this is your DBMS, such as SQL Server).
The emphasis here is on logical architecture. The UI, Business Logic, and Data Access code can all reside in a single project on a single computer, but each ‘tier’ is encapsulated in one or more class modules that provide a specific type or functionality. Tiers communicate with one another by calling the methods of classes in other tiers. Also, these are not hard and fast divisions. For example, you may have data validation occurring at the UI, business logic, and data storage tiers. The idea is simply to achieve as much logical separation between types of functionality as possible.
This approach will save you hours and hours of coding by enhancing your codes reuseability and maintainability. The idea is to work at building a code base, or framework, that you can write once, and implement over and over again. For example, a well designed framework won’t even care what type of interface it is communicating with (windows forms or web forms). The UI simply calls methods of objects residing in the business tier, but is oblivious to the implementation details of those methods. The same relationship obtains at lower levels of the architecture.
In short, classes should be the building blocks of your programs. Dlls/assemblies that contain your classes in a compiled state are your units of deployment. So its real important to familiarize yourself with the fundamentals of class design, and object-oriented programming principles in general. Unlike VB 6, VB.NET finally offers all the features that anyone would expect from a mature object-oriented language. All the .NET languages are born equal in this respect, so VB.NET is a fine language to build truly object-oriented programs with.
Regards,
Bob
|
|
Reply By:
|
pgtips
|
Reply Date:
|
9/23/2003 4:18:22 AM
|
Don't forget about debugging too. there are loads of books/articles about designing/programming but ones about debugging are rare. the more coding you do, the more time you'll spend debugging, so put some thought into it up front. program defensively - don't assume that files/databases/objects will always be available, for instance. A lot of run-time error messages are very unhelpful, e.g
Type mismatch
ActiveX component can't create object
file not found
permission denied
you'll spend a lot of time tracking down which files/objects etc these sort of errors refer to, so save yourself some time by spending a bit more coding time anticipating problems. Debug.Assert can be your best friend
rgds
Phil
|
|
Reply By:
|
swhite
|
Reply Date:
|
9/23/2003 1:53:12 PM
|
Brian -
Hey, no problem - I took your comment as good advise, and look how much more dialogue and advise that was generated from your comments. Thanks for you help and opinions. As a beginning programmer, I am always receptive to fundamental concepts and new ideas.
Thanks to everyone for including an extensive list of topics I should learn more about. I am sure this will prove to make me a better programmer.
Many thanks to Brian, Phil and Bob! 
|
