Welcome To Access Unlimited - Edition 21
See all newsletters

Access Unlimited is an email newsletter that provides free tips,  help and information
for skilled Microsoft Access users and related software disciplines.

Produced by Garry Robinson from Sydney, Australia.

In this edition,

EMAIL AND REMOTE EMAIL IN ACCESS
A QUICK RECORD COUNTING TRICK
BACKUP YOUR TABLES USING XML (TEXT)
ACCESS 2000 VERSES ACCESS 97
SEARCH YOUR ACCESS HELP FOR SOME GOOD READING
AND YET THERE IS MORE ...
GOOD READING AND USEFUL SITES


---------------------------------------------------------

EMAIL AND REMOTE EMAIL IN ACCESS

This edition of the newsletter has a download database that will show you some simple email techniques in Microsoft Access. I put this little database together because I have been thinking that I had to find a way to prepare quick note emails on my laptop whilst on the road. Until a week or so ago, I thought that the only way possible was to setup up email synchronization with Outlook 2000 on both my laptop and my email desktop. This would mean leaving my cherished Outlook Express and installing the big Outlook beast. Then it occurred to me that I could actually type my email messages into a database and fire them off when I synchronize the database back to my desktop using Microsoft briefcase. As my contacts/billing database is always getting opened in both environments, this seemed like a really resource efficient way to handle the remote email issue. As a bonus, it also meant that all my outgoing mail was being stored in the same outbox.

When the database opens, you will find that the FX_RemoteEmail form is opened. From here you can click on the button next to the record and that will launch the email that you typed in a some earlier date. This form will only open automatically when there is a record in the remote email table. Once you have launched the email message, you can delete the email record.

There is also a form called FX_EmailLaunch. This shows you how to launch a single email using a combo box. It will also add emails to the RemoteEmails table using the combo box and text boxes on the form. In my contacts database, I fill the combo box with my contacts and email addresses. I have found over the years that whilst my email client software comes and goes, my contacts database always stays with me. That is why it is good to use the email addresses in your contacts database from time to time to make sure your address are upto date.

Enough said, why not download the example database in Access 97 (33k) from

http://vb123.com/get/accessemail.zip

--------------------------------------------------------- 
A QUICK RECORD COUNTING TRICK

Interested in a piece of Access visual basic to check that there are actually are some records in the table that you just imported or are about to run a query on. Well try this to count the number of records in the remote email table.

If DCount(1, "RemoteEmail") > 0 then 
  MsgBox "Empty Query/Table" 
End if

Note the use of the 1 to denote the first column in the table. The 1 in the first box refers to the first column in the table or query that you are counting the rows. When you use a number for a count, ensure that it is a field that always has values (like a key field).

When you open the newsletter download, you will find that the Autoexec macro that I have setup uses this technique in the condition column as follows

Dcount(1,"RemoteEmail") > 0

For those hairy chested programmers that think that macros and Dcounts are for sissies, using these simple Access tools on occasions can save your clients valuable programmers dollars. That said, do not use "Dcount" in a performance sensitive application without evaluating alternative recordset count methods.

Finally the same Dcount query in SQL would look as follows

Select Count(1) as FirstColumn from RemoteEmail

Isn't it interesting that you can refer to a column by its numerical position. You won't find that in the Access help guide.

--------------------------------------------------------- 
BACKUP YOUR TABLES USING XML (TEXT)

In the February edition of XML developer, Ed shows how you can backup all your Access tables/linked tables to XML and then goes on to explain why this is a smarter way to backup than the slightly buggy save as text method that comes with Access.

http://www.xmldevelopernewsletter.com/

Unfortunately you will need to sign up from XML developer to get a look at the article, but there is always the offer of the free trial subscription to get your hands on this edition. You will find this on the main page of the web site.

--------------------------------------------------------- 
ACCESS 2000 VERSES ACCESS 97

I know its now 2001 and Access 2000 has been out for about a year and half. So do I now develop solely in Access 2000 and leave Access 97 on the shelves. Well here is my answer.

I simply leave that decision up to my client and guess what, most of them just do not seem to be bothered with upgrading to 2000. Now if they ask me whether they should, I would first ask if any of the persons in their company has upgraded. If they haven't I would say, just do not bother.

If I was developing just for myself, I still prefer the Access 97 environment. It is easier to get around and forms development seems far easier. Which brings me to a little tip.

If you develop 70% of your time in Access 97 and very occasionally in Access 2000, you can uninstall Access 2000 in a temporary manner by setting the software up for Install on first use. If you then open Access 97, all the MDB files will be reset to only use Access 97. When you desperately need Access 2000 back for development, just open the Access 2000 shortcut and it will start installing for you. This way you can be surer that the 2 systems are not clashing. I had to do this on my laptop as Briefcase was giving merging errors due to some incompatibility with the two systems.

As for Access 2001/XT, I guess I will just take the same approach of developing in the client database of choice. But lets just hope that the application reverts to some of the traits that made the 97 version such a neat little tool. Also I will recommend to clients to wait till the first bug fix/patch is available on the installation CD Roms.

As an aside, only one of my clients has jumped up and down and said that they are really excited about converting to SQL server/MSDE. Guess either that I must have worked for 50 really odd companies over the last 6 years or maybe Microsoft's push to convert every one from JET technology to SQL server is not quite hitting the mark.

-------------------------------------------------------------- 
EMAIL FILTERS

Here are a few words to add to your email filters to keep out those rubbish spams

'million' or 'rich' or 'fortune' or 'millions' or 'spy' or 'sexual' or 'Now!' or 'Miracle' or 'adult' or 'Now' or 'profits' or 'Secret' or 'Satellite' or 'Cash' or 'dream' or 'homeowner' or 'Judgement' or 'Viagra'

-------------------------------------------------------------- 
SEARCH YOUR ACCESS HELP FOR SOME GOOD READING

In Access 97, a really good description of Error handling can be found by searching for the following phrase

"Elements of Run-Time Error Handling"

Unfortunately the same or similar phrases does not return the same article in Access 2000 or VB/MSDN.

----- OR -----

Search Access for "Automation with Microsoft Access"

This will bring up some background material for the following example showing how you can launch another Access database from your current database.

Dim appAccess As Access.Application Dim strDB As String

' Initialize string to database path. strDB = "c:\Graf-FX\Graf97.mdb"

Set appAccess = New Access.Application 
' Open database in Microsoft Access. 
appAccess.OpenCurrentDatabase 
strDB appAccess.visible = true 
' Make the database visible 
appAccess.RunCommand acCmdAppMaximize
appAccess.DoCmd.OpenForm "Your Form Name"

MsgBox "Click Here When Complete", vbInformation, _ 
  "The other database will open in a separate window"

On Error Resume Next 
appAccess.CloseCurrentDatabase 
Set appAccess = Nothing

-------------------------------------------------------------- 
AND YET THERE IS MORE …

From Alistair,

I thought it might be worth sharing something with you, that you might consider putting in your newsletter.

The general wisdom when putting an Access database on the web, is to create an ODBC System DSN on the server and refer to that by name in your ASP code. This has one major flaw if your site is hosted by an ISP rather than yourself. If the ISP decides to upgrade kit, or move your site to another server, it is easy for them to overlook the fact the DSN needs to be recreated on the other machine. It is after all not part of the site as such. This happened to me and it was a few weeks before I discovered it!

The solution is to refer to the database file directly within your code, as in:

curDir = Server.MapPath("../mypath/mydata.mdb") 
Set oConn = Server.CreateObject("ADODB.Connection") 
oConn.Open "DBQ="& curDir &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;" 
Set oRs = Server.CreateObject("ADODB.RecordSet")

The doesn't affect the performance of the database and, because this is a direct file reference within your ASP code, and it obviously lives within your site, if the ISP moves the site, it doesn't crash it. You do have to be careful with regard to the connection string (oConn) in the above example as there is a known issue with it length and the number of spaces (refer to the MSKB for details).

Alistair Hamilton Beta-microSolutions - Independent IT Services 
Website: www.beta-microsolutions.co.uk 
Tel: 0131 3333972

And from Joe

Hi Garry,

Thank you for the Tips and Tricks newsletter, good stuff..! You mention contributions for the next newsletter, well I would like to contribute a link to my Techniques Page that contains downloadable demos on various Access development techniques.

http://www.users.globalnet.co.uk/~dataflo/technique.htm

and also a link to the e-groups Access Developers forums which is one of the best around. 

http://www.egroups.com/messagesearch/access-dev

Thanks again, Joe Davies (MS Access / VBA Developer)

 _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/

GOOD READING AND USEFUL SITES

All the Smart Access free articles can be found by clicking here

Creating wizards using Office forms.   Notes also on programming the Balloon
http://www.microsoft.com/TechNet/office/TechNote/wizard.asp

Programming command bars, FileSearch and The Office Assistant
http://www.microsoft.com/TechNet/VBA/TechNote/off307ef.asp

The SendKeys command in Access can send its key strokes to other applications.  So here is an alternative solution using Windows API calls.   Or simply tell your users not to run any other programs whilst running the command.
http://www.mvps.org/access/api/api0046.htm

You can choose the colour or font from using the dialog boxes from Windows.
This article and download tells you how.
http://www.mvps.org/access/api/api0060.htm

A vb class library that allows you to read text files and don't we all need to
do that from time to time (well about 10000 times in Ed's case)
http://www.mvps.org/access/modules/mdl0057.htm

Unsupported Microsoft Products... No more needs to be said
http://support.microsoft.com/directory/discontinue.asp?sd=gn

A successful website - here are some timeless tips for your html pages
 http://www.web-source.net/webpage_optimizing.htm

Regional settings on your computer
http://vb.oreilly.com/ron/short_date.html

Some Visual Basic Tips
http://www.codeoftheweek.com/bonus/mstipoftheweek.html

CREATE YOUR OWN EBOOKS FOR MICROSOFT READER
Just download the Microsoft Word 2000 add-in from the Office Update site, and use it to convert existing Word 2000 documents, or new ones you create, into Microsoft Reader eBooks. Microsoft Reader makes on-screen reading more like reading a printed book. Become your own publisher today. Download the Word 2000 add-in for the Microsoft Reader at:
http://officeupdate.microsoft.com/2000/downloaddetails/WordRMR.htm

To learn more about the Microsoft Reader and to download it, visit:
http://www.microsoft.com/reader/

Backing Up Your Computer
http://msn.zdnet.com/msn/zdnet/story/0,12461,2667319-hud00025hm3,00.html

Building Help Systems - Try
http://www.htmlgoodies.com
http://www.webmonkey.com
http://www.zdnet.com/devhead
http://www.webreference.com/dlab
http://www.vb123.com/toolshed/99_fpage/fp1.htm

Hot Access Downloads
ftp://athree.com/pub/candace/access_downloads.htm


-------------------------------------------------------
WRAPPING THIS EDITION UP

"We have the future to look forward to!"
Al Gore, 2/19/95.

-------------------------------------------------------
-- OUR SOFTWARE AND RESOURCES -----------------------
Explore your data visually using our popular Access
data mining shareware
---> http://www.vb123.com/graf/

View our web site as a searchable eBook and have access
to all the downloads discussed in the articles and information
pages at the popular  vb123.com web site.
---> http://www.vb123.com/toolshed/


So thanks for reading our popular newsletter.
Feel free to make comments, copy the email to a friend
or maybe even contribute to the next edition.


Garry Robinson - Software Consultant




Click on this button to read the previous edition of Access Unlimited 

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals