Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 18th, 2003, 02:47 PM
Authorized User
 
Join Date: Sep 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default From Access VBA to ....where?

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

 
Old September 18th, 2003, 04:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old September 18th, 2003, 04:21 PM
Authorized User
 
Join Date: Sep 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old September 18th, 2003, 04:48 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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



 
Old September 18th, 2003, 04:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

...and it's easy enough (and free) to obtain.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old September 18th, 2003, 05:31 PM
Authorized User
 
Join Date: Sep 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old September 18th, 2003, 06:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old September 18th, 2003, 10:48 PM
Authorized User
 
Join Date: Sep 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old September 19th, 2003, 01:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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




 
Old September 19th, 2003, 03:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Sean,

To get a head-start on this stuff, take a look at this page:
http://msdn.microsoft.com/library/de...e/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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
Access VBA Teqlump Access 2 September 10th, 2004 02:33 PM
Access vba rookieat this Access VBA 6 February 7th, 2004 03:01 PM
Access VBA help danielwajnberg Access VBA 3 September 1st, 2003 09:46 AM
Access XP VBA compatibility issues w/ Access 2000 bourgeois02 Access VBA 1 August 19th, 2003 04:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.