Access Unlimited June 2009

The Access Unlimited Magazine from

123

.com

If this email is in text format in the Junk folder, drag the email to the Inbox and add the email address to your Safe Senders list.

Greetings Microsoft Access and Office Developers,

For more than 10 years I have been putting together newsletters together and battling with the technologies that go with

this pursuit. In the last effort, I used an online providor that read directly from my RSS feeds at my

http://vb123.blogspot.com blog. This online providor went bust. This time around I decided to tackle the problem using

FMS Total Access Emailer, an Access report and the information from my blog. Let me know how this goes by posting notes

at the blog.

Whatever happens, as I own the rights to publish Smart Access, there is a lot of material coming your way and it should be

fun getting the content reinvigorated and out to you.

To get the ball rolling, I have included the posts from my blog for the last 6 months or so.

Garry Robinson

Product Links

_____________________________________________________________________________________________

Using Access 2007 to Explore Your Data

Garry Robinson

Microsoft Access has always had a good collection of tricks to allow the normal person to explore their information. This

paper describes what techniques you can use to explore the many data sources that are readily available in Access.

View article… http://www.vb123.com.au/toolbox/09_access/dataexplore.htm

_____________________________________________________________________________________________

Preparing Your Database For SQL Server

Garry Robinson

Following on from my blog earlier on the month on learning about SQL Server, I have written an article discusses things you

can tackle before upsizing your tables to SQLServer. I do this because once you are in an environment where you have

Access as a front-end and a SQL Server back-end, things are going to get more complicated. Another good thing about this

article is that it will probably improve your database model and reduce the size of your database at the same time.

http://www.vb123.com.au/toolbox/09_access/upsizingtosqlserver.htm

_____________________________________________________________________________________________

Searching All Tables In A Database

Garry Robinson

Recently I was asked to produce a general "search all the tables in the database for a text string solution". This sent me

scurrying back to an article that I wrote for a visual basic magazine in the 90's. I modified this for Access and wrote an

article on the topic.

View article… http://vb123.blogspot.com/2009/02/searching-all-tables-in-database.html

_____________________________________________________________________________________________

Creating Charts/Graphs in an Access 2007 Database

Garry Robinson

When I first started with Microsoft Access back in 1995, I focused on writing a data mining tool using Microsoft Graph.

That tool of mine still sells a few copies today and it still works in almost the same way in Access 2007 as it did in Access 2.

Now whilst I did my upgrade about a year and a half ago, I never actually developed any custom charts for clients.

Therefore, it was surprising to me when a client asked me about charts that I found it was very hard to find out anything

about this charting tool in Access 2007. So here is my story on Access 2007 Graphs

View article… http://www.vb123.com.au/toolbox/09_access/access2007charting.htm

_____________________________________________________________________________________________

Pictures from Access 2010

Woody's Access Watch

Woodies have posted pictures of the Access 2010 interface. Of the most important of all those photos is that the VBA

interface remains. This means that VBA will be around for a lot longer though I guess there might be some tweaks in the

name of security or 64 bit support or ???

http://news.office-watch.com/t/n.aspx?a=908

but of course everything can change from here till release date.

_____________________________________________________________________________________________

Smart Access is Back

Garry Robinson

Two months ago I was drinking a good coffee at a coffee shop and I noticed that they had two large coffee machines. I

asked the guy making the coffees why he had two machines and he explained that they had purchased an old Italian classic

machine just to try and make better coffees. For that guy, making the best coffee for his customers was his passion so

why not do it better.

That inspired me to try the owners of the Smart Access magazine that I used to write for one more time ( I have been trying

for 3 years now) to see if they would sell me the rights to exclusively publish the magazines. Surprisingly they said yes.

So I purchased the rights* for a good sum and now I can use the content to make a really great website. In the first instance

I have reorganised all the magazines and downloads and anyone can purchase all the digitial copies of the magazines and

put them on their computers. Hopefully this will make me enough money to spend a lot of time taking the best of the

300+ articles and making them easy to find and relevant to Access 2007 and beyond.

If you interested in this great magazine, go to this page.

Http://www.vb123.com/smart/

If you can promote my venture that would be fantastic but don't fuss too much, google "and live search" will be my best

friend as I have been doing this stuff for years.

* The editions that I purchased go from the days when Ken Getz, Paul Litwin and the other Access Developers Handbook

authors wrote every other month through to the time when Peter Vogel ended his reign of 10 years as editor. These were

the Golden Years, 1996 -2006.

Garry Robinson

The New Passionate Owner of Smart Access

_____________________________________________________________________________________________

Mini Links - Check Out Our Twitter Site

Garry Robinson

Our twitter site will get a short message + links to our new (Smart Access) content as it is posted so it might be worth

signing up or using the RSS feed. What the twitter site won't get is gossip. Http://twitter.com/smartaccess

_____________________________________________________________________________________________

SharePoint Designer 2007 is now free

Garry Robinson

In Office 2007, Frontpage split into 2 streams, Expression Web and SharePoint Designer. Both have the same core tools

but differ in emphasis. Now instead of being a $200 tool, SP Designer is Free. Check the link for more but remember that

you can use SharePoint hosts for $20+ a month from sites such as http://www.sharepointhosting.com/

_____________________________________________________________________________________________

Why VBA is sometimes illogical.

Wayne Phillips

After some discussions over at UtterAccess, I was informed that the following If-block handles the case where MyVar (a

variable) is a variant of type Null.

Let's see why... http://vb123.blogspot.com/2009/04/why-vba-is-sometimes-illogical.html

_____________________________________________________________________________________________

Oracle is Buying Sun/MySQL

Garry Robinson

Oracle has announced that it is going to purchase Sun Microsystems which means that Java, MYSQL and other things are

going from a company that was unlikely to charge for these things to a company that generally charges a bomb for

everything. I think this will be good for Microsoft who generally charges less for similar products that Oracle sells becuase

it has higher sales volumes. http://www.guardian.co.uk/technology/blog/2009/apr/20/oracle-sun-takeover

_____________________________________________________________________________________________

Silver Stocks and Stuff

Garry Robinson

I know it has nothing to do with Access programming but I have launched a very low key blog to help Aussies find out

more about investing in silver on the stock market. I only did this because unlike gold, silver investing is not something

people get into in Australia and I just couldnt find many good places to start.

http://ozsilverlining.blogspot.com/

And you say why bother with silver at all? Because it tracks in the gold price and when gold price spikes, silver generally

goes up more. But if gold goes down, the maths are quite simple :(

_____________________________________________________________________________________________

Using the Currency field data type internationally

Wayne Phillips

Today I was reminded of the age old problem of the Currency field datatype in Access not reflecting the current users

regional formatting settings as set in the Operating System.

The problem is that by default, Access stores the format as specified on the developers machine and does not change the

formatting if an end user has a different currency format set in their regional settings. Some would see this as expected

behaviour. Some would see this as a flaw. If you consider this is a flaw, fortunately there is a simple fix. Please note: The

solution discussed in this article only works for tables, queries and _bound_ form/report controls

Wayne Phillips

http://www.everythingaccess.com

View article… http://vb123.blogspot.com/2009/03/using-currency-field-data-type-without.html

_____________________________________________________________________________________________

Get ready for the Economic Upturn

Garry Robinson

If you are in the unfortunate position of not having much paid work on in this global economic downturn, now is the time

to upgrade your skills because if you are a skilled developer who can help people solve business problems, positive times

will not be far away. So as it is likely that you are an Access practitioner, I suggest that the best way to improve your skills is

to push into areas where Access provides a stepping stone. To me SQL Server is one of those areas and here is what you

have to do.

1. Identify the database that you most proud off and convert it to run with a SQL Server backend.

2. Do not stop when things get hard, solve all the problems.

3. It would be best if you then started using that database in your day to day work

4. When that works, start converting the Access queries to Views, Pass Thru Queries and Stored Procedures

One thing you shouldn't do though is become 100% immersed in your unpaid projects because its always better to write

software that people need. For this you will need to make sure that you attend conferences, participate in online forums

and contact all the people that can help you get back into the thick of things. Good luck to you in your endeavours.

_____________________________________________________________________________________________

Jet MDB security - under the hood

Wayne Phillips

Due to recent discussions in the microsoft.public.access.security newsgroup, it has become apparent that some people

do not properly understand how Jet implements the various levels of security features that it offers.

Here I intend to explain everything in a clearer format. I won't be discussing Access-specific security features here (such as

MDE file protection) - nor will I be providing the actual significant implementation details of the security methods offered

– I will simply explain in brief form how each method works, ‘under the hood’.

JET MDB DATABASE FORMAT http://www.everythingaccess.com/tutorials.asp?ID=185#S1

ACE ACCDB DATABASE FORMAT http://www.everythingaccess.com/tutorials.asp?ID=185#S2

Wayne Phillips from EverythingAccess.com

PS. Garry has a book on Access protection and security http://www.vb123.com/map

_____________________________________________________________________________________________

Log First, Focus and Then Look For Speed

Garry Robinson

A number of times in 2008 I was asked to speed up an Access database. This article outlines how usage data can make this

quest more focused. The article also delves into an Access 2007 ACCDB only feature called TempVars. But firstly let's

discuss some users’ scenarios that I've had to deal with.

Situation one: A skilled Excel technician has set up a database with numerous related tables. When he rang me for advice,

he was adamant that he needed to upgrade to SQL server to speed up the database. As the database was only 20 MB in

size, I doubted this but still we had the meeting. Some of the forms were very slow, had many Tab controls with hundreds

of fields scattered across many subforms. In addition, the training users had was to scroll through records one at a time to

find the record that they were interested in looking at. Just adding a find record box made finding the data a lot quicker.

Situation two: A complex database that had been in development for six years and now the developer had left the

business. The last three years they had done nothing to the database apart from compacting and repairing the database

but the performance was woeful. In this database, some forms could take up to two minutes to close when the close

button was pressed. Also, some reports took nearly an hour to run.

Situation three: A huge database already converted to use SQL Server as a back-end; there were 250 forms and 80 tables.

Performance was sluggish throughout during peak periods.

Read on for ideas on how I approached these issues

View article… http://www.vb123.com.au/toolbox/09_access/needforspeed.htm

_____________________________________________________________________________________________

Caching For Access Databases Under Stress

Garry Robinson

I have revamped an article on using ADO to cache lookup tables into text files to confirm it works with Access 2007. An

interesting sideline in this article is the technique of writing a custom function to fill your combo/list box rather than just

using tables and queries.