Welcome

Hi,

Welcome to the first edition for 2013. I am not sure where this year is disappearing to, I cannot believe it is already February.

2012 was a very busy year for me personally, so my website and newsletter suffered a bit and I had such great plans for 2012. Oh well.... 2013 is a new year and I will try again.

My goal for my Newlsetter will be fortnightly (for those of you who are not Australian, this means once every 2 weeks)

I hope all is well with you.

The other objective this year is to split my original VBA lessons (which comprises of 56 lessons, each about 15 - 20 pages long) into individual lessons and sell them seperately via topic.

Thus the low price below, will soon be removed. So if you wish to purchase ALL the lessons for just $7.95 (AUD) then you will need to do so by February 28th 2013.

As an added incentive, if you purchase the 56 lessons for Microsoft Access (2000-2003) but have Microsoft Access 2007 or 2010, I will send you the lessons 1-5 for Microsoft Access 2007, free of charge. These lessons cover the major differences between the two versions, you should then easily be able to follow the other lessons.

If you are interested in purchasing these lessons, then they are available below:

Microsoft Access 2003 and earlier, is still available for purchase for just $7.95 (AUD)

Microsoft Access 2007 VBA Lessons 1 - 5

just $2.95 (AUD) to buy click on the button below:



Lessons 1-5 covers:

• Introduction into VBA
• How to convert Macros to VBA code
• The difference between Modules, Subprocedures and Functions
• Overview of DIM
• Overview of DoCmd

Microsoft Access 2007 VBA Lessons 6 - 10

just $2.95 (AUD) to buy click on the button below:

Lessons 6 - 10 includes:

• An overview of Constants
• A look at Collections, Properties, Methods and Events

It also includes some very useful code to manage two frequently occurring scenarios.

• Adding new items, to your drop-down boxes automatically
• Cancelling a report if there is no data, and letting the user know.

Note: I am using a new delivery system, it still uses paypal, but now the lessons will be available for immediate download. So if you have any trouble PLEASE PLEASE let me know by emailing me at julie@simplyaccess.com

One more plug, before I start on the tips. If you want a great product to convert your Microsoft Access database to SQL, Click Here I have used this product from MUST on some of my databases and found it works...which is the main thing, but it is also incredibly easy to follow the steps required.

OK enough of the product talk, on to the tips. This week I have I will continue with my normal format of three tips. I am first going to share these with you here, then I am going to load them up on to my website as pages.

Most of my tips are either items I am dealing with in the current databases I am designing or are questions asked at All Experts

Microsoft Access Tips

Tip One: Link Table Manager

Question:

I have created a small application on my local drive in the office, which works fine.

I copied the db to my home laptop to do some more work on it and decided to split the database while I was at home. That worked fine and left me with an .accdb and _be.accdb file, as expected.

I brought the pair of files, plus a Record Locking Information file, to the office on a memory stick, copied it back to my local work folder and now it can't access the data, as the path is still pointing to my home folder.

How can I "unsplit" the two? Do I need to create a new db, and import the data and code separately into it?

Thank you for any help you can give me.

Answer:

The easiest thing to do, is to use the link table manager.

To find the 'Link Table Manager' right click on any of the linked tables and choose link table manager from the dropdown list.

Choose to Select All

Then OK

The browser window will open, browse to locate your back end. Double click on the file. All the tables will be reconnected to the back end.

You will need to do the same again when putting it back on your laptop. The alternative to this is to set up the folders (path) the same on both computers. This might mean making another drive on your laptop if you have a different drive (network) letter on your office computer, depending on how your office computer is set up.

Tip Two: Alternatives to Outlook

Question

I have a database that 30-40 people use daily. Once a month the first person to log onto the database will have a macro run automatically. What this macro does is update a table that contains the date and it increases the value by 1 so it will run again next month. I then have sendobject creating an email with attachment (Reports) for expired training. I have been looking for a way to get all 22 reports attached to one email but it seems to be impossible I have read that a program called click yes works however I cannot download a third party program on 30 computers across the company. So I am taking my last stab at it with you. Do you know of any way this can be done without third party programs or am I stuck with 22 Are you sure you want to send this email automatically boxes?

Answer

There is another way around this by using SMTP rather than outlook. This will send an email without using outlook.

I went down this road quite a few years ago, at the time it was all a bit too complicated and time consuming to do what I wanted, so I ended up purchasing a 3rd party program. Produced by fms

Total Access Emailer

Called total access emailer. Not cheap, but for me it was worth it for the hours saved. I still use it.

If you want to go down the SMTP road here is a good link.

Using SMTP to send emails

Tip Three: including and excluding data in a query

QUESTION

I am currently working with an access query that provides different results of different property types for different divisions. One manager wants me to exclude division “x”, with property type “n”, if it particularly has result “y”. The problem is that other divisions may also have property type “n” with result “y”, so I don’t want to exclude the results for those other divisions. The datasets are small, so I want to prevent building multiple table/queries in order to exclude criteria for this one particular division. Make sense? I am so confused on how to do this!

Answer

Build your query, adding the fields you need.

Then in the first row of the criteria, add the following criteria for each of the fields.

Note: Not sure if the fields are text or numbers, so I have included both examples, just use the appropriate one for the appropriate type of data type. Text criteria, include the Like command - numbers do not

Division

Not Like "*x*"

<> x

Property Type

Like "*n*"

n

Result

Like "*Y*"

y

This is the same as asking the query,

To exclude records that are 'x' divisions and have property 'n' and result 'y'

As the result has to equal all sections, (and) then those divisions that are not 'x' will be included.

Hope that all makes sense.

Cheers