Programming The VBA Editor

Programming The VBA Editor describes methods for programmatically interacting with the VBE editor. Quote:"You can use these features to write custom procedures that create, change, or delete VBA modules and code procedures." The article is written for Excel but I think it should be adaptable to Access. (I haven't tried it yet- if you try it let me know how it works!)

...read more!

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.

Public Function GetDocumentsFolder() As String
Dim objWShell As Object

Set objWShell = CreateObject("WScript.Shell")

GetDocumentsFolder = objWShell.SpecialFolders("MyDocuments")
End Function

You can use the following constants instead of MyDocuments to get the path to other special folders:

AllUsersDesktop 
AllUsersStartMenu 
AllUsersPrograms 
AllUsersStartup 
Desktop 
Favorites 
Fonts 
MyDocuments 
NetHood 
PrintHood 
Programs 
Recent 
SendTo 
StartMenu 
Startup 
Templates 

Notably absent from the list above is the user's Temp folder. See the additional reading before-I've posted a method for getting that path previously here. There's also an post linking to an API method to get special folders here.

...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...

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. Decompiling is done using a command line switch, and it seems to be an undocumented option. The links below provide instructions and additional information on decompiling. Note that some of the articles talk about decompiling actually introducing problems sometimes, so it seems like it's best to only decompile when you have reason to suspect a problem and to make a copy of the database before decompiling.

Here are the links:

• How Do I Decompile a Database? This new post from Rogers Access Library explains how to decompile and why you might need to do this, including instructions for creating a shortcut for decompiling.
• Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times from Tony Toews contains more details including some other database repair methods.
• Solving Access IPFs with /Decompile from The Access Web provides very concise instructions and a link to a post that attempts to explain how decompile works.

...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.

...read more!

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.

...read more!

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!

...read more!

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.

...read more!