We are very pleased to announce the creation of our new (and free) Microsoft Access Developer Help Center. It contains our many popular papers with tips and techniques for becoming a better Microsoft Access developer, programmer, and administrator. It covers the whole life cycle from strategic planning of how MS Access fits, database and table design, form and report tips, programming do's and don'ts, deployment, best practices, performance tips, etc.
In Access 2010 and 2007 databases (ACCDBs), the Attachment field type lets you attach files (documents, files, etc.) to records in your database. When you open the table in Datasheet view, the attachment fields display a paperclip as the column header.
Microsoft released service pack 1 (SP1) of Office 2010 late June 2011. Information on the update and download instructions are in their Knowledgebase article 2460049. The KB article was last updated yesterday.
Unfortunately, we and some of our customers have encountered problems after installing the update. While we haven’t had exhaustive testing with SP1 and tentatively believe everything is okay with the 32 bit version, there are definitely compatibility problems with the 64 bit version of Access 2010. In particular, ACCDE files created in the original release of Access 2010 64 bit no longer run under SP1. This error message appears:
The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. You should back up your database before attempting to open the database and delete the VBA project.
Obviously, with an ACCDE file, unlike an ACCDB file, you can’t modify the VBA project. It requires rebuilding the ACCDE from SP1.
So far, we have not experienced problems with our 32-bit ACCDE files between the original and SP1 Access 2010 versions. Some of these issues have been noted in these articles:
This is a Microsoft KB article discussing the problem which impacts ACCDE, MDE, and ADE files created with Access 2010, 64-bit. For us, when we create MDE and ADE files for use across multiple versions of Access, we’ve used Access 2003 so that still works fine. The only time we use Access 2010 64-bit version is when we’re creating an ACCDE specifically for Access 2010 64-bit users.
This issue impacts two of our products’ Access 2010 64 bit versions:
We have created new builds of these products and are undergoing internal testing and QA to verify they work with SP1. When ready, we’ll release free updates of these products to existing customers. If you need it earlier, please contact our support team. Note that this does not impact the Access 2010 32 bit version.
Regardless of whether you are using our products or not, please be aware of the compatibility issues if you’re using the 64-bit version of Access and deploying ACCDE files. You will need to make sure your Access version is in sync with your users’ version. Unfortunately, this bug occurs before any of your code can run to provide instructions to your users or offer a graceful exit.
Not sure what version you have installed? Run Access, select the File menu, and click on Help. The version informing is shown on the right side and will show (SP1).
Download the free preview of Total Visual CodeTools 2010 for Microsoft Office/Access 2010. The Preview Version is fully functional through the end of July 2011. Take advantage of this opportunity to see how Total Visual CodeTools can cleanup the code in your existing projects, let you deliver more robust solutions, and simplify your daily coding chores.
Integrated directly into the Integrated Development Environment (IDE), Total Visual CodeTools gives you a rich set of tools to help you create, cleanup, and deliver better solutions. The latest version is enhanced for MS Office/Access 2010 and has many new features:
Supports Microsoft Access/Office 2010, 32-bit version
VBA Code Parsing Supports Access/Office 2010
Code Cleanup and Code Delivery Allow Immediate Overwrite
Code Cleanup Error Enabler and Handler Tags are Customizable
Copy Control Builder Supports Multiple Target Controls
Long Text/SQL Builder Supports Query Retrieval and Converts Tabs and Spaces
Select Case Builder Supports Text Blocks and Numeric Ranges
Recordset Builder Defaults to Current Database and Simplifies Selections
Three Locations for Total Visual CodeTools on Your Menus
Tools Available During Debugging
Default ‘Send To’ is Remembered
Screens are Resizable
Redesigned Storage of Standards for Improved International Support
The purpose of the SQL UNION query is to combine the results of two or more queries into a single result set. The list contains all the rows belonging to all the queries in the union. This applies to queries in SQL Server or Microsoft Access. A common question is whether to use the UNION or UNION ALL syntax.
The main difference between UNION ALL and UNION is that, UNION only selects distinct values and sorts the results, while UNION ALL selects all values (including duplicates) without sorting. Read our revised paper on UNION versus UNION ALL SQL Syntax to learn more about the differences, see some examples, and understand why you would use one versus the other.
He discusses the dynamics of IT departments in large organizations and their natural conflict with the needs and budgets of information worker. He also shows how organizations that understand the strengths and weaknesses of Microsoft Access can leverage its power for competitive advantage, and how to structure service levels to do so.
Microsoft Access Queries are among the most powerful features of MS Access. We have created a new resource center to make it easy to review all our papers related to Microsoft Access Queries. These original works offer tips and techniques aren’t found anywhere else. They will help you maximize your use of Microsoft Access queries to better analyze and understand your data.
What’s the Maximum Number of Microsoft Access Users?
There is a persistent myth that Microsoft Access Jet databases can only support 20 or so users. Here’s my response to a recent inquiry:
I flatly refute any suggestions that Microsoft Access users are limited to around 30. We’ve run many tests and have never seen that kind of degradation in performance. It is a myth from Access 2.0 days that was eliminated with Access 97 almost two decades ago.
A poorly designed Access database won’t support two users, but a well designed Access solution can support hundreds of users. Of course, what matters is the number of simultaneous users, and what they’re doing.
At Least 200 Simultaneous Microsoft Access Users on an Access/Jet Database
If everyone is just viewing data or entering data into a table, that takes very little work and a large number of people (well over 200) can be supported. People cannot type faster than what Access can handle. If users are all running massive reports and queries with data updates, that can still be done but performance would be an issue which applies to any technology, so testing and optimization would be necessary.
Migrate Back-End Access Database to Microsoft SQL Server for More Users
If the back-end database is in SQL Server rather than an Access/Jet database, the number of users can be practically unlimited if each user has their own front-end copy of the Access application. Performance issues still apply based on what they are doing. In some cases SQL Server is slower than Access, so it is important to understand the situation before thinking SQL Server is the answer.
All that said, any Access application that is distributed to others with shared data should be a split database design. Here are a few resources we’ve written:
Simplify Support for All the Users of Your Access Databases
Total Access Startup helps with the distribution of databases to each user’s desktop, and launching it with the right version of Microsoft Access.
This allows you to centrally support a large number of Access users across your network and ensure everyone is using the latest version of your application. It also simplifies the migration from one version of Access to another.
Total Access Emailer is the most popular Microsoft Access email program. Running as an add-in, it lets you easily send personalized emails using the information in your database. From using fields in the subject and body of the message, to attaching PDF reports filtered for each recipient, you’ll find that Total Access Emailer revolutionizes the way you communicate with your contacts. Any table or query can be the data source for your emails which can be in text or HTML format. We can even automatically include graphics in your HTML emails or let them reference a public site.
We are very pleased to announce Total Access Emailer for Access 2010. Enhancements include support for Access 2010, 32 and 64-bit, compressing all your attached files into one zip file with optional password, international extended character support, a new Code Generator, additional programmatic interfaces, and many more new features. An X.6 upgrade version for Access 2007 and earlier versions also available. Download the Free Trial today!