We received a question from a reader named Eric week before last about how to write a Count(Distinct) query in Access. In other database applications such as SQL Server, Oracle, and MySQL, the Count(Distinct) aggregate is used to count the number of unique items in a given rowset. For example, consider a table on SQL Server named tblColors, such as: Color Value Red 5 Green 2 Blue 8 Orange 1 Red 8 Green 6 Blue 2 To get a count of the number of unique colors in the table, you could write a query such...

This is a quick post to lay out the limits in Access 2007 and make a little clearer how the application works and how it scales. The first table below shows the limits of the database engine itself. Access Database Limits Access database (*.mdb or *.accdb) file size 2 gigabytes, including all objects in the database (data, forms, reports, indices, macros, modules, etc.) Total number of concurrent users 255. Note that the practical limit will likely be lower than this based on database design. Table...

In the last post, I gave a quick overview of SharePoint, SharePoint lists, and how Access works on SharePoint. In this version, I'll show what happens when you move an Access database to SharePoint. Publish to SharePoint Access has had a feature to "Upsize to SQL Server" for quite some time, and the "Move to SharePoint" feature in 2007 is very similar. It creates SharePoint lists for all the data, moves the data to those lists, and replaces the local tables in the database with remote tables linked...

This post provides an overview of features and components that will be deprecated in the upcoming release of Access 2010. A decision to remove a feature or component from the product is never a trivial one; we therefore have put a lot of thought when deciding to remove functionality. We believe it will benefit the majority of our customers by helping us to make forward progress. Microsoft recommends that when you develop new Access applications, upgrade or modify existing applications, avoid using...

Download Access 2010 Runtime, Access Database Engine Redistributable (office connectivity components) 2010 and Source Code Control Add-in for Access 2010 today! Access Runtime 2010 Available in both 32-bit and 64-bit, you can download the Runtime here – http://www.microsoft.com/downloads/details.aspx?FamilyID=57a350cd-5250-4df6-bfd1-6ced700a6715&displaylang=en . It is currently offered in 13 languages and more languages will be offered at a later time. Read this post on features in Access...

Today’s guest writer is Wouter Steenbergen, a PM on the Access team responsible for Access form and report designers. If you want to build an easy to use application it always starts with a good navigation model that allows users to quickly perform the most common tasks and easily find the less common tasks. Years ago, Access introduced the concept of a switchboard manager which has become a mainstay for many applications in the late 90’s. The hierarchical model and ease of adding new...

Way back in October, I posted an overview of Access. We've finally got a complete customer-ready overview for the product that will be going up on the Access site at http://www.microsoft.com/access . If you'd like a printable version of this paper, it is available here . This takes longer than you might imagine, so I thought I'd post it here as well. Even though I understand many of you are looking for more technical details, this blog is fairly widely read and I know there are many who would like...

We’re excited to announce that a longstanding Access tradition continues. A new version of the Northwind sample database has just been released. The best way to get to the template is to start up Access 2010, under Office.com Templates , click on Sample Templates , and then click on Northwind Web Database. You can also get the template here: Northwind Web Database . As before, Northwind shows you what an Inventory/Orders system might look like for a mail order dry goods company. For this version...

Today’s guest contributors are Kerry Westphal, Chris Downs, Krunal Sheth, James Rivera, and Michael Tucker—from the macro designer feature crew. The Macro Designer is a revamped editor that allows you to automate repetitive tasks; wire together forms and reports to create productive UI; and implement business logic in Access databases. We will talk more about business logic next week when we start the conversation about data macros. Probably the best way to introduce this feature is a...

Last week we outlined improvements to the Access 2010 macro designer , parameter support , and IntelliSense . These improvements build upon Access 2007 macro improvements of macro sandbox, embedded macros, TempVars, debugging, and support for error handling. The natural next step in macros is to provide a model for business rules. Data macros allow developers to attach logic to record/table events (similar to SQL triggers). This means you write logic in one place and all forms and code that updates...