Database Tips Blog

This blog tends to be more generic in order to appeal to mainstream computer users. I decided to branch off with a blog on database tips because I realize that many of my readers (at least the ones from the newsgroups, where I'm known to give free technical advice) are looking for database tips, but a database-oriented blog would bore the majority of my other readers. So if you're interested in computer software, the Internet, and security in general, as well as the free things I find on the Internet, please continue to read this blog, Data Devil Dog. If you're interested in databases, specifically Microsoft Access and the things one can do with Access when connected to other popular database engines, such as Oracle, SQL Server and MySQL, please read my other blog, Database Tips.

Book List For Access 2007

The book list for Access 2007 is out for the books already published and those that will be published in the near future. Whether you need a comprehensive reference, such as "Microsoft Office Access 2007 Inside Out," by Microsoft Access MVP's (Most Valuable Professionals) Jeff Conrad (AKA "The Access Junkie") and John Viescas, or you're a beginner and need "Access 2007 for Starters: The Missing Manual," or you're an experienced developer and you need the "Access 2007 VBA Programmer's Reference" to give you the edge, you'll find a wide variety of choices in the list of 13 books on Microsoft Office Access 2007 listed on Microsoft's Access Blog.

Simulate Access 2007 Runtime Mode

An old trick to prevent users from making changes to a database's design is to use the /runtime switch when opening the database file. Access 2007 has a new trick to do the same thing. Change the ACCDB extension to ACCDR. The user will be able to open the database in runtime mode -- without having to specify the /runtime switch or have the Access Runtime version installed. The UI Ribbon and navigation pane will be missing when the database file opens, so ensure that you make alternate arrangements in your database application design with custom UI Ribbons if necessary.

Get It Done with Access

Need some tutorials to figure out how to use Access for your database tasks? Microsoft has some online tutorials showing how to get it done with Access. So far the online tutorials are:

• Basing one combo box on another
• Shading alternate rows in a report
• Multiple column reports
• Eliminating unwanted blank lines in labels and reports
• Counting in reports
• Summing in reports
• Using parameters with queries and reports
• On time and how much has elapsed

If you don't see a tutorial you're interested in, send your question to msaccess@microsoft.com, and Microsoft will answer as many questions as possible in future tutorials.

Customize The Office 2007 UI Ribbon, Part 3

Although it's often mentioned that the XML code needed to customize the Microsoft Office 2007 UI Ribbon can easily be written in any text editor, such as NotePad, one should use better software tools for programming. Microsoft Office Access MVP Gunter Avenius's Microsoft Office Access 2007 UI Ribbon tutorial includes links to the free tools needed to easily and quickly develop custom UI Ribbons.

His tutorial is well organized, and includes plenty of good examples which are available for download. It's so well organized that one may use it as a quick (and useful) reference. The examples include code and often show accompanying graphics to illustrate each concept. Includes troubleshooting tips in the FAQ section.

Customize The Office 2007 UI Ribbon, Part 2

Need to create a custom UI Ribbon for the new Microsoft Office Access 2007? Viki Selca and Clint Covington of Microsoft wrote a tutorial, "Ribbon Extensibility in Access 2007," that shows how to create a custom UI Ribbon using only Ribbon extensibility markup XML and macros.

I know what you're thinking. "Macros are for weenies." But it turns out that the VBA code won't run due to the new security model unless the user has certain settings set. However, macros can be deemed "safe," so that an application can have a guaranteed minimum functionality, even if the VBA code can't run. Microsoft put extra effort into the macros for Access 2007, so that developers can write as little code as possible (or no code) and still have a reasonably functional database application.

Access Developer Extensions 2007 Are Free

Microsoft Access developers have been complaining forever that the Access Runtime should be free to distribute. Microsoft has finally heard the pleas! When the Access Developer Extensions 2007 are released (scheduled for March 2007), they'll be free to download and redistribute.

That's a cost savings of US$549 (upgrade version) or US$799 (full version) for Microsoft Visual Studio 2005 Tools for the Microsoft Office System (VSTO). Most editions of VSTO included the Access Developer Extentions (ADE), which includes the unlimited Access Runtime redistribution license.

In case you don't know what the Access Runtime is, the Runtime is a slightly cut-down version of Access installed on computers that don't have the retail version of Access, so that Access developers can distribute their database applications without the customer having to purchase a retail license for Access for every computer that the database application runs on. What is "slightly cut-down"? It's missing the design capabilities of the retail product, built-in menus, and graphics filters, but is otherwise fully functional for tables, queries, forms, reports, modules, and macros.

Connect To Access 2007 From Pre-2007 DB

Need to connect to an Access 2007 format database, but don't have Access 2007 installed yet? Or do you need to connect to any other Office 2007 formatted file or to Sharepoint Services and don't have them installed? Download the Data Connectivity Components of the 2007 Office System Driver, which includes the ODBC and OLEDB drivers for the ACE (accdb) and Jet (all versions of mdb) database engines, a driver for Microsoft Office Excel 2007 (xls, xlsx, and xlsb), and a driver for Sharepoint Services.

To connect to an Access 2007 database file without security using OLEDB, use this connection string (all one line):

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyDir\MyDB.accdb;Persist Security Info=False;"

To connect to an Access 2007 database file with a database password using OLEDB, use this connection string (all one line):

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyDir\MyDB.accdb;Jet OLEDB:Database Password=MyPswd;"

To connect to an Access 2007 database file without security using ODBC, use this connection string (all one line):

"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\MyDir\MyDB.accdb"

To connect to an Excel 2007 file using ODBC, use this connection string (all one line):

"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\MyDir\MyFile.xls"

Microsoft Office Access 2007 Inside Out

"Microsoft Office Access 2007 Inside Out" is scheduled to be released 9 May '07. Microsoft Access MVP's (Most Valuable Professionals) Jeff Conrad (AKA the Access Junkie) and John Viescas are the authors, and if history is any guide, it's going to be an outstanding book. You can pre-order a copy at Amazon.com now.

Microsoft Access Development Team Blog

Get the scoop on Microsoft Office Access 2007 from the Group Program Manager for Microsoft Access, Erik Rucker.

Should You Upgrade to Access 2007?

Wondering if you should upgrade your databases to Access 2007 now that Microsoft has released it? Read Microsoft Office Access MVP Allen Browne's comprehensive article on what's hot and what's not in the new Access 2007. Check out Allen's other great tips for Microsoft Office Access, too.