API: Copy variables/control contents to memory

I used to have this bookmarked but somewhere along the line I lost it: API: Copy variables/control contents to memory from The Access Web. I stumbled across it today when reading this post on UtterAccess.com.

I have found copying things to and from the clipboard to be a useful feature in several of my applications. Here's one I built in Word. This little macro copies the current document path to the clipboard.

Public Sub PathToClipboard()
    With Application.ActiveDocument
        ClipBoard_SetText .Path & "\" & .Name
    End With
End Sub

(You'll need the code from The Access Web link above in addition to this little snippet.) I found I often had to give a colleague the path to a document I had just updated on a shared drive somewhere. Using this macro I would just hit the keyboard shortcut I had set up, pop over to email, and hit ctrl-v to paste. And there I'd have the full path to the document in my email.

There are lots of other applications for the putting data on the clipboard, so it's a good thing to have in your toolkit- bookmark the post on The Access Web and you'll have it when you need it.

Macros and Access 2007: What happened to all the extra Macro Actions in Access 2007?

"Access Junkie" has a quick tip here about macros in Access 2007: What happened to all the extra Macro Actions in Access 2007? When I drop down the list of macro actions in the macro design window, I do not see all the options present in previous versions. Where are they?

Access 2007's security enhancements include a distinction between trusted mode and non-trusted mode. It seems some Macro actions are allowed when your database is running in trusted mode, while some aren't. By default Access only shows you the trusted actions. The article shows you how to see the rest.

There's an overview of what else changes depending on whether your database is trusted or not here: How database objects behave when trusted and untrusted and an overview of the new security provisions in Access 2007 here: Get started with Access 2007 security

Until Access 2007 most serious Access developers believed that macros were rarely the right way to go- that VBA was a better choice. There's a article here from UtterAcess.com that reviews the differences: Macros vs VBA - Why use Macros? With the introduction of trusted and non-trusted mode and some additional capabilities in macros in Access 2007, it seems there may be a case for macros after all- I'll have to explore the new features and see if they provide enough functionality to create an application. There's a review of the additional macro features here: New macro features in Office Access 2007.

Case Study: Improving Visibility & Control for Mission Critical Spreadsheets in Energy

In their company blog the folks from Prodiance have posted a case study that discusses spreadsheet and EUC risk management in a major US enegry company: Case Study: Improving Visibility & Control for Mission Critical Spreadsheets in Energy.

While they're coming at it from the perspective of selling their specific software solution, the issue facing many businesses is very real. Whether compliance is an issue for you or not, you need to understand and manage the risk to your company from EUC that is not being managed. Any time end-users are creating tools that sustain you business, you need to know that it's being managed properly- this applies to everything from spreadsheets with formulas to simple macros to complex applications that have been built by savvy end-users. Managed well these tools are a key part of your organization's toolset. Managed well and leveraged to their fullest they can become a real cometitive advantage- manking you more efficient and more nimble. Managed poorly or not well they can be a real risk.

The real key is to understand how your organization is leveraging business technologies. If you were to wander around you organization to do a quick scan, here are three questions you could ask:

• Where did that neat little macro come from? Does someone who's still here know how it works? What would we do if it stopped working?
• Did someone from finance take a look at the complex formulas in that spreadsheet? How do we know those projections include everything we agreed we would include in the model?
• Do the expert users who built that handly little departmental application know the best ways to manage and deploy that kind of technology? Where's the master copy kept? Who's okaying changes to make sure the users don't see down time?

Check back here from time-to-time as I follow this post up with a mini-series on managing business technology.

My Treeview Project Episode 7 | Another Way To Read Through Your Data

In Episode 7 of My Treeview Project we're going to look at another approach to reading through our data and adding nodes to the treeview.

Read more »

Running a Command as Administrator in VBA

As Windows and Office become more and more security aware we've had to learn about things like trusted folders and macro security. Here's another one- when you shell out to run a command, that command may need to be run as an admin. Running a Command as Administrator in VBA from the Microsoft Access Team Blog tells how to make this work.

Link Textbox Contents to Worksheet Data

Link Textbox Contents to Worksheet Data on Glenn Lloyd's "OfficeTipsAndMethods" blog is a nice discussion of some really good techniques for working with charts in Excel.

There's a big difference between doing a chart and doing a chart well. For instance I've seen charts done lots of times that couldn't easily be updated to the next months data. When discussing one of his techniques, Glenn says "Now you ask, why would I want to take the time to set this up when I could simply edit the title every month? For me it’s a no brainer. Yes, it took some time to set up – probably less than five minutes, to be honest with you. What is the payback on that investment of my time. Sure it is small but let’s say it takes me 1-1/2 to 2 minutes each month to edit the title. In the first three months I have more than recovered my time investment.Then there is the intangible benefit of saving me the embarrassment of forgetting to change the title or occasionally misspelling something in the title." Hear, hear, Glenn!

Gantt Chart using the Microsoft Chart Control

"How can I show a Gantt chart on my forms (or reports) using data in my database?"

Here's one solution that uses the built-in chart control. Gantt Chart using the Microsoft Chart Control.

If I wanted a sophisticated Gantt chart I'd use MS Project (hopefully I'd already be using MS Project if I was working with tasks and timelines.) But for a simple Gantt the built in chart control works great!

The example database shows the Gantt in a report but would work find in a form too, I think.

Search Criteria Form Example From Allen Browne

Many Access developers set out to create a search form: a form where the user can enter or pick criteria and matching records will be shown. Often they find themselves trolling the Internet for a good example to get them going.

You'd be hard pressed to find a better starting point than this article by Allen Browne: Search criteria. The example works you through creating a form with criteria entry boxes at the top and matching records at the bottom. Once you understand the methodology, you can easily extend it to forms with different layouts, pop-up forms, reports, etc.

Subqueries Primer: Find Employees With Salary Higher Than Their Department Average

This is a classic SQL problem- probably more a textbook exercise than a real-life business problem, but a classic nonetheless. "Find all employees whose salary is higher than the average salary for their department."

In Access, the most straightforward approach is a multi-query solution. However it can be also be accomplished in a single query using subqueries. I'll discuss both approahces here

Read more »

Functions for Calculating and for Displaying Date/Time Values in Access

Functions for Calculating and for Displaying Date/Time Values in Access from Microsoft covers a whole bunch of data and time topics.

It has functions for everything from the last day of the current quarter to the first day of the current week. It also has some functions for working with intervals, although you'll see they differ slightly from my advice on the right way to store intervals.

Read more »

A Utility to Help With Ensuring Uniform Control Width on Forms and Reports

Tony Toews has posted a neat little utility that grabs the control widths and a few other details for all the controls on your forms to help you make sure that you've sized things consistently throughtout your application. This will be especially useful when you expand a field and have to change all the controls it's used in.

See Ensuring uniform control width on forms and reports

A Ribbon Class - Another approach to using the new ribbon in your applications

Much has been written about the new "Office Fluent User Interface" that has been introduced with Office 2007. Also known as the ribbon, it's a whole new way to work with Access, Word, Excel and Powerpoint, replacing the menus and command bars we've all become accustomed to.

Like them or not as a user, Access developers have to deal with the reality that the custom command bars they have been using in their applications the past don't work the way they used to.

In this article Albert Kallal presents a neat approach to solving the problem of having your application interact with the ribbon. He's created a ribbon class which uses methods similar to the old command bar methods to operate on icons within the ribbon. I haven't tried using it in an application yet, but I ran the demo and it looks pretty neat.

See also: my other ribbon posts.

What is the Fastest Way to Return the Record Count of a Table?

Roger Carlson has posted an interesting analysis of the various ways to get the record count of a table. Take a look here.

Treeview Example- Filling Recursively, Populating With Names of Folders

Here's another working Treeview example from the code archive at UtterAccess.

Quote: This little database illustrates the implementation of Microsoft Knowledge Base Article Q20981 (How to Fill a Treeview Control Recursively). In addition, frmFolderExamples illustrates one way to fill the Treeview Control with the names of the folders located on a drive. It also shows how to return some of the properties of the treeview nodes as well as how to clear a treeview. I use some code harvested from Candace Tripp’s Browse and Open Files Database as well as ideas from Luiz Cláudio’s Treeview Control for Database Objects.

Roll-Your-Own “Traffic Light” Icon Set

Quote: Here’s a great tip suggested by Clint Covington. Using an expression and a Rich Text text box, you can create a traffic light indicator in an Access form or report, as in the following illustrations:

I really like this use of a Rich Text text box. We used it in my shop successfully- I think it was the first Access 2007 specific feature we used. (Rich Text text boxes became part of Access in the 2007 version, I think.) Take a look at http://blogs.msdn.com/access/archive/2009/04/16/roll-your-own-traffic-light-icon-set.aspx

This solution gives more options for colouring controls on your forms and reports than conditional formatting does.

Using Right-Click With Treeview Nodes

Lets say you have a treeview and you want a user to be able to right-click a node and then be able to either delete it, copy it, or view details about it. How do you trap the right-click event? How do you show a context menu? And how do you know what node was clicked?

There's no OnRightClick event for Treeviews- that would have been the easiest solution. There are MouseUp and MouseDown events that know what mouse button was pushed- maybe that would help.

It turns out the secret is MouseUp.

Read more »

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!)

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.