Monday, December 15, 2008

Garry’s Christmas Stocking Suggestions




If I was to fill a stocking with the software that I need to help run my Access / Office Programming company, here is what I would like to see on most computers.

No matter what you think about Ribbons and the side task bar in Access 2007, the interface is here to stay and Microsoft is devoting lots of good developers dollars to the product. I highly recommend you spend time with A2007 soon if you haven't already tried it. Just be careful installing the product to make sure that you don't delete your existing version of Access.

The main place you will see the Microsoft Access developer dollars is when you use Access with SharePoint. If you don't know what SharePoint is, we use a cheap and well featured WSS 3 service from http://www.sharepointhosting.com/ If you are interested in SharePoint but reluctant to signup, our Xmas offer is we will give you a free subweb with full permissions for 3 months if you purchase a copy of our newly revamped Toolbox for $70. This will allow you to create Lists and Surveys and upload documents and use all the great wizards that make the SharePoint product what it is. You will also be able to link to SharePoint lists and many other useful wide area team functions that simply aren't available in Access. Send us an email after we deliver the Toolbox so we can set up the subweb.

Note: MSoft is making truckloads of $$$ from SharePoint and Access appears in all sorts of places in the SharePoint web interface. Its really quite exciting as the business skills needed for SharePoint are quite similar to those needed for Access.

If you like to capture screens for your help files, web pages or word files, throw away MS paint and try SnagIt Version 9. You will find the capture options are a real time saver especially with the quick save feature that I use for remembering stuff without taking notes.

All day I am switching versions of Access and moving to the folders where those databases are stored. My team and I use our Access Workbench for these tasks and it saves us quite a bit of time.

If you are looking to upgrade Access to a SQL Server backend with linked tables or even trickier to upsize your queries to stored procedures and views, I have found Andy Couch's MUST upsizer tool such an improvement that we started selling it.

If you want to write better quality visual basic, the premium tool on the market is Total Visual CodeTools from FMS. We are now an International Reseller of FMS products and will be discussing this more in the new year.

Changing the names of objects and controls is still an important part of the Access programming environment. For this we recommend Rick’s tool at http://www.rickworld.com/

Zipping files is a major part of the development process. Whenever we send out a new version of a database, we will update the version using our workbench and then store the version in a zip file with a version number. http://www.winzip.com/ is still a great tool compare to Windows folders compression. On a similar note, we like the Nitro PDF tool for creating PDF files but there are a lot of tools like this on the market.

One tool that I find really useful is Google desktop search. To fire this up I press the Control key twice and type in my computer search term. Its great for finding emails without opening Outlook. To match my use of this product, I save good web pages on my computer so that I can find them on my computer before switching to the web. Watch this tool on corporate networks, IT boffins are not so enthusiastic.

Finally if you work on a laptop, do your eyes and neck a favour and plug it into a decent screen and a decent keyboard. A few hundred bucks is a small price to pay compared to poor posture injuries.

All the best to all my readers for the holiday season

Garry Robinson
Office Access MVP

Friday, December 12, 2008

Flexible Normalization and Denormalization of Data into Reports and Forms


This article describes how I denormalize a fully normalized table into a grid form, do the data entry and post it back in the normalized table. This article also describes how Helen Feddema turns a normal flat table with lots of fields into a normalized table suitable for grouping by queries. Read this comprehensive article here

Saturday, November 22, 2008

SQL Server Upsizer Tool Gets A Mention at MSDN


The Microsoft Access to SQL Server Upsizing tool that we are promoting has been mentioned in the very popular MSDN flash magazine. Also mentioned was a 15% discount which is available with a complementary copy of the Toolbox. You can read all about MUST plus SQL at this page.


To help you understand the SQL Upsizing tool better, I have put together a video of a presentation that I made at the Office Developers conference in Australia last week. This shows how you can take a split database, push all the tables into SQL Server, upsize Access queries into Views and Stored Procedures and relink the Front-End in one repeatable package. You will find the flash video on this page.
Note: The USA registration date problem we had with MUST+SQL last month has been resolved.

Monday, November 17, 2008

Access 2007 Database Encryption


I have mentioned it before but anyway here is an article on greatly improved "Password" security for Access ACCDB format databases.




Garry

Monday, October 27, 2008

Workgroup Files ~ Have you got the correct version?

Opening a workgroup file that was created with an older version of Access is not so efficient. ie if it was built with Access 97 and you are using Access 2003.

How do you find this out?

Open Access 2000+, choose menu File then Open and navigate to the workgroup file. When it opens, if it says "You cannot make changes to the database objects in this database" then you have an old workgroup file.

To convert to a newer workgroup file is impossible, you have to recreate it from scratch as per this kb. http://support.microsoft.com/kb/824258

For that you are going to need the Name, Organization and ID that you used to create the original file. You are also going to need the PIDs of the usernames or you may as well stick with the slightly slower old workgroup file.

Now if that all sounds too difficult, you really want to ask yourself what you are doing with the workgroup file anyway. If you cannot rebuild a new one because you don't have the old credentials, then you may be leaving yourself open to difficulties down the track.

If you are running Access 2003 or less, you still can pick up my book on Access PROTECTION and security. http://www.vb123.com/map

Garry

Saturday, October 18, 2008

Posting Microsoft Access information into the clouds

In this article I show you how you can use MSXML and Microsoft Access to post information into a website using an XML based API. Whilst this sort of coding isn't easy, it can cut down on a lot of duplication between your local database and your Web2 type online tool.

Read more about posting_into_the_clouds using XML and MS Access

Garry Robinson
Sydney Based Microsoft Access consultant.

PS. A couple of Access friends in Australia have decided to head back to corporate world after a few years running their own Access consultancy businesses. We are going to help them by taking over their work, keeping them in the loop so they can earn extra cash after hours and guaranteeing them the projects if they find that the corporate life is not for them. Can we help you ? Visit www.gr-fx.com if we can.

Tuesday, October 07, 2008

MUST ~ The hot SQL Server Upsizing Tool

After converting a number of Access databases to SQL Server, we have decided to promote an Access tool that will make this task easier and more accurate. Here is a page of questions and answers on the MUST upsizing tool that explain why it is useful.

Read more on the MUST upsizing tool and find out what it can do for you. For my team, we are finding it makes upsizing easier to the extent that we will do conversions earlier in the development cycle.

Let me know what you think about MUST if you download the demonstration version and experiment with your database (by posting a comment on this Blog).

Saturday, October 04, 2008

Expression Web MVPs

I met some nice Expression Web MVPs whilst upgrading vb123.com to vb123.com.au and ExpressionWeb. Here is a how they describe their online pursuits.

Pat and Tina are teaching a class on migrating from FrontPage to Expression Web. I have one Introduction to Expression Web, both at http://starttoweb.com/ . I've got a video tutorial for beginners at http://by-expression.com/media/p/1300.aspx along with other tutorials. I also support my book on Expression Web "Foundations of Microsoft Expression Web" published by Apress on the forums at http://by-expression.com/--

Cheryl D Wise
MS MVP Expression

Sunday, September 14, 2008

New Style Website - New Article - I am in the Clouds


In this article, I describe how you can use an Access Database and VBA to manipulate a Web 2 application called HighRise, a customer relationship management (CRM) tool from the highly successful Web2 company, 37Signals.

http://www.vb123.com.au/toolbox/08_access/in_the_clouds.html

and you can check out the new Expression Web website that I am building out vb123.com content.

Note: The tool we use most from 37signals is their highly successful Basecamp product

Saturday, September 13, 2008

SQL Query Tree Editor for Microsoft Access



(Advertisement)






An indispensable MS Access add-in for developers who use queries as the core of their applications. Create queries using SQL templates, edit your SQL queries in a superior query editor. Keep track of the dependency relations between queries.

Click here to read more

Thursday, September 04, 2008

Disable The Aero Look

Windows Vista is here to stay so if you are purchasing a new computer, its a good skill to have. Off course it may not make you more effective but its fun trying something new but probably only on a new computer. Anyway one of the neat features is the aero look. But Aero comes a price, lots of extra resources which may not be there if you have 2 or less gigs of Ram or an older computer.

Here is an article that describes how to turn back the clock to the less hungry Windows Basic look

Tuesday, September 02, 2008

Where Does Access 2007 Fit In The World

An Editorial Rant by Garry

One of the important things that always seem to be missed about Access is that in all its complexity it is one single object (or file) (2 if you link). This is really easy to understand because it is the same as Excel and Word. Because people understand a file, it grows with them (and their business) and they are comfortable with the increased complexity. This is why Access can never be stomped out.

SQL Server and .Net are totally different beasts. SQL Server requires a special program to be running on computer and you have to setup your database in a special folder to make it run. Sure that is ok when it is done but how do you move the database? Who knows says the average user, better get the SQL Server expert in. No way I am doing that says the user, back to Excel it is then. As for .Net, man if you come from Excel, this isn't something that you grow into.

So it is into this world that Access 2007 is attempting to fit and becoming more like Excel is worth far more important than becoming like .Net because the BIG Excel/Word world is crying out for a simple database. This in the long run mean that ordinary people can setup basic databases for small teams of people and they will hardly know that it has happened. We should be thankful that Access was chosen for this role. Another thing we should be aware of is the simpler Access user interface may even drag those sceptical IT boffins back into Access and then the credibility of Access in the IT community may actually rise. Lets hope all those man years of research in the user labs were undertaken properly.

Garry Robinson - Editor vb123.com

Friday, August 22, 2008

Maxout Newsletter

I subscribe to many newsletters and occasionally read some of the them but the one that I enjoy the most is the MaxOut newsletter that I publish once a week (with no editorial input). This newsletter is created using the feeds from 5 Access MVPs and the Microsoft Team blog. You can sign up for it at http://vb123.com/news or view the last edition


Here is a picture of the TOC in the last newsletter.

Thursday, July 10, 2008

Programming Help In Access 2007


Looking for Programming Help in Access 2007. When you open help, choose the Pull down arrow and you will find Developer Reference.



Garry Robinson

Wednesday, July 02, 2008

10 Years As A Writer

10 years ago I wrote my first article for the now defunct Smart Access magazine (but still the best paper news on Access). Just thought I would share this occasion with all of you.
Happy Anniversary Garry :)

Thursday, June 26, 2008

Access 2003 .MDB front-end with Access 2007 .ACCDB back-end

Now that Access 2007 uses a better encryption system for securing your data (provided you upgrade to RC4-128 as my last post suggests), as developers you might wonder if it's possible to use an Access 2007 file as a back-end to an Access 2003 front-end application.

Well it is possible, but you have to use late-binding forms (or completely unbound forms).

Read more: Using an Access 2007 .accdb back-end database in an Access 2000+ front-end application

Monday, June 23, 2008

Expression Web ~ The New FrontPage

I FINALLY got around to updating my 10 year old gr-fx.com website using Microsoft Express Web. This site acts as my company website rather than vb123.com which is my information website. Anyway the interface in Expression Web is pretty good (note that there are about 5 other expression tools) and if you are familiar with FrontPage, this is a no brainer and a worthwhile upgrade (backup your site first).

The photo page from the MVP 2008 summit may interest some readers.

Garry Robinson

Friday, June 20, 2008

SQL Server 2005 Express ~ Getting Started


What I always find the most incomprehensible thing about SQL Server is that it is so hard to actually know what to do to get started. Take Windows Vista, click on the Start button and its likely that you will see the SQL Server Configuration Manager in the recently used program list. Now as this is the only SQL server program that is obvious, you would expect that you could create a database and start doing things. Well you cannot, its simply a more fancy version of the old SQL Server Service manager for SQL 2000. It starts and stops the SQL program and does a few other administration tasks. So stop there, dont worry about it.


Type in "sql server management studio express" in the internet search engine and download that product. Install it. Now you will know the name of your Server, be able to create databases and all other things that you expect in a database backend.

Excel to Access

Over the last 13 years of Access consultancy, 20% of our work has involved converting Excel mega (muddled) systems into Microsoft Access and in lots of cases then uses those Excel spreadsheets for reports. Here are new videos from Microsoft on getting Excel in Access 2007

Tuesday, June 17, 2008

Excel Automation and Windows Vista

If you find that you have the following error

Automation error
Library not registered.
-2147319779

When trying to run automation code in Access 2003 and Vista, you need to change the automation code to late binding from early binding.

ie This means remove the reference to Excel

Dim objXLApp As Excel.Application

and change it to

Dim objXLApp As Object

Read more
http://www.granite.ab.ca/access/latebinding.htm

Garry Robinson


Tuesday, June 10, 2008

Brisbane, Sydney, Melbourne ~ Meet Garry


Send me an email to book a half price 90 minute meeting in the CBD of Sydney or Melbourne. Let me know what you want to chat about and definitely bring your database in for the discussion.



Topic Ideas: Your database and things like upgrade to sql server readiness, upgrade to Access 2007 or simply one of my recent online papers.

Garry Robinson

Monday, June 09, 2008

Compacting Has Been Reported to Delete Access 2007 ACCDB files


The Microsoft Access team has reported instances where internal Compact and Repair will delete one of the new ACCDB format files. They have also said that you can ring support to get the fix for this problem. Search the internet for "KB article 950812" for more detail.

Well first of all don't panic about this, if you are not using Access 2007, it doesn't matter to you. If you are using using Access 2007, then make sure that you have good regular backups and also head off and recover a database from those backups just to be sure (bit like eating your greens).

For all of those readers who are Access Workbench users, make sure that the ACCDB databases that you compact regularly are saved as a favorite with the backup date set to 1 day. See the picture.

And be assured that when the next service pack rolls along, this will all be fixed and if an easy patch comes along before then, I will tell you about it in my newsletters or one of the many Access MVPs will surely mention it in our maxout newsletter.

Garry Robinson
Sydney Access MVP

P.S. We are making adjustments to the workbench to make sure that you understand this issue when doing ACCDB compacting

Thursday, June 05, 2008

Access 2007 Encryption

Prior to Access 2007, security of passwords (database and at workgroup user level) has been a joke. Fortunately for us developers, Access 2007 is a big step in the right direction. Even more so if you follow my guide here to tighten up your encryption settings even further.

http://www.everythingaccess.com/encrypt

It's true that the 'one password for all' scenario isn't perfect for everyone, but it's all we've got now that ULS doesn't exist in A2007 format files. In some situations it's just fine, and most of all it's pretty darn secure as long as you use a strong password and follow my guide.

Wayne Phillips
EverythingAccess.com

Garry has now written a covering article on the topic here

Thursday, May 22, 2008

Video: Find Out About Using The Windows Taskbar on the side

See why its better to place the taskbar on the side in Windows XP (In my humble opinion). Ok so I was just trying to make a video :)

Friday, May 16, 2008

Feel Like Getting Access Fit ~ Read on Aussies



The activity that I have enjoyed the most over the last 12 years is working with other Access developers. On many occasions I have sat down with a developer and gone over the many things that they would like to do to improve both their business and their Access software. After an hour or two the developer has gone home with a few tricks and sensible strategies to keep their software development focused. I personally love these meetings because it gives me insight into how people really use Microsoft Access and this helps my writing and my business.

So now I have decided that I want to see if there are more Australian Access developers of any skill level that would like to work with me to improve their skills. To achieve this, I considered running formal Access training classes ("now children here is a form") or having a lecture hall full of people (no that's not personable enough). After thinking about this for a while, I thought, why not be like a fitness trainer: Introduce new routines, guide people and let everybody in a small group improve together.

So if you live in Australia, send me an e-mail and tell me your name, what city you live in, tell me what you would like to learn, and whether you would be interested in one of these ideas

a) Getting together at your office or meeting me in the city with only people from your company

b) Getting together with two or three other Access people in the central business district for an hour or two

c) Getting together with 5 to 10 Access people in the central business district for an hour talk and discussion among your peers (sort of like a user group)

Please note that I would like to talk about something thought-provoking before discussing your databases and issues. Also note that my intention is to charge everyone who attends these meetings (unless you also give a talk).

So all you Aussies, send in your emails to my website for my email address and come and get fit.

Garry Robinson
Australian Access MVP

Wednesday, May 07, 2008

How Would You Create A Ribbon for Access 2007



In this article, Anthony Moore shows you how you would use Visual Studio 2007 to craft a ribbon that you could modify for Microsoft Access 2007.




Read the word document here

Tuesday, April 08, 2008

A Positive Spin on Access 2007

PC World has a positive spin on Access 2007 here.

My own view on Access 2007 is that if you have Access 2000 or less, its time to start thinking about it. Access 2002 is still the best version and Access 2003 was OK except for the mess with Macro Security.

Monday, April 07, 2008

New Version Of The Workbench for Microsoft Access



In the latest version of my popular Access Workbench tool, you can now make sense of those obscure computer names that occur at large networks. The Workbench does this by allowing you to add notes for those computer names and then displays them in the database Users Tab. Notes you might add would be Name of person using the computer or email address or phone number. Read More

Also in the latest version, the delivery button will create a zip file with dates in it for databases that don't use the Workbench version system and when you purchase the software, the bonus download sample databases have been simplified for easier installation of the much requested Shutdown and Messaging forms. Find and download the Workbench Here

Garry Robinson
Microsoft Access MVP

Friday, April 04, 2008

Ken Getz Needs Respect and Discusses XML in VB

There is no one on the planet that commands my respect more than Ken Getz but he still feels inferior to c# programmers. Anyway here is an article on cool Linq support in vb.net and it mentions Access where Ken cut his teeth.


Here is the article

Jet Engine Vulnerability - Word Opens Jet

This article describes a security issue that means a jet engine patch is heading our way soon.

http://technet.microsoft.com/en-us/magazine/cc434695.aspx

Thursday, April 03, 2008

Maquarie Island Penguins


My son is doing a project on Macquarie Island, thought you might like to see a picture of hundreds of penguins to break up your day.

Here is the link to the real picture..

Thursday, March 20, 2008

Garry's New Writing Challenge

This year I have decided to take on the challenge of writing an article a month for Database Journal, a prestige offering from Internet.com. If you want to see a picture of my ugly noggin (head), go to this page for my biography. Note that I will keep you fully upto date with whatever I write by telling you where the articles are in my newsletters.

Also a special thanks must go out to my old Smart Access co-writer Danny Lesandrini who has written countless articles for Database Journal for many years and now is having some time off.

Garry

Friday, March 14, 2008

Access and related tools good reading links

I have published a page of useful internet links such as access backups, commentary on FrontPage, ensuring you have the right form size

Access and related tools good reading links

Tuesday, March 11, 2008

Access Team Blog : Pushing Access data to the web: Integrating with Office Live Small Business

Here is a link that gives you a good idea about how you might some tables on the web and interact with Access 2007. You do it using Office Live Small Buisness

Access Team Blog : Pushing Access data to the web: Integrating with Office Live Small Business

You can keep up with all the posts at the Microsoft Access blog by subscribing to the MaxOut newsletter

Saturday, March 08, 2008

Access Team Blog : SQL Server Data Services

This is a surprising announcement from Microsoft, they are getting into the online databases space just like Amazons SimpleDb through a new variant of SQL Services. Read more


Access Team Blog : SQL Server Data Services

Wednesday, March 05, 2008

Reports And Forms - Get Control Of Those Controls

Ever tried to make all the text boxes on a form the same width. What happens when there are lots of other objects on the form at the same time.

Tip: Hold down the Shift Key and click each object individually (or in groups) and you can select them from random positions. Now you can make them all the same size at the same time using the sizing commands.

Tuesday, March 04, 2008

Bill Gates vision for Office 14

Read about Bill's vision for Office, in my humble opinion it seems less like a vision and more like a strategy to marginalize Google in the online Office aps space. Decide for yourself.

Friday, February 29, 2008

Single, Double Comparison Accuracy

Understanding problems with single precision and double precision can get you unstuck. Here is description of the problem all be it from a vb.net perspective.

Visual Studio Magazine Online | On VB: Maximize Comparison Accuracy

Personnaly the biggest problem in Access with single precision fields occurs when you use Sum or Avg in group by queries. The results generally come up with a figure that has some weird significant figures. The solution in this case involves changing the field from single precision to double precision. This will increase the table size by 4bytes*no_of_rows which can prove significant in large tables.

Garry Robinson

Monday, February 25, 2008

Check out Garry's new blog

I have now started a new blog on web 2 software at the following location
http://thicktoast.blogspot.com/


Even easier sign up for the newsletter (it probably will only come out every other month)

Friday, February 22, 2008

FTP From An Access Database

I recently came across a good looking FTP solution written by Danny Lesandrini and Dev Ashish. If getting files on and off the web is what you are after, this will help. Just a word of warning, this article uses the Shell command and large companies may have turned off "Shell"ing of applications like FTP altogether.


Read the article

Garry

Friday, February 15, 2008

Good looking forms (in less that 100 words)

I was asked how to make a better looking Access form

Generally the format these days is lighter (no bolds) and only buttons have raised the raised effect. No sunken controls. No underlined text unless it is a hyperlink

Fonts seem to look best like this (these occur on all machines that run Access)

MS Sans Serif for the field boxes and combos.
Verdana for the captions and labels but only if the form has a lot of space.
Arial if the form is pressed for space
Tahoma if space is really tight (also applies to boxes).

Tuesday, February 12, 2008

New Internet Programming Language?

There could be a new internet Programming language coming to Microsoft. Not sure I totally get it, why don't you have a read.

Thursday, January 31, 2008

Click Here For Chapter

Want to find out a little bit about Access 2007

Martin Reid has a free book chapter telling you about what is new in Access 2007. Not a bad way to read about this topic. Better still is to find a newish PC (that doesn't matter if the old version of Access gets trashed) and install a new version of Access 2007 and upgrade your favourite database. Anyway this book chapter reads and prints well. Given that service pack one is out, there should be no more excuses about not trying Access 2007.

Click here for Access 2007 Book by Martin Reid

Thursday, January 24, 2008

Steve Schapel: Real World Access Story No. 30

Steve Schapel from NZ has joined the MVP's that are contributing to my new MaxOut Access newsletter. Here is a sample article from a huge series of Access Real World stories at Steves Blog. In this article you will learn how Access is used to generate XML feeds for posting UK Stamp Duty information.

Join Our MaxOut Weekly Newsletter Here

Thursday, January 17, 2008

Closing Down Databases With The Workbench

Here is one of the questions and answers from the Workbench FAQ page.

We purchased the Workbench at the request of one of our developers.
He thought he had the ability to close open databases, or kick the users off of it, but I don’t see that ability. Am I missing something here?

It is in the Workbench, you need to add a bit of code and a form to each database.
Read MoreHere

You may have the Admin button turned off, it can be turned on in the Options form by pressing the options button. See figure 2 on this page.
Read more here

Tuesday, January 01, 2008

Shift Key

Hi Garry

Any tips on preventing a user from getting to the database window by holding down the SHIFT key when opening a database?

Robert
--

Allow Bypass Key

http://msdn2.microsoft.com/en-us/library/aa200349.aspx

Easy to do in my workbench
http://www.vb123.com/workbench/help/version.htm#Startup

--

Many thanks for such a prompt and helpful response.

Robert