I was looking at SQM data about file size last week and ran into an interesting question. We capture the size of the file every time Access opens the file in SQM. It is interesting data as it provides insights into the size of a typical existing app. I wanted to bucket the ranges into something more readable with smaller buckets at the low end and wider buckets at the top end. Something like:
(The actual numbers have been changed.)
First, I created a table called FileSizeRanges that defined the custom ranges.
The size of the files was stored in a table called LOCALDATA_FileDetails in a field called SizeOfFileKb.
I created three queries:
qryFileSizeRanges
SELECT LOCALDATA_FileDetails.[SizeOfFileKb], FileSizeRanges.*
FROM LOCALDATA_FileDetails, FileSizeRanges
WHERE [SizeOfFileKb]>=MinSize And [SizeOfFileKb]<MaxSize;
qryFileSizeRangeCount (shows friendly name for each group)
SELECT ID, MB, Count(SizeOfFileKb) AS RangeCount
FROM qryFileSizeRanges
GROUP BY ID, MB;
qryFileSizeRangeDisplay (shows the MinSize and MaxSize values for the friendly name)
SELECT MinSize & "-" & MaxSize AS Range, RangeCount
FROM qryFileSizeRangeCounts;
The qryFileSizeRangeCounts query takes 2 minutes and 31 seconds to rip through 3.5M records on my ThinkPad T60 running Windows 7 (BTW – I’m loving Win 7). Seems like reasonable performance to me. I turned on the totals row to get the total count and pasted it into Excel to visually clean up.
You don’t need to use the qryFileSizeRangeDisplay query if you want to use the friendly names in the FileSizeRanges.MB column for friendly display names. In my case I had converted KB into MB and provided some friendly text to make it more readable.
Today’s guest blogger is Colin Wilcox, writer for Access Training on Office Online.
When you query multiple tables for data, you sometimes see a message about “ambiguous outer joins.” The message tells you to create a separate query that performs one of the joins, and then include that first query in your SQL statement.
You're seeing that message because of the join structure in your query. Whenever an outer join points to a table, and that table also participates in another join of any kind, your query can't run. Access doesn't know which join to make first, because it can't match the records returned by each join.
When you see that message, look at the joins involved in your query. Any time you have an outer join on one side, and a join of any kind on the other, you have an ambiguous structure. Here are some visual examples. All of these structures will cause Access to display the “ambiguous joins” message:
A left outer join between Table1 and Table2, and an equi-join between Table2 and Table3.
A right outer join between Table3 and Table2, and an equi-join between Table1 and Table2.
A left outer join between Table1 and Table2, and a right outer join between Table2 and Table3.
To solve the problem, create two queries. In the first, retrieve the data from the tables that participate in one of the joins. In the second query, use the first query as part of your record source, and retrieve data from the tables involved in the other join.
Have an Access Power Tip that you want to share? Send it to Mike and Chris at accpower@microsoft.com.
There is a good article that recaps the recent O’Reilly Velocity 2009 conference on web performance and operations. I particularly liked this quote:
Phil Dixon, from Shopzilla, had the most takeaway statistics about the impact of performance on the bottom line. A year-long performance redesign resulted in a 5 second speed up (from ~7 seconds to ~2 seconds). This resulted in a 25% increase in page views, a 7-12% increase in revenue, and a 50% reduction in hardware. This last point shows the win-win of performance improvements, increasing revenue while driving down operating costs.
There are probably fewer ways to increase satisfaction from people that use your application than good old block and tackle performance improvements. Here is a help article with lots of useful ideas to explore making your app faster.
Do you have success stories of delighting customers by making your app faster? What techniques did you employ?
I want to give a big shout out to Ron Disandro for the redesign of the Access Team blog. He worked on Access 14 templates for the last 9 months and recently updated our bland previous design. We wanted to come up with a more modern and fresh design that reflects the efforts in Office 14. I hope you all enjoy the new face lift.
A question came in today from Michael about terminal services scale:
I have been using the Access 2003 runtime in conjunction with the sagekey scripts for a couple of years. My deployments have had pretty light concurrent user counts, 10-20 is typical. I have a client that would need it to scale to at least 100 concurrent users, possible 200-300. I know the scalability of an app would be impacted by what the front end is doing and so forth. The hardware side can be whatever it needs to be, so I'd assume that part of the equation will be ok. The backend is sql server and I'm sure it can handle the load fine. I use a mix of linked tables with plain Access queries against them, and stored procedures in sql server invoked via pass-through queries. Access to the app is provided via Terminal Services with a helping hand from Citrix PM.
In the system I use, each user gets their own copy of the Access front end. Thus, as far as the scalability concern goes, the concern I have is that the terminal services box which hosts the Access front ends will be running many copies of Access and/or the application in memory. I just don't know enough about the way Access and Windows and TS work together to know how that all plays out. It's not so simple to set up a test bed for this... But maybe I'll need to do that.
I was hoping that you might have some insight into this issue since you have inside exposure to Access and a good tap into the community of Access developers? What are the largest concurrent user counts that you've heard of? Any input is appreciated.
Anyone from the community have experiences you would like to share with Michael?
Today’s guest blogger is Michael Groh, co-author of the popular Access 2007 Bible.
The VBA language is not, by default, case-sensitive. A statement such as
CBool("XYZ" = "xyz")
will always return True. You might come across this issue in cases such as the following:
If strOne = strTwo Then
' Perform some operation here
End If
Consider what happens if it's important to know that the strings differ only by case. By default, VBA will never report a difference in the strings based only on the case applied to the string variables.
One easy fix is to use the InStrB ("in string byte") function, which considers the strings based on byte value (where "x" is different than "X"). By contrast, the InStr function considers just character value ("x" is the same as "X").
The transformed If statement becomes:
If CBool(InStrB(strOne, strTwo)) Then...
This statement takes the output of InStrB() and converting it to a Boolean (True or False) expression. When InStrB returns 0 (no match found), CBool converts the expression's value to False. If InStrB finds the strings match (by case and by character) the expression evaluates to True.
You'll find that using the InStrB() function is much faster and easier than writing a VBA function that parses each string and does a binary or ASCII comparison on each character in both strings.
Get your favorite Power Tip on the blog! Send it to Mike and Chris at accpower@microsoft.com.
Today’s guest writer is Garry Robinson, Access MVP and proud new owner of past Smart Access journals. I asked him to tell the community about the new home for Smart Access.
Hello Clint and Microsoft Access lovers,
I am happy to announce that my company has purchased the exclusive rights to Smart Access. A great magazine that was put together by over a 100 Access professionals for more than 10 years. Purchasing all these articles (300+) realizes an ambition that I have had for years to rapidly grow our web site and knowledge based products.
So once I purchased the materials, my first goal was to rebuild the pdf's of the magazines and the downloads into collections so that people could refer to the materials on their own computers. That task is now completed and all this content is now available digitally at the following location. If you are interested in purchasing the products, use this special Access blog coupon code AB-45K2D46T6J till the end of July for a discount.
http://www.vb123.com/smart/
For those of you who are interested in what we do going forward with the Smart Access content, join my newsletter or twitter posts at http://www.vb123.com/news We hope to start posting material from the magazines in a well structured site from mid-August.
Access lives on and on…
Garry Robinson
Microsoft Office Access MVP 2006-2009
The folks @ UtterAccess have produced an useful article titled The Beginner’s guide to ODBC.
This is an attempt to equip the developers who want to use Access as a front-end client to any RDBMS (Relational DataBase Management System) backend (ie, SQL Server, DB/2, Oracle, MySQL, PostgreSQL) with the right questions to explore and ask during design and development. As we cannot cover every special case or all the nuisances that each developers must deal with, it is hoped that,by reading over this document, you will be better equipped to find the needed answers for your specific case. The article assumes that you are familiar with developing an Access application, understand the fundamentals of data types used in Jet and VBA and know basic SQL and is familiar with Data Access Objects (DAO) library and/or ActiveX Data Objects (ADO) library, but are otherwise new to working with ODBC data sources. The objective is to provide you with a set of questions about design and development and know how to ask them.
The first thing that you should be familiar with is potential issues that can crop up when developing against a RDBMS. This is, in no way, a complete list, but hopefully broad and high-level enough to give you an idea of where to start in investigating or anticipating any problems they that you may encounter in development.
About a month ago, we posted a Power Tip from Edwin Blancovitch about creating shortcut menus by using VBA. However, if for some reason you’re not interested in cracking open the Visual Basic Editor, there is a way to create shortcut menus (the menus that appear when you right-click an object) just using macros. See this article on Office Online to see how to get started.
Have an Access Power Tip that you want to share? Send it to Mike and Chris at accpower@microsoft.com.
Once of the most interesting trends in the web application space has been a refocus on user experience improvements. Often times Access gets a bad reputation for not keeping up with the times from a look and feel perspective.
Our good friend Brandon from OpenGate Software created a great white paper (with sample database) on how to bring these improvements to your existing Access databases. Here are some highlights:
Fade-In/Out
Dynamically Expand/Contract Form Elements
Hover Effects
Click here to get the whitepaper and sample database.
Recently Maarten a blog reader asks:
TempVars (introduced with Access 2007) are great! But I have some problems in using them.
I created a query with a TempVar as criteria. When I run the query it shows the correct data. But when I open the query in VBA using OpenRecordset, I get the message: “Run-time error 3061: Too few parameters. Expected 1.”
This is the expected behavior. TempVars are defined in the scope of the Access Application object. When you use the CurrentDB.OpenRecordset method, the resulting recordset is defined in the scope of the ACE database engine (DAO).
Here is a workaround. Create and form based on the query and assign your recordset to it. Something like this…
DoCmd.OpenForm "FormName", WindowMode:=acHidden
Set rs = Forms("FormName").Recordset
... Do stuff
DoCmd.Close acForm, "FormName"
Enjoy!
Today’s guest blogger is Vinny Malanga. Vinny is the CTO of IMI Software, Inc., which specializes in software development for the property management industry.
I recently received a call from a client asking for help on how to use the SQL Server bigint data type in a disconnected Access .accdb file. Here’s the scenario:
There’s a SQL Server table that is using the bigint data type as an identity column. In this scenario, the Access database is completely disconnected from the SQL Server 2008 database. There is no use of ODBC or linked tables. All data access is handled using ADO.
As explained in SQL Server 2008 Books Online the range of the bigint data type is -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807). The problem is that there is no equivalent Access data type to the SQL Server bigint data type. But, you are not out of luck. There are a couple of ways that you can handle this. Both options are explained below.
The first option is to have all stored procedures or views which return the bigint value to Access explicitly cast the bigint value to a varchar. Access can then utilize the data as a string value. When sending the value back to SQL Server (such as when updating or deleting a record), the stored procedure simply casts the string back to a bigint so it can be used appropriately in SQL Server.
The second option is to utilize the Variant data type in Access. I know what you may be thinking at this point. At first glance, many of you may not like the sound of this, and as a general rule, I tend to agree, but it is worth exploring this option. By taking this approach, there is no casting in SQL Server necessary. The Variant data type can hold the bigint value that SQL Server sends to Access because the Decimal data type is a subtype of the Variant data type. You can’t explicitly declare a Decimal data type in Access but the Variant data type does store the value as a decimal. You can see this by running the VarType(VarName) function. The result is 14 which is the VB constant for vbDecimal. You could also run the TypeName(VarName) function which returns Decimal. I’ve included these methods in the sample access database in the cmdGetName_Click() event. Just uncomment them and the result will print in the debug window.
Another small caveat to using a variant is if you are going to display the value in a textbox. The large bigint values will display in scientific notation. For example, if you are displaying the bigint value 9223372036854775807, an Access textbox will display this as 9.22337203685478E+18. However, the actual value is stored appropriately so this isn’t an issue if the field is hidden. For display purposes though, you could cast the value to a string using CStr(Expression) when binding to the textbox (again, a little more overhead), or simply display the value in a label control.
As a general rule, using variants can degrade performance. Since the data type isn’t explicitly declared, VBA has the additional overhead of maintaining which data type it is actually storing. Also, variants are large, 16 bytes so they have a higher memory footprint. However, variants are there for a reason and sometimes they do have their purpose. This could be one of them. I’m not totally opposed to this method, but personally I try to avoid using the Variant data type when another option is available. So, personally, I tend to gravitate toward the first option.
I’ve supplied some SQL scripts and a sample Access database applying both methods There are also some examples in the sample database on the use of classes, disconnected ADO Recordsets, and general object oriented programming principles.
Here are the scripts to create the objects in a SQL Server database.
-- 1 Create a table:
-- I have set the identity to begin at the largest bigint value
-- for demonstration purposes.
CREATE TABLE [dbo].[tblName](
[RecordID] [bigint] IDENTITY(9223372036854775807,-1) NOT NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_tblName] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Create four Stored Procedures.
CREATE PROCEDURE [dbo].[procGetNameUsingString]
AS
BEGIN
SET NOCOUNT ON;
-- bigint range is -9223372036854775808 to 9223372036854775807
-- Cast RecordID to varchar(19) when sending to Access
-- 19 is the number of characters needed to hold the largest
-- length bigint value. Use 20 if you're going to use negative
-- numbers to account for the negative sign.
SELECT CAST(RecordID AS varchar(19)) AS RecordID, Name FROM tblName
END
GO
CREATE PROCEDURE [dbo].[procGetNameUsingVariant]
AS
BEGIN
SET NOCOUNT ON;
SELECT RecordID, Name FROM tblName
END
GO
CREATE PROCEDURE [dbo].[procUpdateNameUsingString]
(
@RecordID varchar(19),
@Name varchar(50),
@ReturnValue bit OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
-- Declare a variable to hold the bigint value
-- and assign it the result of casting the incoming
-- string representation of RecordID back to a bigint value.
DECLARE @RecordIDBigInt bigint
SET @RecordIDBigInt = CAST(@RecordID AS bigint)
-- Update the record using the bigint value.
UPDATE tblName
SET Name = @Name
WHERE RecordID = @RecordIDBigInt
SET @ReturnValue = @@ROWCOUNT
END
GO
CREATE PROCEDURE [dbo].[procUpdateNameUsingVariant]
(
@RecordID bigint,
@Name varchar(50),
@ReturnValue bit OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
UPDATE tblName
SET Name = @Name
WHERE RecordID = @RecordID
SET @ReturnValue = @@ROWCOUNT
END
GO
-- Create a view (example if using views
-- rather than stored procedures).
CREATE VIEW [dbo].[vwGetNameUsingString]
AS
SELECT RecordID, CAST(Name AS varchar(19)) AS Expr1
FROM dbo.tblName
GO
Once you’ve run the scripts, your SQL database will contain one new table, four new stored procedures, and one new view. You’ll want to add a record to the newly-created table to make use of the form in the sample Access database.
You can download the sample Access database from here. You should change the connection string in the Connect subroutine of the clsDataAccess module to suit your environment. Note that the code requires a reference to the Microsoft ActiveX Data Objects 6.0 Library. The sample database already contains the reference; this is something to keep in mind if you implement the class in your database.
Sal Ricciardi of the Access Developer Documentation team has produced a couple of articles for MSDN that address two commonly-asked date-related questions about Access:
Thanks Sal!
Today’s guest writer is Shawn Sullivan from the Office Shared team. I have asked him to talk about the work he has done to help organizations move to newer versions of Office.
In March, we made a post about Office Migration Planning Manager (OMPM), which is a suite of tools to help analyze your environment for migration to Office 2007. Its primary function is to scan Word, Excel, PowerPoint, and Access files in the Office 97-2003 binary file format, and provide analysis of any issues those files might experience being converted to the Office 2007 OOXML format, as well as any possible issues those files might exhibit simply being used in Office 2007.
OMPM provides you with several techniques you can leverage specifically concerning migrating to Access 2007. Here’s a short list of how the tool can help you plan for migrating to Access 2007:
Identify files are in your environment
As the March post mentioned, OMPM can be used as a file inventory tool to identify all Access files (as well as Word, Excel, and PowerPoint) in your organization. Both the light and deep scan modes can accomplish this task.
Analyze Access files for issues
OMPM’s deep scan mode will actually crack open files from Access 95-2003 and scan them for potential migration issues. The full list of issues as well as pointers to documentation are at the bottom of this post.
Develop plans to deal with issues
OMPM can generate reports from scan data which show you what migration issues are present where in your environment. In combination with OMPM documentation, this allows you to test and plan for migration.
OMPM can be downloaded here so you can try it out and see what it can do to help assess your Access 2007 migration. It requires no installation, although SQL 2000, 2005, or 2005 Express are required. SQL 2005 Express is available for free to help you get started looking at OMPM, and works well for smaller (<100,000 files) scan analysis.
Screenshots and error details
Here’s a few shots of OMPM reports (personally identifiable data obscured). First, a report showing all scanned files and any issues found:
And second, a closeup of a report listing all issues found in a scan, and their frequency:
Finally, here’s a list of the errors OMPM can generate about Access files.
| IssueID | IssueText | UserIntervention | IssueType |
| 1 | Too many code or class modules | Required | Error |
| 2 | Sort order and system language do not match | Likely | Warning |
| 3 | Replicated database | Required | Warning |
| 4 | Database is an MDE file | Required | Error |
| 5 | DAO 2.5/3.5 compatibility layer | None | Note |
| 6 | Microsoft Jet SQL help | None | Note |
| 7 | Additional References | Likely | Warning |
| 8 | Missing References | Likely | Warning |
| 9 | Reserved Name in Form/Report/Macro | None | Note |
| 10 | Linked Tables | Unlikely | Warning |
| 11 | Database in uncompiled state | None | Note |
| 12 | Backup database | None | Note |
| 13 | Sample database | None | Note |
| 14 | Old Database | None | Note |
| 15 | Secured Database: User Level Security | Likely | Error |
| 16 | Secured Database: Database Password | Likely | Error |
| 17 | System Database | None | Note |
| 18 | Unsupported legacy format | Likely | Warning |
| 19 | Unsupported legacy format - forms, reports, and modules will be lost | Required | Error |
Errors 1-17 are the complete list of errors which can be generated by the Access Conversion Toolkit, OMPM’s predecessor. Documentation for 1-17 can be found in the Access Conversion Toolkit. The documentation is Flash-based and requires the installation of the toolkit from here.
Errors 18 and 19 are new to OMPM and mean
18 – Access 2007 no longer opens files created in Access 2.0.
19 – Access 2007 will open a file that was created in Access 95, but will not read its forms, reports, or modules.
Summary and links
Here are links to useful sites with more info on OMPM:
If you have any questions or comments, please feel free to leave them in the comments section of this post or on the wiki. And if you have helpful tips of your own about OMPM, please share them on the wiki!
Last week Joel Graff shared his story on how he used Access to track salt usage for the Illinois Department of Transportation. Some of you wanted to know more about the custom class that he uses to ease the creation of similar forms.
Joel has agreed to share a sample implementation of his custom class.
It basically uses three interrelated lookup tables to demonstrate the class with varying degrees of complexity (three different examples). I made use of the nifty article on writing help using reports to provide some information on how it performs. I’m no guru at interfaces / tutorial writing, but hopefully what I’ve sent is a sufficient example.
I suppose it's homebrewed enough anyone who knows better would get a chuckle or two off of it, but I'd love to see what someone else thinks.
You can download Joel’s sample database here.
