Monday, December 17, 2007

Converting to Access 2003

Converting or upsizing your older Access database to Access 2003, Microsoft helps you out with this tool

Tuesday, December 11, 2007


If you have code that refers to


you will need to fill in the Application Title in the menu Tools ~ Startup

Thursday, December 06, 2007

Jeff Conrad talks Access 2007

Jeff Conrad put together the following resources for Access 2007 junkies

Hi everyone,I'd like to let you all know of some new content I've added to my Access 2007 site.

This page lists any new Help articles concerning Access 2007 posted on Office Online along with the date of release. It also lists any recently revised articles as well. Currently I have ~40 articles listed. I'll continue to update this as new articles get posted.

This page is my new Access 2007 FAQ . I'm really proud of this one. It includes links to pages discussing the most frequently asked questions I'm seeing in the newsgroups and online forums. I've been wanting to put something like this together for a very long time, but coming to work at Microsoft put a little "dent" in my free time. :-) I've been slowly chipping away with adding content for many months.I will be adding a lot more pages and content to the FAQ as time progresses, but I feel it's finally at a point in time where there is enough content to make it live. Right now there are 50 questions/pages devoted to common questions/issues. Most of the pages include screenshots so people can easily see what I'm talking about.

I think people in this group will find these two pages of most interest:

In the regular newsgroups, these two seem to be the most asked questions:

I hope they help you when you're assisting people with 2007 questions. Feel free to point people to them; the pages are now live!--

Jeff Conrad - Access Junkie

Wednesday, December 05, 2007

Renaming a query

Recently I had to build a monster stack of union queries that were all based on a single query (query1). In this case I had to have two different versions of query1 for different purposes. So I needed to rename a query. To do these two renames is simple in vba

currentdb.QueryDefs("query1").Name = "queryY"
currentdb.QueryDefs("queryX").Name = "query1"

Thursday, November 29, 2007

Niche or Mainstream Access ~ Is this the magic pill

“A Magic Pill for MSAccess”

After reading Gary's last article on MSAccess and IT departments I was compelled to write this article. Why do IT departments hate MSAccess, here are a few reasons from my experience :

1 Slows even with a few concurrent users
2 Chews up network bandwidth
3 Does not work well over a wireless network
4 The slowest computer decreases performance of all users
5 Corrupted databases are mainly caused by network failure.

The magic pill, remote desktop. Using solutions such as Terminal Services and Winconnect will give life to your MSAccess application that was simply not achievable before. How do I know, this is our preferred recommendation for all companies that purchase our product. We sell a mission critical application for the fashion industry, it runs their entire business and is written entirely in MSAccess. Our target market is 1 to 30 users , we have been criticized for not moving it to SQL and .Net but with a RAD development environment and a feature set other applications would die for, MSAccess is the right product for us.

How does it work: Using a product such as Terminal Services allows users to connect to a single box which holds and runs the MSAccess application. Users use a RDP connection to connect to the box, login to a windows desktop and use the application like any other application. In fact the box is a windows box with all your normal software applications installed (licensing applies).

Performance increases and corruption decreases simply because you have now removed the network component from the equation. Even if the connection is lost, you have not terminated your session. When you reconnect, you connect back to the same session.

Users can also connect to the box via the internet, all of sudden you can work remotely from your office. Support staff love it and your application becomes more manageable. Support and training can be done remotely, similar to products such as Nortons PC-Anywhere, where RDP work much faster. For internally used applications there is no need to ever write a browser based application.

Setting up Terminal Services and Winconnect is relatively straight forward. Most IT professionals have used it, the trick is in how to setup your MSAccess application. We have a front and back end application. When a user logs in (unique session username and password) they get a mapped local drive L: which maps to their own folder where a copy of the front end application is held. Every user has their own copy and this eliminates problems you might have with front end application settings.

Terminal Services runs on a Window Server where Winconnect runs on any Windows Operating system. The main difference is cost and security, we tend to install Terminal Services with companies over 5 users. However Winconnect will work fine up to 25 users also the cost is cheaper. Try it for USD $300 for 3 licenses it is worth experimenting on, you will be pleasantly surprised.

Peter Pakarinen heads development for Niche Fashion Technology and has been working with MSAccess since MSAccess 95. NFT uses TS extensively in its own company and that of it’s clients.

Peter Pakarinen
Niche Fashion Technology
Software for the fashion industry : POS, Retail, Management.

Wednesday, November 28, 2007

Save Change SQL Server Table Scripts

FMS have a handy hint on how to save SQL Server change table scripts

Unnecessary Indexes

One of the most common table traps in Access is the unnecessary index trap. I have mentioned this on my website and in a couple of other magazines a number of times.

FMS have also come up with a new page on the topic

So open your database backend and start looking for those dups and then turn off the automatic feature in your Options.

Tuesday, November 20, 2007

Office 2003 Service Pack 3

A whole bunch of fairly important issues caused by Office 2003 SP3:

The AutoCorrect feature does not work in Outlook 2003 after you install Office 2003 SP3

The Fast Saves feature in Word 2003 no longer functions after you install Office 2003 Service Pack 3

You receive an error message when you try to open a file or to save a file after you install Office 2003 Service Pack 3

You are unexpectedly offered Office 2003 Service Pack 3 when you use Microsoft Update, Office Update, or Automatic Updates

Error message when you try to install an Access 2003 add-in for all users by using the Add-In Manager on a Windows Vista-based computer: "There was an error adding to the registry"

You cannot open Microsoft Excel 5.0/95 Workbook files that contain Visual Basic for Applications macros in Excel 2003 after you install Office 2003 Service Pack 3

You may experience issues when you run the Microsoft Office Document Imaging program after you install Office 2003 Service Pack 3

Monday, November 19, 2007

Access 2007 Ribbons

Anothony Moore is putting together a collection of articles on the new Ribbon in Access 2007. Have a read...

Sunday, November 04, 2007

Intellectual Property

An article on intellectual property in the big world

Home -

Tim Getsch, after 8 years working for the Microsoft Access team has left to run his Sports Card Swap business. Lets hope his new business continues to grow.

Wednesday, October 31, 2007

Facebook ~ A Discussion On Development

A detailed roundup on whether facebook is important from a developers point of view. The fact that Microsoft has bought in indicates that there will be programming options for Microsoft centric developers.

Tuesday, October 30, 2007

Access and IT Support - How Much Control and How To Improve It

In the editorial of my latest newsletter, I posed a ? to the readers (and got a lot of responses)

Whilst I have been discussing Access databases with managers and IT staff this year, I have often heard the phrase "We don't support Access databases". I then ask the question, do you support Excel and the reply is "yes but we don't support macros (VBA)".So here are three questions to YOU, the readers.

Have you noticed this behavior?
What parts of Access should IT staff support as a minimum ?
What are three things that IT help teams should know about Access so that they can provide at least some support?

Here are responses thus far, I will edit them further later

Peter "The simple problem with MSAccess is that anybody can create a database and call themselves a software developer. There are too many poorly designed databases which are causing companies a lot of grief. I would recommend certification for MSAccess on it owns. The simplest suggestion is recognition as a MSAccess Professional. This way MSAccess could be recognised as a serious database and development platform.

"Have you noticed this behavior? Yes, many times. Practically every firm (both government and private) don't wish to go near Access. On rare occasions we have run into clients/students who say that someone in their Help Desk support area has been able to help them, at least partially. In those instances the Help person has already created a database for themselves and have been able "to relate".

What parts of Access should IT staff support as a minimum ?
Personally I think IT support should be capable of showing/helping users in creating effective tables since good tables properly normalized are a major basis for a 'good' database. Also query basics would be helpful.

What are three things that IT help teams should know about Access so that they can provide at least some support?
1. Good table design including primary key use and normalization through at least 2nd normal form.
2. Query design and criteria use especially with the combination of wildcard use, particularly the asterisk (*).
3. Report wizard understanding, then basic understanding of Report sections, as well as elements such as 'changing a label in place', adding a logo. I don't know how many times we have discovered Help Desk personnel telling end users learn Crystal Reports to do your Access reporting. Incorporating another software to avoid doing reports in Access is ridiculous. The Access report generator is so cool and we've never encountered any thing it couldn't be taught to accomplish.

David "We use Access to a great extent in our organization. IT is not very supportive. We generally know who the Access gurus are within the organization (I'm one) and have a informal network of problem solving. My department has posted tutorials for working w/ Access using the data that we provide via SQL Server databases. There are also plenty of books and classes one can take.

The only Access support we expect from IT is installation support.

Access is too complex an application to expect support from non Access users and IT folks don't use Access or use the the data we use. They maintain systems and networks, plan for future needs, maintain user accounts and security. If an organization wants support on any more then the basics, in terms of software support, they need to provide educators to handle this task, not expect IT folks to do this.

Christopher "Our agency has taken an extreme measure to stop the proliferation of user developed MS Access databases by removing the Access application from the standard software image on computer workstations. The current software image installs the run time version of Access and not the full version. The rationale offered for this action are: (1) Access databases on the network consume too much of the data pipeline, (2) IT does not have the resources available to support user developed databases, and (3) Not installing the full version of Access saves the department money since most users do not use it.

If the IT department was able to support the needs of the users in a timely manner, the users would not need to create their own databases to efficiently manage the agency’s business. Instead, they have disempowered the workers by taking away a powerful tool and replacing it with a process to establish IT programming priorities which requires completion of form that is sent to an IT Governance Committee. This committee meets monthly to review requests and allocation IT development resources to projects they deem worthy.

Unfortunately, the IT dinosaur cannot react in real time to the changing business needs of the users in a timely manner, so they took away the one tool which allowed the user to get the work done themselves. I have a request in with our IT Governance Committee since last May and I still have not received a thumb up or a thumb down response. At least with Access, I could have finished the task and be doing something else useful.

Arvin "I've come to the conclusion that there are 2 types of IT folks.

1. Normal people who do their jobs, and learn what they need to support their networks. Type 1 individuals are basically people who are helpful and supportive. They recognize that their role is to enhance the business process, not supplant it. They do no feel threatened by challenges.
2. And then there are the network guys who need to control everything. They will support new apps only if they chose them and they only choose apps which make their job easier or give them even more control.

From a business prospective, the main difference between the 2 is the health of the business. Type 1 admins are looking for every opportunity to enhance the business, while type 2 individuals think they are the business.

Apparently you've run into type 2. I've been fortunate enough to only work with type 1's this year, although I've had the great displeasure of working with type 2's in the past. I now refuse the work if I sense control freaks. It just isn't worth the aggravation.--

Mark "Quite frankly, I'd rather NOT have the IT staff try to support mydatabases! I will set-up my clients with a backend on a server and each user having their own copy of the frontend. Later, I will walkinto a client's office and find that someone has messed with this. For example, the shortcut I've established on the user's desktop has been replaced by a shortcut to the master copy of the frontend on the server! Sometimes they do this because they just didn't realize what was happening, but other times because they think this is "better"! I have one client where I left detailed written instructions on how to set-up a new computer for accessing the database. Their hardware guy blithely ignores these instructions EVERY time. This has been happening for years!

Bill "I guess I'm the most qualified IT person in my department to answer this. We don't support Excel in any way other than the installation of the software which comes pre-installed on all our machines anyway. No macro writing, no template design, nothing.

As to Access, before I was hired, IT had one or 2 people with power user experience who helped users create reports in a few databases that the users or someone in their department had built, but it was always a favor, not an actual duty of the IT dept.

Once I was hired, that all changed. I took on support along with development for all things Access. I only support the ones I redesigned or databases I developed myself.

We do not support any databases created by people within the hospital. Kinda like "You made your bed. Now sleep in it." Those databases can be requested for redesign by me which would then put them in the realm of IT support.That all makes sense to me from a support perspective. Unless the IT department owns the database it really can't spend the man-hours to make it work right when it never worked right from the beginning.

Tom: At The Boeing Company (at least in the Puget Sound area--I cannot talk about other locations), IT support for all of the Office applications is limited to installation, and troubleshooting of system-related errors, such as a wizard that refuses to work. Any other support, such as how to create an array formula in Excel, design a database in Access, write any VBA code,etc. is up to the user to find help. Several years ago, there used to be an organization that one could go to to get up to 40 hours of free help without first establishing a charge line, but we haven't enjoyed this level of support since the mid 90's. These days, when people call the Help Desk for support, the folks there will often times try, but simply don't know the answer. I've personally experienced having 2nd level Tech. Help folks pass their Access questions on to me many times.

There are about 20 private Boeing e-mail distribution lists that employees can sign up for, which cover various subjects. Two of the more popular ones include:DL DevTalkOffice (covers Word, Excel, PowerPoint, Visio, Project, etc.) andDL DevTalkAccess (covers Access, of course)

There are also lists for SQL Server (DL DevTalkSQLServer), VB Classic,VC++, and a host of other topics. I naturally tend to answer a lot of questions on DL DevTalkAccess.

On the lists, Yes, the lists are Boeing supported. One must be either an employee, a contract employee, or be associated with a related support company in order to sign up. I think we still have quite a few people on the list who are located in Wichita, Kansas, who used to be Boeing employees, before Boeing sold the operations there to Spirit Aerosystems, a Canadian company.

Mike " I work for a UK Government department where the IT support iscontracted out to a three letter acronym. The company is the primepurchasing route for all IT. The support includes desktop, laptop,remote access, desktop applications, servers and networkinfrastructure.

Essentially the contract for support provides for 'best efforts' foranything they do not 'own' or have constructed under contract. Thismeans that any staff having problems with writing or debugging codewritten by themselves or another, are on their own. It also means thatif a bespoke database or spreadsheet is required and the users do nothave the skills to develop a solution there is a development cost tobe contracted for and added to the bill.

We have many solutions provided to us by specialist contractors, asthey have done so for several years. These are dying into a hard coreof experts, as where the contract comes up for renewal the acronym isallowed to put in a bid. The primary supplier is not always successful.

For most day to day work this is not a problem as there are a numberof expert users around who have the skills to handle small systemsdevelopment. These still have to go through a development process tojustify the work and usually are used as a test case prior toextending into a larger development process. A issue can be use ofdevelopment servers, which are not available to staff for databasework, and the constraint of using MS Access or Excel. It would bebeneficial to use Microsoft SQL server is some situations.

The current working environment is such that being a developer is veryhard work. For example no one has a permanent desk, all work is savedto file servers and not all desktops are loaded with more than thebasic Office products which does not include Access. Anyone could bein the seat I occupied yesterday.

A feature of current reorganisation is to employ only key workers fullor part time. This is beginning to mean only policy experts areretained, any 'support' staff are contracted in when needed. Thus theIT knowledge and development skill are being eroded for the core staffand increasingly IT development is being contracted out, and thatincludes all IT training. Interesting times.

What parts of Access should IT staff support as a minimum ?
Advise on product suitability for project being planned.
Installing and uninstalling and testing there is a operating product.
Provision of correct NT/desktop permissions for operation of product.

What are three things that IT help teams should know about Access so that they can provide at least some support?

Differences/benefits for upgrade between last 3 versions and servicepacks;Directions to a least a dozen MVP and quality Access and VBA web siteswhere help can be found;Advice on the key elements of stepping into product development, likea planning and documentation tool.

>Comment You may have divined from my comments above that I am not averse tothe changes taking place. I appreciate what is happening and whybecause I was the Local IT manager for about 150 people for severalyears trying to control the chaos that ensues where uses are givenlots of toys.

I welcome new developers to my desk. They leave, I hope, with morethat what they asked for. Namely links to helpful web sites and a fewpages for documenting the development process.

>In closing So good to see you (Garry) gained the MVP badge. I should have commented on that great result a long time ago. I've seen your good work from afar for the best part of 10 years. Very well deserved.

Tuesday, October 23, 2007

SQL Server 2005 Express Edition - Easy Setup

SQL Server 2005 Express Edition - Part 9 - Managing User Instances

This article describes the unique functionality of SQL Server that allows you to distribute and implement single-user databases (typically with applications they support) without the dependency of having administrative privileges or the need for a cumbersome configuration.

Wednesday, October 03, 2007

Removing Time from a date field

I was faced with a date field (called eDate) that also held time values as well as the date for some entries. To remove the time from the date-time entry, I used the following update query

UPDATE eInvoices SET eInvoices.edate = Format([edate],"dd-mmm-yyyy") WHERE (((Format([edate],"Short Time"))<>"00:00"));

Tuesday, October 02, 2007

Open a form and find a record

The Access buttons wizard includes an option to create a button that Opens a Form and find a record(s) based on a filter. This is fine if you want to filter the form but if you want to find a record and not filter try this code

DoCmd.OpenForm "FX_MyNewForm"

DoCmd.FindRecord Me![FieldOnCurrentForm], acEntire

Monday, October 01, 2007

Pickup Only in EBay

I know this has nothing to do with Microsoft Access but I had a lot of trouble trying to specify how to make a cot pickup only in eBay that I decided to pass on these notes.

To apply the Pick-up only option to your listing, please follow the steps below:
1. On the Sell Your Item form, click on Show/Hide Options from the top right hand side of the page.
2. Click on Postage from the left.
3. Select the '3 domestic services' option from the drop-down menu on the right.
4. Click on Save.
5. Scroll down the page, and choose Pick Up Only from the drop down box in the Postage section.

Wednesday, September 12, 2007

Log Me In

I was given a demonstration of as a way of supporting Access databases on remote computers. It looked professional and my friend was very happy how it worked at many of the Access sites that he supported. He has used many of the other similar remote login tools over the years and this is the new hot kid on the block.

Friday, August 17, 2007

SharePoint and Access 2007

Anthony, a reader wrote "How do you think SharePoint will change the MS Access front?
Do you think that they will stay separate products? do you think that there will be a migration of users and Developers from Access to SharePoint.

The one thing that I always liked about MSAccess is that it created the process of a business, it gave developers, and end users the flexibility to define, and customize their processes into a system. Now that workflow foundation is a product for SharePoint, I personally see that a lot of the prior MS Access technology will eventually head there, maybe not immediately, but I do see it as something that will happen down the road."

Garry thoughts "Whilst I have only a bit to do with SharePoint, it is very important to understand that Microsoft put a lot of effort into making Access the primary Office 2007 vehicle for interfacing and programming SharePoint. I believe that Access will loose a few customers to SharePoint lists but will make up the same and maybe more by the ability to link to SharePoint lists. So I don't think we should be to concerned because in we now have a way of using a rich development tool (Access 07) with SharePoint thru linked tables. i.e. we now can reach a wider audience. Trust me, Access forms with tabs and subforms and the new interactive '07 reports are still way more flexible than similar offerings in SharePoint"

Friday, August 10, 2007

Highlighting Text In A Field

Thanks to Kath for bringing this simple tip to my attention.

If you want to ensure that the text in a field is easy for the data entry person to overwrite, use the following code (for a field named txtName).

txtName.SelStart = 0
txtName.SelLength = 10

Wednesday, August 08, 2007

SharePoint Server and Workflows and Access

Andy Baron describes how Access and SharePoint server work together to produce a Workflow solution.

Switchboard Alternative

A possible alternative to the Access switchboard.

I asked Brandon the developer "Do you need to install something on each computer or is it all internal access forms?" He replied "It's all internal access forms. No install necessary, no ActiveX required (just ADO). Users just create their own forms and/or assign existing ones to menu buttons.
Click here to view

Sunday, July 29, 2007

To Save or Not to Save Records

Sometimes people like to know when a record has been changed before closing a form
This function will help you in that regard

Public Function ConfirmRecordChanges() As Boolean

'Confirm record changes

Dim intSaveChanges As Integer
On Error Resume Next
ConfirmRecordChanges = False
If Forms(CurrentObjectName).Dirty Then

intSaveChanges = MsgBox("Do you want to save the changes?", vbYesNo, "This Record has been modified")
If intSaveChanges = vbYes Then
On Error GoTo errHandler
RunCommand acCmdSaveRecord
ConfirmRecordChanges = True
RunCommand acCmdUndo
End If
ConfirmRecordChanges = True

End If

Exit Function

MsgBox "Error number: " & Err.Number &amp; vbCrLf & vbCrLf & Err.Description, vbExclamation, "Problem Saving Record"

End Function

Friday, July 27, 2007

Making a Tab Control Page visible

If you want to make a page in tab control visible, use the following code.

Dim tabCtl As TabControl, ctl As Control
Set tabCtl = Forms!YourFormName!tabCtrls '~Your tabcontrol that holds the pages

For Each ctl In tabCtl
If ctl.Name = "pgeIdentifier" Then
ctl.Visible = True
End If

Next ctl
Set tabCtl = Nothing

Wednesday, July 25, 2007

Folder Listing

Its a really old trick but if you want a listing of a folder, open MSDos window by typing cmd into the Windows run box. Now cd to the folder that you want to list i.e.

cd c:\projFolder

Now type

dir/b > list.txt

Default values for combo boxes

Did you know that you can make the first row of data in a combo box, the default value for that combo box? Open the form that the combo box is on in design mode and select properties. Find the default value property and type the following into the default value.


Please replace the name of your combo box with [cboCustomer]. Leave the term [itemData] as is

Monday, July 23, 2007

DataModel.Org ~ A place to find out about data modelling

Scott McManus writes

This website provides a good overview and introduction to the Mystical world of datamodeling, symbology and cardinality. It explains the reasons why we would want to normalise our data and the levels of normalisation and what they do in simply easy to understand language and has some good diagrams that would normally be used in an Entity Relationship Diagram. It is not a pretty web site and has not been updated often. But then the topic is fairly static, it also provides a list of relevant texts one can source that will further explain Normalisation. I like this introductory book on Database management with a bonus that it uses MS access as its explanation tool.

DataModel.Org Home Page

Click for book

Saturday, July 21, 2007

The runtime download is available for FREE!

On this page you will find out more about the free runtime kit and developer extensions for Access 2007.

Note: If you are intending on using the runtime kit, think about your target market.

If your target market is running Office 2007, but not Access this is a good approach. If your target is not running anything Office, it should be good.

If your target market is using older versions of Access or Office, you will need to do a lot of testing or at least be on your customers computer so that you can wind back the install if it wrecks your customers older versions.

Clint Covington: Software design, Microsoft Office Access : The runtime download is available for FREE!

Thursday, June 28, 2007

The Other Insert Query

If you turn an insert query from design view into SQL view, it will look like this

INSERT INTO participants ( projectid, person )
SELECT 1002471 AS Expr1, '1453703' AS Expr2;

Now the select statement is a bit ugly for a single line entry. Following is a more elegant way to add values.

Insert into participants (projectid, person) values ( 1002471,'1453703')

Tuesday, June 19, 2007

User Interface In Office 2007

Find out about the 10 different user interface elements in Office 2007 as follows
The Ribbon
Tabs, contextual tabs, program tabs
The Office button
Live Preview
Enhanced ScreenTips
The Quick Access Toolbar
Status Bar

Saturday, June 16, 2007

Making an image behave like a button

Sometimes you want to make a button with a favorite (small) image and you don't have a bmp file or a ico and cannot make one. In this case add the image that you want to your form.

Set the following properties on the image,
size mode = stretch (if the image is too large)
special effect = raised
name = "Something meaningful"

Now setup the following code for the onclick event.

Private Sub fetch1_Click()

'Make an image behave like a button
fetch1.SpecialEffect = acEffectSunken

YOUR CODE HERE (ie docmd.openform )

fetch1.SpecialEffect = acEffectRaised

End Sub

Here is a sample of an image as button

Tuesday, June 12, 2007

Google Gears API Developer's Guide - Home

Here is the information on Google Gears, a very beta version of local software that includes a database.

Google Gears API Developer's Guide - Home

SQLite home page

Google has snuck out an announcment for local PC development called Google Gears.
Here is the database that they are using.

SQLite home page

Implement User Preferences in MS Access

This article by Danny Lesandrini shows you how to use the registry (simply) to save and retrieve user preferences.

Friday, May 18, 2007

Vista's Integrated Search Capabilities

Whilst it is not exactly MS Access coding, how about using a SQL query and a bit of code to search your computer for a file

An Introduction to Vista's Integrated Search Capabilities

Friday, May 11, 2007

MSXML Illustrated

The first two pages of this website provide a nice introduction to using the MSXML object to create an XML file.

Click to read

Wednesday, May 09, 2007

Security Considerations and Guidance for Access 2007

Summary: Find out how you can use Access 2007 to protect the information in your databases. Learn about encrypting files, administering passwords, converting databases to the new ACCDB and ACCDE formats, and using other security options such as SQL Server. (18 printed pages)

Read More at MSDN

Monday, May 07, 2007

Add this blog to your feeds

If you like this blog, you can subscribe to it by clicking on the

Subscribe to
Posts [Atom]

link at the bottom of the RHS column of this page.

I will keep different content in this blog to my main Access website.
Garry, editor of and Access MVP

Friday, May 04, 2007

Access Unlimited News - Issue 77

The latest edition of my newsletter, Access Unlimited - Issue 77 is available online.

Send SMS with Skype 3

Send SMS with Skype 3

On this page I will show you a simple way to send an SMS using Access VBA and Skype. All you will need is Skype credit and Skype 3 or higher internet phone system.

Wednesday, May 02, 2007

Consistent Filtering In Access 2007 and Excel

In Access 2007, one of the considerable achievements is greater consistency with Excel. One of my favorite features is Filtering.

In Excel (First click on the Filter Button) then click on the down arrow at the top of the Column. In Access 2007 (2nd picture), click on the down arrow at the top of the Column.

Using A Form Field In A Crosstab Query

A client asked me how to use a form field in a crosstab query. Initially I thought it would be a matter of adding the following in the criteria field of a query

Unfortunately this brings up the error Microsoft Office Access does not recognise FieldName as a valid field name or expression. Much hunting around and then I remembered that you can use a Function as criteria for a query. So I wrote the following Function

Public Function CrossTabParam() As String On Error GoTo error_exit
CrossTabParam = [Forms]![_MyApplication]![cboProduct]
Exit Function
MsgBox "Error in function { No. " & Err.Number & "} " & Err.description
Exit Function
End Function

Now i changed the criteria to be and it works... Here is the working crosstab

WHERE zSales_Demo.ProductName =CrossTabParam()

The example is taken from a table in my popular graphical program graf-fx.

Tuesday, April 24, 2007

Macro protection in Microsoft Access 2007

This video shows you the best way to manage macro protection in Microsoft Access 2007. In other words it allows you to make sure that the message bar that says Security Warning "Certain content in this database has been disabled" no longer appears. Garry Robinson has written an article on this topic for Microsoft in the MSDN Web site. The video shows you how to set up a trusted location for your databases.

Click to see video

Monday, April 23, 2007

Backup Basecamp from XML into an Access database

Find out how to download your complete basecamp project and import the xml into a new Access 2002,2003,2007 database. You can then reload the next basecamp xml into the same database.