Here, Peter Vogel looks at the single most important change that you can make to your applications to make them run faster. It’s also the one mistake that gets made the most often.
I would say that most of my Access consulting work comes from clients who have applications that are running too slowly. So far, in every case where I’ve been brought in, I’ve been able to significantly improve the speed of the application. One of the reasons that I’ve been so successful is that most of my clients have made the same mistake: They’re retrieving too much data. I’d say that was the reason for every client, but my memory isn’t what it should be, and there’s the possibility that I’ve forgotten the one exception to this rule. This is the first area that you should concentrate on when trying to speed up your application. Continue reading
I keep hearing that developers aren’t using Access to create commercial applications anymore. That might be true, but you wouldn’t know it by me.
One thing that I keep being asked to do is review Access applications that developers are preparing to market commercially. I’ve seen three in the past three months, and they’ve all been very interesting, ranging from sophisticated service billing systems to specialized legal office management. There are lots of benefits to this side of my business: I get to meet some interesting people, I get to talk about Access with people who care about it, and I probably learn as much as I help. There are benefits to readers like you, too, as these developers can often be convinced to write up some of the techniques that they use for Smart Access. Continue reading
Stu Alderman explores the structure of your Access database and discusses the relationship between Access objects and System tables.
I’m currently developing an application that imports several files from mainframe sources at the State of Nevada. The application uses a number of tables in a local Access database to clean up and normalize the data before it’s transferred to a SQL Server database. I’d been using a very good commercial tool to document my Access database designs from FMS, but at the time I needed the tool, the version for Access 2000 wasn’t available. The result looks like this … read on Continue reading
Renaming a Bunch of Tables
I am working on a conversion database that requires linking to lots of tables in lots of databases with exactly the same tables. I then need to rename the linked tables to remove the Suffix of 1 (that appears on all linked tables that are the same) and give the new linked tables a new prefix . Here is the code to do that
Private Sub cboLive_Click()
Dim tbl As DAO.TableDef
For Each tbl In CurrentDb.TableDefs
If Right$(tbl.Name, 1) = "1" Then
tbl.Name = "MyPrefix_" & Left$(tbl.Name, Len(tbl.Name) - 1)
Code to strip out the DBO prefix on SQL Server linked tables
and for people doing conversions to SQL Server
Private Sub cmdRenameTables_Click()
'Code to move through the (linked) tables that have been created
'with a SQL server upsize and rename back to their original name
'designed for the DNO owner names
Dim tbl As dao.TableDef
For Each tbl In CurrentDb.TableDefs
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
Peter Vogel answers a question about querying a customer Survey table.
I have a table that lists our customers. Some customers have agreed to participate in a survey and others haven’t. I need to calculate the number of participants who have and the number of participants who haven’t and give both numbers as a percentage of the total number of customers, grouped by city. Whether or not a customer has agreed to participate is indicated by a Yes or No in the Survey field. Is there an easy way to do this? I’m using two queries right now, and I’d like a simpler solution. Continue reading