Microsoft Access/Jet databases require routine maintenance to ensure reliability, integrity, and efficiency. You can manually compact and repair a database. However, you can save yourself some time and effort by configuring your database to automatically do this every time it closes. Read about how this works and the issues and limitations around this Microsoft Access feature:

Automatically Compact and Repair Microsoft Access Databases with Compact On Close

We have a long history of leveraging existing data to improve decision making and communications.

Many of you are already familiar with how our Total Access Emailer program automates the sending of personalized emails with recipient specific data and reports. This amazing product lets you leverage the power of your data to significantly improve how you communicate with your contacts.

With our experience creating Total Access Emailer, our Professional Solutions Group has helped many organizations incorporate the product and implement advanced, custom features.

We've also taken this technology to the next level by creating web sites to host files rather than emailing them to your contacts. If you're interested in learning more about how we can extend your Windows desktop files to the web, please let us know. We can make it easy for your contacts to privately log in and see their files.

Can we help you with a project? Contact us for a Risk Free Assessment

In our Microsoft Access, VBA, and VB6 programs, we often need to pauseprocessing for a certain period of time, or until a specific time, and thencontinue processing. One common way to do this is to use the DoEvents function in a loop whilewaiting, however, this method consumes the CPU time and slows down the PCconsiderably. This is a significant problem when pausing for more than a coupleof seconds, when users will notice the slowdown. Use the Sleep command instead.

Learn more about our royalty-free MicrosoftAccess/VBA/VB6 module code.

Microsoft Access, VBA, and VB6 include a wide range of built-in Date functions, including DateAdd, which calculates the difference between two dates. A common need, however, is to add a number of weekdays to a date, without counting weekend dates.

Learn about this and all our royalty-free module code in Total Visual SourceBook where you can also perform business day math with a list of holidays to avoid.

Total Access Analyzer examines all your database objects to provide extensive documentation, cross-reference, and application and flow diagrams, with over 350 presentation-quality reports. It detects 280+ types of errors, suggestions, and performance tips, so you can learn and apply Best Practices to improve and speed up your Microsoft Access applications.

As part of our commitment to quality, we are pleased to announce that we have released a new update for Total Access Analyzer 2002, version 10.8. This update resolves all known issues since their original release.

The updates are available free of charge to registered owners who were notified via email. If you did not receive instructions, please contact us so we can update your information. 

In Microsoft Access tables, the AutoNumber field type allows you to automatically assign a unique sequential number to each row in a table. AutoNumber fields are often used as the Primary Key, since they are unique and permanent.

By default, AutoNumber begins with 1. However, you may want to start with another number. Here's an easy way to set the AutoNumber at a number higher than 1.

 

(NOTE: This tip was revised on March 26, 2010 to fix a typo in the SQL syntax.)

The release candidate of Office 2010 is now available, and Office 2010 is expected to be shipping by mid-year. Hopefully you take the chance to download it, or at least review some of the many videos and articles about the new features.

For Access enthusiasts, the new version makes Access even more accessible to non-developers by exposing more functionality without requiring code or queries. For example, Reports are much richer, with added calculation tools and the ability to combine data from multiple sources. Microsoft has also created a whole new set of Templates to get users started more quickly, and what can be characterized as data triggers for Jet databases.

Most interesting is the ability to create solutions that can be exposed to the web. There are lots of restrictions and it's not the same as putting an existing Access application on the web, but by leveraging SharePoint and cloud computing, it's a whole new option for Access users and developers.

Part of Office 2010 is the Office Web Applications (OWA), a web version of Excel, PowerPoint, Word programs that allows you to create, edit and host Office files on the web without having Office installed locally. Microsoft is actually providing 25GB of space for you for free on their new SkyDrive site (with you Microsoft Live account).

In our continuing coverage of query tips and techniques, we've create a paper focused on the DELETE query in Microsoft Access. It covers the basics of DELETE query SQL Syntax. It's a very powerful feature and a fundamental part of relational databases since you can remove multiple records at one time, and can specify which records to delete by linking to data in another table.

The paper also covers these more advanced topics:

• Query fails: "Could not delete from the specified tables"
• Use DISTINCTROW with Delete Queries for Non-Key Field Joins

Understanding and using Delete Queries improves the performance of your applications and makes them easier to maintain versus doing the same changes manually or in code.

Many organizations use Microsoft Access to manage a list of email contacts. In conjunction with Total Access Emailer from FMS, it’s easy to send everyone a personalized email (such as a newsletter, order notification, or even a recipient’s individual invoice or sales receipt) in a convenient and automated way. Handling undeliverable emails and unsubscribe requests, however, can be tricky.

Read our new tip to find out how...

Total Visual SourceBook is the most popular professional source code library for the Microsoft Access/VBA/VB6 community. This huge library of powerful, well tested, and documented VBA/VB6 source code is available for your projects royalty free! Eliminate the drudgery of writing code that already exists. Simply insert our code into your project and run it.

As part of our commitment to quality, we are pleased to announce that we have released a new update for Total Visual SourceBook 2007. This update resolves all known issues since their original release.

The updates are available free of charge to registered owners who were notified via email. If you did not receive instructions, please contact us so we can update your information. 

Total Access Detective is our popular Microsoft Access database, object, and data comparison product. Compare any two databases or two Access objects in one database to pinpoint exactly how they are different. As part of our commitment to quality, we are pleased to announce that we have released new updates for:

• Total Access Detective 2007 (version 12.0)
• Total Access Detective 2003 (version 11.7)
• Total Access Detective 2002 (version 10.7)
• Total Access Detective 2000 (version 9.7)

These updates include performance enhancements, Windows 7 support, improved ADP support, and resolve all known issues since their original release. For more details, visit the Total Access Detective Updates page.

The updates are available free of charge to registered owners who were notified via email. If you did not receive instructions, please contact us so we can update your information.

The Microsoft Access runtime version allows you to distribute your Access applications to users who do not have a full license of Access. The runtime is similar to the full version of Access, but several of the features that exist in the full version are not available in the runtime. To test your users' experience, you can simulate the runtime with a command line. 

To learn more, read our tip: Simulating the Access Runtime during Microsoft Access Development

 

In Access 2007-Format databases (ACCDBs), the AppendOnly property for Memo fields allows you to store a history of the changes made to the field. The history of the Memo field can later be retrieved using the ColumnHistory method.

In Access 2003 or earlier, you may have seen truncated numbers on forms or reports, and not realized that you weren’t seeing the full value. Access 2007 includes a new option tocheck for truncated number fields and display pound signs (#) to indicate that the entire value is not shown.

While attempting to import an Access 2007 format database (*.ACCDB), you may receive an error such as: Unrecognized Database Format or Could not find installable ISAM. These errors can occur if you do not have the appropriate driver installed to import data from ACCDB files.

Importing Microsoft Access MDB databases into SQL Server is a built in feature of SQL Server. However, because of the difference between the database engine of Microsoft Access 2007 and earlier versions of Microsoft Access, it is not possible to connect to the Access 2007 database using the built-in data source “Microsoft Access”. You can use this if you wish toimport data from a MDB format, but not an ACCDB from Access 2007.