Use WShell to Retreive the Path to Windows Special Folders

Sometimes in your program you'll need to find the path of the user's MyDocuments folder, or to the Desktop, or to the Favorites folder. Because different versions of Windows will place these folders in different places, it's important to ask Windows for the location of the file, instead of hard coding it or figuring it out from the user name.

This post describes a method for using the Windows Script Host to retrieve these names. The Microsoft documentation is here: SpecialFolders Property.

Here's an example of how to build a VBA function that uses this method within MS Access to find the path to the user's MyDocuments folder.

Read more »

Decompiling an Access Database

Sometimes your database starts to exhibit unexplained behaviour- something that used to work suddenly stops working, or seemingly inexplicable errors appear. Or sometimes the database gets larger than it used to be (even after compact/repair) or starts to load much more slowly.

One of the solutions often recommended is to decompile the database.

Read more »

The world's largest collection of connection strings on the Internet

Ran across this today: The world's largest collection of connection strings on the Internet.

If you want to use ADO you need connection strings. And if you want to access something outside your local Access database you may need to find the right syntax for the particular type of data you want to connect to. Carl Prothman has compiled sample connection strings for everything from Jet to AS/400 to MS Project to Oracle to.... You get the idea.

The Normal Forms in a Nutshell

Good database design is key to success with application development in Access. The principles for database design in Access are the same as they are in any relational database, and one of the core principles is normalization. Within normaliztion there's a concept called "normal forms". Roger Carlson recently posted a series of articles that explain the normal forms. Take a look. It's a great way to get an introduction to this important concept.

Accessing external data using the IN clause

Accessing external data using the IN clause. This article on the Microsoft Access Team Blog shows some of the ways to access data from outside your database by specifying the source of the data in the "In" clause of your query.

Quote:The other use of the IN keyword is as part of the SELECT statement or FROM clause, and is called the IN clause. The IN clause is used to access external data, and can be used in place of linked tables. For example, let's say that you had a SQL Server database that is used as part of an application, but you don't want to maintain a DSN to connect. You could use the IN clause in a query that uses a DSN-less connection to quickly read data from the external table.

It's one of those weird and wonderful things you can do in Access that many developers may never have tried!

Dymanic Forms: Moving Controls Along With The Tab Control They're Part Of

Lately I've built some really neat forms where the controls are moved based on the size of the form or in response to use input. It's not so hard to change the position of a control and it can make for a really well optimized user interface.

One of the particular challenges when moving controls is keeping the controls on a tab control in their proper place. If you move a tab control the controls on the tabs don't move on their own. A.D. Tejpal recently posted this sample database on Rogers Access Library that demonstrates enumerating the controls on each page of the tab control and moving them all so that they all stay together and stay in the right place on the tab control.

Access 2007 Sample Database Demomstrating Cascading Combo Boxes

"I have two combo boxes on my form. When the user makes a choice in one I want the choices in the other on to change. For example the user will pick a country in the first combo box and the second combo box will now show only cities in that country. How do I do that?"

That's commonly called Cascading Combo Boxes and is one of the most commonly asked about techniques. I ran across a new article on this topic the other day. This a blog post from the Microsoft Access team that points out two different ways to get an Access 2007 sample database that demonstrates and explains two simple approaches to cascading combo boxes.

See also: all my Cascading Combo Box posts.

My Treeview Project | Episode 6: Images for the Nodes

This (at long last) is Episode 6 of My Treeview Project.

• In Episode 1 we started with the "Hello World" treeview
• In Episode 2 we built a treeview that showed some simple data from the Northwind Traders sample data
• In Episode 3 we learned how to change the appearance of the treeview
• In Episode 4 we added Expand All and Collapse All buttons that interact with the treeview
• And in Epsiode 5 we finally got the treeview to do something useful- we used it to select records to view and edit.

Here's this episode's treeview:

Download the finished database

In Episode 6 you'll see two things. The main focus of the episode is the use of images for the nodes in the treeview. As well, I'll demonstrate a different approach to the code that reads records from the tables and creates the nodes.

Read more »

Unbound Form With Undo and Redo

Access is great at forms that are bound to tables in data. But sometimes there's value in creating an "unbound form". The data is stored in the controls on the form and your code saves it to the table (or tables) when the user clicks the save button. You might do this when the structure of your data is not ideal for your form- a survey database for example.

This post provides a working example of an unbound form complete with undo and redo buttons.

Access TreeView-ListView Basics from Database Journal

Here's a treeview article I haven't run across before: Access TreeView-ListView Basics from Database Journal walks the reader through creation of a form with a treeview and a listview.

It doesn't have a detailed explanation of how it works, but it does include a downloadable working example, and the zip file for the example includes the elusive help file that covers the treeview and listview controls- very handy! The code uses the "one pass" approach to loading the treeview- a single query is used that brings in all books, and whenever a new bookstore is encountered that node is added.

Connecting an InfoPath form to an Access database

Here's a reference from Microsoft on using InfoPath with Access. I haven't dug into InfoPath yet, but it looks like something that should be part of my toolbox.

Great Tutorials From Crystal on YouTube

Take a look at these video tutorials on YouTube. Crystal has started a great series of video tutorials. Crystal is an imprtant part of the UtterAccess community, she's a Microsoft Access MVP, and she's posted a great "Access Basics" reference here.