Client/Server Development with Access

When you start using Access as a front end for a server database, the rules change. This article introduces the fundamental techniques for delivering effective client/server applications with Access.

There are many reasons why you might want to upsize your application to use a remote database server like SQL Server 7.0 or Oracle. Reasons that you might want to move your back-end database to a remote server database include, among others, the ability to support an increased number of users, to support transactional consistency, to handle larger and more complex databases, and to improve security, to name just a few. However, simply upsizing won’t guarantee that your application will automatically run faster, support more users, or be more secure. Continue reading

Posted in Old Material | Leave a comment

Building a Better Access Resource

A while ago, many of you took the time to fill out the Smart Access reader survey form. Thank you! Because of your contributions, I’m in a much better position to give you what you want — which is my goal as editor of this newsletter. I wanted to take this opportunity to give you some feedback about what I’ve learned about my job from you. As I read over the responses, I had a number of interesting surprises.

The most important statistic, for me, is the value you place on Smart Access. Sixty percent of the respondents felt we were in the excellent range on the scale. Another 34 percent felt we were in the above-average range. While it’s wonderful to know that 94 percent of our readership feels that we’re doing a better than average job, my job as editor is to provide material that will get you all to feel that we’re giving you the best information available.

As the in-depth source of technical information on Access, I wasn’t surprised to discover that many readers appreciate the technical depth we provide in this newsletter. Our readers count on Smart Access for technically deep articles from industry experts that they can’t get anywhere else. However, the survey revealed many ways to make this information more valuable. I discovered that many of you would appreciate having that information made more accessible to you: better lead-ins, more background sidebars, and better connection between the real world and the techniques that our articles demonstrate. One of the ways that we’ll be accomplishing that is by adding a sidebar called “Foundation Concepts” to many articles to provide additional background about the material discussed in the article. You’ll see the first examples of this feature in this issue, and we hope that you find that this new addition helps put the material into better perspective. You’ll also be seeing more examples in the text of how to apply the techniques discussed to different types of business situations.

In the survey, we listed a number of topics and asked you which ones we needed to provide more coverage on. Over the next year, you can expect us to start working our way along that list of topics to provide you with targeted information on each one. Another surprise was the number of readers who felt we weren’t giving enough attention to issues around developing systems: user interface, project management, database design, and so forth. Mike Gunderloy’s article on requirements analysis came into a lot of praise from this group of readers. This month’s article on data modeling is another step toward providing that information.

One surprise to me was how many readers are using Access to develop front ends for client/server databases. While Access does a pretty good job of burying the difficulties in working with remote databases, there are some special issues in working with client/server databases, and we’ll be discussing them in upcoming issues. Another surprise was the number of readers who want to integrate Access with other Office products — look for two articles on integrating with Excel in next month’s issue.

Finally, I really appreciate those of you who took the time to write in comments. All of the comments made valuable suggestions for article topics and ways we can improve the newsletter. Look for your suggestions to be implemented in future issues.

Back when I worked for a large corporation, I wished I had a boss who provided me with as much feedback and direction as you have. Thanks again.


Posted in Old Material | Leave a comment

An ADO Command Factory for Stored Procedures

There are some technical and bureaucratic obstacles to using ADO command objects. Since using stored procedures can make a tremendous difference to your Access application, Burton Roberts supplies a simple way to get around the first problem.

Recently, I read an article on writing “Efficient VB Code” where the authors wrote strings of SQL code in VBA modules and opened ADO Recordsets using those strings. Interesting, but certainly not efficient. Typically, the code looked something like this: Continue reading

Posted in Old Material | Leave a comment

An Access Explorer for SQL Server Tables

Rick Dobson follows up last month’s article with another tool for exploring SQL Server databases. Rick’s table explorer allows you to connect to any database, pull up any table in the database, and review or update the data in the table.

Last month, I demonstrated how to explore SQL Server instances, databases, and their objects with VBA and SQL-DMO (SQL Distributed Management Objects) from either Access database files or Access projects (see “A SQL Server Database and Object Explorer” in the July 2003 issue). As developers know, Access lets you build SQL Server applications, but it doesn’t include the tools that would allow you to manage your SQL Server database. This article not only shows you how to build a tool that will let you explore a table in any database at any time, but it will also show you how to program against SQL Server databases. Continue reading

Posted in Old Material | Leave a comment

Advanced Data Shaping

Mike Gunderloy provides some examples of ADO’s SHAPE_APPEND statement in action, including how to synchronize child and parent Recordsets. Mike also introduces the SHAPE_COMPUTE statement.

The SHAPE_APPEND command lets you work with hierarchical data (see my article, “Data Shaping: Handling Non-Relational Data,” in the December 1999 issue for the SHAPE_APPEND statement’s syntax). However, it’s hard to understand anything until you see some code. In this month’s article, I’m going to take a look at some examples of SHAPE statements. I’ll then introduce you to the SHAPE_COMPUTE statement. Continue reading

Posted in Old Material | Leave a comment