Tuesday, December 13, 2011

vb123.com has been refurbished

vb123.com has been upgraded.  Its got a new look, all the 500 articles from Smart Access content is front and center on the site. Enjoy it readers, its your Christmas present from me.  

Garry Robinson
Microsoft Access MVP - 2006 till now

Wednesday, November 23, 2011

2003 Articles - My Race is now over

For the last two years I have been working on getting the greatest Microsoft Articles magazine, Smart Access online and getting them to you guys as articles. To do that I had to concentrate on just getting the Smart Access articles online and linked and didnt worry too much about whether they were suited to Access 2007 and 2010.  From now on I will concentrate on 2007+ ready content because just about all of our client work happens in these versions of Office.

Before those articles roll off the press, I am going to use the tools in Help and Manual to upgrade the content to HTML5. Hopefully this will not take too long. 

This is what I looked like when I first started programming in Access 2

Out of Sync Cascading Combo Boxes and Subforms

Andy Baron, authors of Access books and who is now working as a Natural Therapist, wrote an article for Smart Access Answers way back in 1998. In this he discusses cascading combo boxes and keeping them in sync and problems you can have with users who fill in the sub-form before the main form.

Having problems handling text that includes quotation marks ?

In this article, Doug Steele describes how you can handle quotations in SQL strings and queries that you may throw at either Access or SQL Server. At the end of the article you may still be confused but at least you will know where to come back to when you next tackle this thorny problem. I personally like the cQuote/Chr$(34) solution, its the easiest to read.

Thursday, November 03, 2011

Cool Tricks for the Sophisticated Developer

Chris Weber addresses questions about report totals, splash screens, trapping keystrokes, managing control widths, when there's more in a memo than meets the eye, triple state check boxes and yes there is more...

Shrink-Wrapped or Do-it-yourself Queries

This article explains how to use multiple queries, both canned and custom-built, to build an application using only one form and a report. This should empower your users to become more productive and efficient by rolling their own queries.

Efficient SQL by Peter Vogel

Peter Vogel looks at some of the general issues around creating SQL queries that run quickly. Read the PDF article here.

Monday, October 10, 2011

# Convert Access to SQL Server and then to .Net #

Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query translation, Then convert to .Net forms and reports in Reporting Services  This is what Andy's MUST upsizer does for you, read about it here

Making your Access Database more Efficient by Peter Vogel

When Peter wrote this column, he said that speeding up a database was his most lucrative consulting project task. Of course he always went for the easy target, too much data was being retrieved. But its not that easy. Have a read...

Sunday, October 09, 2011

Chronology Of Early Days of Apple

Here is early history of Apple. It shows that Steve Jobs and Stephen Wozniak were visionaries long before a graphics monitor came along. 

Just for perspective and because I never purchased a Mac, here is the early history of the IBM PC.  Trust me, the XT was slow and you were forever shuffling floppy disks. The AT was quite usable, we started running quite a bit of mining software on it when it came out. Our IT manager who liked Vax Mini computers at 500k a pop was not too pleased. The users won out in the end, the Vax was then only used for Accounting and the mining guys switched to PC's and Unix boxes for large scale graphics processing. This was late in the 1980's. At this stage I started programming with Informix 4GL, a programming language not unlike Visual Basic for Applications inside MS Access.

Wednesday, September 28, 2011

Making History by Doug Den Hoed

Doug Den Hoed is back with another new technology invention. This time, he introduces his technique to capture multiple on demand history time slices of an application, allowing you to instantly flip back in time and view data from  back then. 

Saturday, September 24, 2011

Matching Data for Analysis

In this article, Rickard Olsson shows how to compare rows in SQL by loading the desired data into two tables for easy comparison. In fact, he shows two different methods and tries to figure out which method will give the best performance.  Read the pdf article

Microsoft Access Forms—All Class

If object-oriented development seems foreign to you, it shouldn't. All forms are defined in class modules, and all executing forms are objects. Garry Robinson shows how to take advantage of this to create classy forms.

Read Garrys article here

Saturday, September 17, 2011

Have I Thrown In the Towel

A user asked this great question

I’ve been in MS Office dev for quite a while…Access, Excel, Outlook….
Still, I learned a lot from your vb123.com website. 
That being said, I saw nothing about Access 2010.

Have you “thrown in the towel” and stopped working in Access ? 
There’s very little work in the USA in Access right now.  That’s why I ask.

My reply..
First I havent posted much on Access 2010 for a number of reasons but the principle one is that I am still working through the backlog of Smart Access articles and posting them to http://www.vb123.com/  There are a few hundred online now.  I keep thinking about cute little things that I have learnt about Access 2010 and 2007 but when I compare that to putting all those well editied and thoughtout articles in the Smart Access collection, I just go back to reformatting and indexing Smart Access, its far more worthwhile for everyone.

As for work, I have had quite a few really interesting projects in Access in the last few months. Australia, thanks to the Chinese led mining boom is still pretty busy though there are signs that things are slowing.  My work collegues and I are good at mining and manufacting software so we still seem to be kicking goals for now.  Access still seems to come up trumps as a clean and powerful interface that works well with Office (read Excel) documents.

If I had to hazard a guess as to why Access has disappeared off the radar for this reader, its probably because external Office programming projects are probably one of the easiest for IT departments to dump to save money. That seemed to be the case for us in 2009 when things fell off the cliff.  Our work dropped to 25% in two weeks. My recommendation when work drys up is work harder than you have ever worked before at getting the work back. Don't take a break until the big break comes along.  Try everything to market yourself well and learn as much as you can about the things that improve your prospects.

Monday, September 12, 2011

Optional parameters in SQL Server stored procedures

Russell Sinclair talks about optional parameters in SQL Server stored procedures, and how you can use them to do advanced searching of data. Read article

Thursday, September 08, 2011

Access Developer Needs a Date

Doug Steele looks at problems with handling dates - like inputting regional dates and guiding you to the reliable VBA date functions  Read the article here

Wednesday, September 07, 2011

Ribbon Vs Apple - A study of the UI

If you have a beef about the ribbon and think "I wonder if the mac is truly easier", this article gives a decent side by side comparision of the photo editing tools in both environments. Its actually a good way of comparing the old drop down menus verses the ribbon. I think the other standout difference between macs and windows is right click context sensitive menus verses Nothing [Where is the right click button on a Mac?]

Wednesday, August 31, 2011

Lucky me

I was lucky enough to see this fabulous parrot when walking in a old gold mining town called Hillend in NSW, Australia.  It is called a King Parrot.  It had a partner that was more green but still stunning.  I had my camera but missed my moment. So I found this picture on the net.

Windows Explorer in Windows 8 - Wrapped in a Ribbon

Explorer 8 has a ribbon.  Here is an article and a picture.  Ribbons: You love em or ya hate em.  Me: Dont care really, they are OK but the person who invented the HD shaped screen sure didnt have ribbons in mind when they decided wide was good.

Tuesday, August 23, 2011

Spatial Progamming in VBA

Many moons ago, I graduated with a Post Graduate degree in Land Surveying. In those days, HP made the most fantastic calculators with reverse polish logic and brilliantly responsive buttons, computers cost $500,000 and were less powerful than an Ipod and I wrote my first program using cards that I marked up with a pencil (there was no keyboard). When I returned to university to undertake a Masters degree, a program appeared on the scene that would draw three dimensional pictures if you managed to arrange the information that the program liked to read. That was my thesis, organising and displaying three dimensional data on $50,000 plotters and even taking pictures of screens to make into a movie. I learned that programming drawings and managing spatial data was very tricky.

Which brings me to an early series of articles by David Saville on programming spatial data. If you do nothing else with this article, have a look at the code relating to the user data
Type LineXY. 

Sunday, August 21, 2011

The 3 Principle Rules of Normalisation - Or was that 2 or was it 5 - Whatever !

Peter Vogel wrote some fantastic editorials over the decade that he was editor of Smart Access. Here is one of those ...

IF memory servers me correctly (and I believe it does), great database administrators have a saying that goes, “Normalize, normalize, normalize.” By which they mean that you should apply at least the first three rules of normalization to your database design in order to get the best design.

“Iron Chef” references aside, there’s a lot of truth to this rule, and it’s one that I believe in very much. However, like any other rule, you must recognize when it applies and when it doesn’t apply.

Please don’t misunderstand what I’m saying here: Full normalization is essential to high-performance database applications. I often find myself fighting to keep developers from “de-normalizing” their databases. My typical situation is arguing against designs that combine separate entities into a single table or repeat data from one table in another. Often, this drive for de-normalization is triggered by a concern that the application will run too slowly because of the number of tables generated by the normalization process. Just as often, this concern is raised before any testing has been done to determine whether there’s a performance problem. Even if testing shows a performance problem, de-normalization is often done before developers examine their code or question whether they’re following the best practices for developing against a relational database. More often than not, I’ve found that performance issues are related to insufficient knowledge of SQL or an insufficiently normalized design rather than one that’s been “over-normalized.”

De-normalization gives you exactly one benefit: a database optimized for specific activities and, as a result, suboptimized for every other activity. Hardly a desirable goal.

So please, please, please don’t use this editorial to justify de-normalizing a database. Though, considering the matter, why you’d think that my opinion would change anyone’s mind is a mystery to me.

The reason that I bring this up is that I was working with a client who was setting up a home-grown data warehouse using Access. Having been thoroughly indoctrinated in the rules of normalization, the DBA was intent on fully normalizing the data warehouse. The purpose of the data warehouse was to support end-user reporting (again, using Access), and some users had already begun to work with the information in the warehouse. Well, at least the users were trying to work with the data. My client was finding a lot of confusion among the users around the many tables they’d have to join together in order to get the result that they wanted. One user even complained that the reason his report took so long to run was because of the joins between the tables underlying the report. I suggested that any report that extracted tens of thousands of records to generate four summary totals was going to take awhile to run regardless of how many tables were being processed.

To reduce confusion among the users, the database administrator had started to create a set of queries that joined together tables in a way to support the typical queries. My suggestion was that the company shouldn’t bother with the queries. Instead, when loading the data into the data warehouse, they should store the data in de-normalized tables. I suggested that the only normalization rule that should be enforced is the first one: no repeating fields.

From the look on the DBA’s face you’d have thought I was suggesting that we de-throne the Queen (remember, I’m up in Canada). My point was that the higher rules of normalization (rule two and higher) were designed to prevent problems during updates, something that doesn’t happen in a data warehouse. The closest that this data warehouse got to an update was when new data was added during the nightly update.

Having abandoned one of the iron rules in my life as a database designer, I got a little giddy. My next recommendation was that we duplicate enormous quantities of data among tables. The benefits in simplifying end-user reporting would be significant and, since we had no updates, we needn’t be concerned about different copies of the data getting “out of sync” with each other. The only cost would be the extra disk space. With the cost of disk space so low, it was hard to get concerned about this cost.

We could, of course, have made data reporting easier by using queries that drew data from separate tables to create the illusion of repeated data without actually repeating the data in the base tables. However, using a query does impose a performance burden (however small) on a report. By actually duplicating the data, we were trading off disk space against CPU cycles. Since it was far easier for my client to add disk space than to create more CPU cycles, we duplicated the data. Since most of the end-user reporting was done during peak business hours when computer time was in short supply, our solution made even more sense.

Who knows where this de-evolution in my standards will end? By next month, I may be using GoTos in my code. And that report that took so long to run? It took just as long against the “de-normalized” tables.

Subform Performance and ADO

Peter Vogel discusses setting the RecordSource property of a subform dynamically to improve an application’s performance. He also answers some thorny questions about using ADO to update a view (you can’t).  Read Access Answers

Saturday, August 13, 2011

Access on a File Server - Whats the performance story

In 2002 one of the readers asked the Smart Access newsletter editor, Mike Gunderloy the following question..

 I’ve always been under the impression that when you place an Access MDB on a server and then run a query against a table, all the processing is still done at the client PC. In other words, if I query a 100-record table to get one record, all 100 records go across the LAN, and then the SQL is processed by Jet on my machine. This was always explained to me as a drawback of Access being a file-based database product. I’m wondering if this is still true. If not, was this correct in the past? Can you give me a definitive answer?  Mike answers the question here in this Smart Access pdf article

Monday, August 08, 2011

Demystifying Joins

Russell Sinclair explains the mysteries of the JOIN statement in SQL and how to use it with both Jet and SQL Server  Read more

Tuesday, July 26, 2011

List Boxes, Slow Forms, Access Bugs, and More

In this jumbo-sized edition of Access Answers you’ll find out about a subtle Access list box bug "row source is too long", forms that open slower every day, some more obvious bugs, and how to avoid using edit masks (and why you’d want to).  Read more in the PDF article here

Monday, July 25, 2011

Top values and Other Ken Getz Hints from the Good Ol' Days

In 1996, Ken took on three topics: modifying the Top Values property of a query, selecting items from a hierarchical list on a form, and why two equivalent strings really aren't.  Read the article here

Garry's Notes "I thought it was interesting to see that Top Values was being supported back in 1996 and its still offered as a major feature in the 2010 Pivot table Ribbon."

Sunday, July 10, 2011

Peter Vogel on record locking in multi user environment - 2 PDF articles

Creating an effective Update statement in SQL can make all the difference in what data gets saved in a multi-user environment and what sort of performance you’ll get from your application. Peter Vogel discusses record locking and SQL statements.  Read the first article

Peter Vogel examined the issues around locking records and concluded that, most of the time, you dont want to lock your records. In this article, he reviews that discussion and then shows the code that you can use to avoid record locking. Read the second article here

User Preferences, Toggles, and Rocket Science

Maybe it isn't rocket science, but maintaining user preferences will make your applications more appealing. Follow along as Danny Lesandrini demonstrates how you can maintain a variety of user-defined options.  Read the article

Sunday, June 26, 2011

Hide Your Input, and it’s Pretty Common

Doug Steele looks at how you can program your own InputBox, as well as how to programmatically call a couple of the standard Windows dialogs (the Color and Font dialogs),  Read the article here

Tuesday, June 21, 2011

Error Trapping with a Code Builder

If you code for a living, you know that error trapping is a drag. But its also an essential part of any serious Access development. This article is a heavy read but it will give you an idea about how to manipulate lots of code using code rather than cutting and pasting.  Read the article here

Peter Vogel's take on writing easy to read code and error handling

Peter Vogel takes a quick look at two topics that will help you create code that can be maintained: error handling and program documentation.  Here is the article in PDF format

Sunday, June 19, 2011

Dates, Data Access, and Presentation

Peter Vogel looks at a problem in managing dates and displaying information using conditional formatting. He starts with a solution to the problem, but uses that as a springboard to discuss what processing should be done in the different parts of your application.  Read the pdf article here

Tuesday, June 14, 2011

Navigation Through Recursion: Part 2

In this article, Christopher Weber demonstrates recursive query and reporting techniques he uses to generate a tree navigation map of the database. Along the way, he builds a reusable module that uses Access graphics to draw hierarchies in reports.  Read the article here

Navigation Through Recursion: Part 1

Christopher Weber takes us through a navigation map generating algorithm he uses to populate a table that describes how the forms and reports in an Access database relate to each other. This is a precursor to discussions on recursion theory in part 2 of this article..   Read the first article here

Reporting from Two Tables with Common Data

This month, Peter Vogel looks at reporting from two tables where one table overrides entries in the other table.

Here is the problem: I have a master table of data that I want to report on. However, I also have a table of daily data. Where there’s a matching record in the daily table for a record in the master table, I want to use the daily table’s record.  Read the article for the solution.

Blog On Your Mobile

If you want to read this blog on your mobile, use this address

Sunday, June 05, 2011

Is Dropbox useful for an Access Developer / Office Power User

Introduction: For quite a while now I had been contemplating a very popular specialised online tool called DropBox for one of our important projects.  Finally I have taken the plunge and I like what I see. This article explains how I think it is useful for developers and users of MS Access and Office.
Figure 1
Online Backup:  Dropbox is a secure file syncing product that moves the latest version of your file to the Internet as soon as it is saved on your hard disk (see figure 1). It does this (as far as I can see) as follows. When the file is saved / created, the online DropBox database is updated. Then DropBox goes about moving the files. You have the ability to pause the movement of the files and you can also make it move the files at a slow bit rate. DropBox will also slow down the movement of the files if it senses other Internet activity going on. Once the files are on the Internet, you can browse the files using a Web Interface.  You can store upto 100 gigabytes on the Internet as a single user.  There is a corporate plan as well that is far more extensive.
Figure 2
Two Computers: Probably the most appropriate thing for developers is the fact that you can use the same DropBox account on two computers and have files synced between the two computers plus have the files saved on the web (as in figure 2). This only works on files that are located in a folder tree that has a folder named DropBox at the top.  You cannot rename DropBox folder but all the other folders are yours to arrange as you will. To show you how smart this is, consider that you have a powerful desktop for most of your development and a lightweight laptop for field visits and occasional work. When both machines are turned on and you are logged in to an account on one of those computers, DropBox recognises when the Local Area Network is available and synchronises the computers quickly using that.  This will allow you to start developing on the beast (desktop) rather than the dainty little laptop. The visual clues that DropBox provides makes this pretty seamless. Remember that working on two computers on an Access database or a Excel spreadsheet in this environment relies on one thing. The SYNCING BETWEEN THE COMPUTERS MUST BE COMPLETE otherwise you are working on the old version.  The LAN syncing makes this quite practical and the Internet sync in this case is your backup and can progress in the background.  This functionality is a very good reason for having DropBox.  

Folders: DropBox will happily handle folders and subfolders and if you want to get smart you can either stop the syncing of a folder or cut and paste the folder back into your normal environment when you are finished with a project.

Inviting Another Person: In your DropBox folder, you can right click on a folder and invite a friend to share that folder. DropBox generates a special link which is sent in an email to that person. When they accept the invitation and install DropBox on their computer, that folder will end up in their DropBox folder. From then on if either of you change, delete or add a file, hey presto, that change moves to your computer and your folder on the web. You can later remove that person from sharing the folder at a later date.

If You Both Change The Same File At Once:  I believe that I am just about smart enough not to change a file if I have already changed it on another computer and am waiting for it to sync. I am not smart enough to know when someone else is modifying a file at the same time as me. So to my simple mind, you need simple rules to make this environment work. One rule that may be easy to manage is that one person does 95% of the work on the files and you simply have the files on your computer for help when they need it. At this stage you would notify them that you are going to change the files. The risk of this is reduced as DropBox maintains all the modified files online for a month. It also makes it easy to find these files as shown in Figure 3. This could be appropriate for database prototyping.

Figure 3
Practical Implications For Access:  When you open a database in MS Access, DropBox thinks that you have changed the file even if nothing actually is changed. As a result the file is then synced to the web and any other shared computers. This clearly is only OK with smaller files and only OK when everyone understands the rules for opening the file. This is why you have to think this through carefully. Another consequence of opening the file is that a laccdb or a ldb file is created in the folder to manage object sharing in Access. This file is rapidly transmitted across the internet or LAN to other computers. If you are smart enough, this can act as a visual cue that the database is in use as in Figure 4.  I would not grapple with multiple person file sharing until everyone really understood what the issues are and can communicate file ownership rules effectively.

Transferring Files Using Public Folders:Dropbox automatically sets up a folder called Public. If you place a file in this folder, DropBox can generate a hyperlink to that file on the internet.  You can then add that hyperlink to an email and send it to someone to download the file. This link is open to anyone but the link is encrypted so you would need to have a copy of the email to download the file.  As soon as you delete the file from your local Public folder, the file is deleted from the web. This approach will reduce Inbox / Sent email bloat by 90%.

Why Not Try It Out:  To get started follow this link to dropbox.com and download the installation file or look at the video's that explain the product.  By following this link, you will receive some bonus free shared space. I recommend that you start DropBox gradually until you fully understand how it will benefit you and your associates. It is possible that you may only end up using DropBox for sharing photos and doing some backups.   I am sure that there are other products out there that do a similar job but this one is quick, simple, smart and well worth thinking about.  In a future edition of this newsletter, I will outline how we are using DropBox.

Garry Robinson

See Comments by Glen Lloyd"The other caution is that folder sharers are able to delete files, so when it matters, copy files to those folders, rather than move them there."

Wednesday, May 25, 2011

Managing Cursors, Quotes, Subforms, and Missing Data

Christopher Weber looks at four problems: handling missing data in reports, customizing the cursor, variable sized subforms, and managing quotes.
Read the article in pdf format here

Tuesday, May 24, 2011

Choosing and Working with Option Boxes

Access provides three different ways for users to select among multiple choices: check boxes, option buttons, and toggles— and then there are option groups. Peter Vogel looks at what you can (and can’t) do with these tools.

Here is Peter Vogels article on MS Access Option Boxes

Did someone say holiday ?

Doug Steele shows how to automate the creation of table of holiday dates for use in your databases.
Read the first part of this article here and the actual article on holidays here.

Consolidating Your Data With Queries

This was my second ever article for Smart Access back in 1998. Nothing much has changed when it comes to group by queries, if you want to be a good Access programmer, you need to know all the tricks that you can do with these queries.

Microsoft Access's Query/SQL environment, along with its support of functions and complex expressions, makes Access a powerful tool for consolidating data. Access's ability to work with many different database formats also gives you the ability to explore data in many different data sources. As I discovered, there's more to consolidating data than clicking the Totals button in the Query Design window, and I'm going to share that information with you in this article.

Tuesday, May 17, 2011

Access 2010 - VBA Book

My good Scottish friend Andrew Couch has written a book on Access 2010 VBA
Go to Amazon to read about the book and pre-order

Ordering Controls, Fixing Bugs, and Speeding Up Remote Databases

Chris Weber returns to his list of most asked questions to address some pressing issues for Access developers. This PDF article discusses managing your control’s tab order, provides an easy solution for an annoying Access bug, and shows an easy way to improve performance when retrieving large recordsets from remote computers.  Click here to read the article

PDF: Outputting Flexible Data

Producing static reports is easy—but what if your users want to be able to customize their output? Dave Gannon and Nich Mann look at all the options available to you and let you in on the best answer.  Click here to read the MS Access article

Its not the backup system that counts - its the recovery

Two weeks ago, my Access 2003 laptop bit the dust, stopped working, kaput.  Doesn't matter how good your backup systems are its always tense when you go hunting for those files.  In my case the main backups were created using Norton's 360 on a Terabyte drive and I had recovered files from there a few times so I wasn't too worried. But when I looked at the files from another PC using Norton's they weren't there.  Norton's packs everything into an encrypted /file and Norton's from the other machines only showed the backups from that specific machine.  An hour of madly typing in a Logmein session with a Nortons engineer at 3 in the morning and I finally worked out the trick that allowed me to recover the files from a different PC.  It wasn't obvious in the help file etc   You know the story... After the Easter weekend a Chinese guy at the local PC shop did a disk mirror and I got my older laptop working again so I was back to normal. 

Moral of the story.  Never rely on just having a backup system, its much more important to rely on testing the backup system with recoveries.  Irrespective of what your backup system is, delete an unimportant file and then recover it.  Once I tried this with a big company and they sheepishly said they couldn't, all their backup tapes were empty.

"Its the recovery that counts "  Try it now..  Not next week  Now.   
Garry Robinson - Access MVP 2006-2011

Tuesday, April 19, 2011

Switching Access Versions

In the last newsletter, I discussed downsizing  problems Access 2010 to 2007 after finding out that the client was using Access 2007.  My golden rule for this now (after wasting a few hours) is to develop in 2007 all the time for clients that have 2007. Whilst I still prefer Access 2010, I will switch versions as required using my Access Workbench 10 product.  Here is a page that discusses switching with Workbench 10

Monday, April 18, 2011

We do what we do but is it enough ?

Every 6 months or so you should ask yourself, am I heading in the right direction with my job or am I just going where I want to go. If you are a software consultant, your clients will be your barometer. If your job mix consists of supporting lots of clients doing lots of different short jobs, changes will be gradual. If your job mix is one of 3 month contracts, a down period could easily drag on from a few weeks and turn into a few months.

My rule on managing this is simple, if you are making lots of dough, don't push extra hard, if you are not making much dough, work/train/learn/market yourself real hard till the money comes back. No paid work is not the time for golf and holidays, part timers generally become more part time than they want to be.

Peter Vogel discusses this in an editorial entitled  Is Programming Enough? back in 1997. 

Note: I loved reading Peter's editorial every month back in the good old days, I hope I can get you interested in the ones that are still relevant today in this newsletter.  Garry

Saturday, April 16, 2011

The First Ever Edition of Smart Access - August 1993

In August of 1993, the first ever edition of Smart Access was written. This newsletter featured articles on Developing with Microsoft Access, the Reddick naming convention, ASCII text reporting and Soundex for more flexible text comparisons. Download and read it here Writers in this edition included Helen Feddema, Paul Litwin, Stan Leszysnky, Dave Browning and Gred Reddick

Wednesday, April 13, 2011

Facebook or Tweet or Forward Newsletter

Everywhere in the monster Smart Access inspired website, you will find the following F Share button at the bottom of each page. Click on this and you will share the page with your Facebook Pals. 

On my Blog which is where this newsletter comes from now, you will see twitter and facebook and RSS links.

In the newsletter you will see links to forward the newsletter to friends.  If you can all do this once, vb123.com will become more popular, a little more money will flow in and I will be able to pay for more organised content.  

Thanks  if you can do this, Garry Robinson, Editor of vb123.com

PDF: Simplifying Complex SQL by Peter Vogel

Access developers often find SQL bewildering: While simple things are easy in SQL, as you move up to more complicated problems, SQL statements can quickly become intimidating. Peter Vogel looks at some strategies for solving tough problems with SQL.

Read Peter's PDF article on queries

Tuesday, April 05, 2011

Backward Compatibility from Access 2010 to Access 2007

If you are creating an Access database using 2010 but your audience requires a solution that works in Access 2007, the best way to do this is to use Access 2007. Of course you will be like me this week and create the database using 2010 and add features that are not 2007 compatible. If you do that you will get a warning "Unrecognised database format".  That will excite you. So what have you possibly done wrong and what is the solution

You have added the Navigation Control
You have added a Data Macro
You have added a Calculated Column in a table
You have added a Web Browser control

This compatibility topic is discussed at Microsoft here

Friday, April 01, 2011

PDF: Flexible Normalisation and Denormalisation Helen's Way

Helen Feddema writes "A reader asked me how he could convert a table with more than 100 questionnaire fields to a more manageable format, with the fields converted to records in a table to make it easier to tabulate the data. Effectively, this is the reverse of Garry’s problem where the converted multiple records into one  Read the article in the pdf document here

Thursday, March 31, 2011

Adding A Data Macro To Automatically Fill in a Field

In the following table, I have used a Access 2010 data macro to fill in field. In this case I wanted the PassFail field to equal "Pass" only if all three questions were answered yes as in picture 1

If any of the question fields were No, then it would be a fail as below

I did this with the following data macro

So now how did I learn how to do this, I watched this video from Microsoft

Saturday, March 26, 2011

Video: Photo Renaming and Display using Microsoft Access

Here is a sample video of photo management software that we have created using Access for a number of mining companies. This video shows the software in action and doesn't include any vba code. In this particular sample, the photo is moved to a sub folder and name that corresponds with the primary keys of the records that the photo relates to. The photo is not stored in the database. The video goes for 5 minutes and is narrated by Garry Robinson.

Wednesday, March 23, 2011

The Second Ever Edition of Smart Access - September 1993

In September of 1993, the second Ever edition of Smart Access was written. This newsletter featured articles on customising report properties, windows class libraries, recursion management and documentation of Access databases using the FMS Total Access product we still sell today. Download the 2nd edition of Smart Access here.

  Please tell your friends about this newsletter.

Tuesday, March 22, 2011

Solid State Drives and Office

Woody's Office Watch discuss the speed implications of Solid State Drives and Microsoft Office, as I mentioned in my article on Apple MacBook Pro, I purchased a sony laptop machine with Solid State drives.  Also note that in the Apple article, 2 readers have commented that they have been very happy with the Mac Pro for development. Its seems to me that a hybrid machine with SSD drives and ordinary drives will be very popular in the future.

Sunday, March 20, 2011

Peter on quotes, specific record selection, hot key code and first monday

Peter Vogel looks at the following situations in an article he wrote for Access 95 that looks pretty handy for Access today:

How to handle quotes in code and when using the Dlookup function Read more

How to display the three or four records before the record a person wants and then set focus on the record the user asked for Read more

Create a shortcut key to run a function without putting the code under a button Read more

How to determine the last Friday and the first Monday of a month Read more

The Smart Access Related Topics Page

Smart Access Index Page
Over the last two years that I have been compiling the Smart Access articles into a comprehensive website, here is the primary page where I have been organising the articles.

The topics on this page include
Project Management & Data Modelling
List Boxes and Combo Boxes
Controls, check & text boxes, buttons etc
Forms and Menus
Sub Forms
MS Word & MS Excel
SQL Server etc
Access Data Projects
Protection and Security
Pivots and Charts
Managing Quality, Bugs and Errors
Imports, Exports and XML
User Interface and Documentation
Access Answers
Dates and Time

Real Time from Google

With so many unfortunate disasters happening around the world, you may think of searching for news that is current rather than the news that the TV stations want to give you. If you are into twitter and maybe facebook, you may stumble across the news in your inbox. Better though is go to the RealTime feed at Google and type in a search term.

Google Real Time in Action - "you don't need a Twitter account"

P.S My own twitter posts turn up in less than a minute into this service and they are not mass media topics

Friday, March 18, 2011

Microsoft - Millions of Reasons Why They Are Still The Force

Here are some interesting stats.  Kinect for the Xbox is the fastest growing electronic gadget to the 10 million mark.  Windows 7 has many more sales than Ipads/macs to the tune of 14 to one last time I looked. 2 million downloads of IE 9 in the first day.  Watch for this note on Windows 64 Bit IE9.  IE9 definitely loaded one of my complex web pages a lot faster.   BTW: The separate search box in IE9 is actually the windows address box. If you press Ctrl E keys, the search box opens instantly and you can start typing the search terms.  Also press the F11 key and IE9 looks sensational. Good ol' Microsoft, right clicks and hot keys, you dont get that in Ipad world.

Caveat: IE9 doesnt work so well on some websites (logmein is my bugbear) so I am downloading a different browser for those. I have used multiple browsers with multiple home pages for years anyway so no big deal

Here is a picture of IE9 with the old menu
Don't forget the hidden achievement of Microsoft in the last 2-3 years, switching from 32 Bit computing to 64 bit computing so that we can start to use more than 3.2 gigabytes of memory on our laptops and desktops. Something that probably was as hard in engineering terms as building and launching a satellite that takes photos of the earth.

Saturday, March 12, 2011

The Third Ever Edition Of Smart Access - October 1993

As I mentioned in dispatches, I would release scanned copies of the very early Smart Access magazines to those people who signed up for the new aweber.com version of the newsletter.

In October, 1993, Paul Litwin was the editor and Ken Getz was already writing lots of articles.

Garry Robinson ~ Access MVP for 5 years

Friday, March 11, 2011

Invisible Forms Part 2: Park Your Global Values Here

Tobi has discovered that adding an invisible form to your Access application can make a number of difficult tasks much easier.   Read more here

PDF: Citrix - tracking users and hidden forms

Migrating a project into the Citrix platform provided a workable environment for Tobi Hoffman’s company’s people nationwide to access a single database, yet presented some unexpected challenges. Since Citrix forces all users into a single application, keeping track of users’ individual security clearances was almost impossible—until the application got a form that no one saw.  Read the PDF here

vb123 Knowledge Base - How It Has Grown

A couple of years ago I purchased the rights to publish and sell what was one of the best Microsoft Access knowledge bases, Smart Access. The other good resources were Access Advisor which morphed into a VB/ASP/Access magazine and Inside Access, a lesser know but reasonable magazine.

So when I purchased that and put 100 or so articles online, I was left with a decision, switch to writing Access 2007/2010 articles or continue to find and make articles available from the collection. As it is time consuming to write more than 2 good new articles a month, I decided it was best to keep putting the Smart Access online as I could do this at a much faster pace.  There are now hundreds of articles online at http://www.vb123.com/kb and a large number of them I haven't mentioned in my newsletter yet.

These articles appear in two formats now.  html pages that are linked and properly indexed and available in the online search pane and PDF files that are only linked and really can only be found in Google or Bing.  In the last 6 months I put 150 PDF's online as these were quicker to publish than the HTML format. In this newsletter, you will see articles from Tobi Hoffman about Citrix and Invisible Forms, one in PDF and one in HTML format. That should demonstrate the formats.
The catch: The only way to get the Smart Access articles in their entirety is to purchase them.  Many of the readers have already done this, thanks, that simply encourages me to do more work online. The other catch is that the download databases that accompany most of the articles must be purchased.

Tuesday, March 08, 2011

Saturday, March 05, 2011

Getting an Apple - Is it a good idea?

A reader asks

May I ask a few minutes of your time to offer an opinion re Apple computers?
I’m a long term Access developer, should really be retired; had PCs since the early 80’s but fed up with their vulnerability to outside threats and the deterioration in their performance once they have been in use for a year. I need a laptop and I usually buy the best going with the largest screen on offer.
The primary use I put my computer to is the development of Access applications; still with 2007.

Do you think an Apple Mac would provide a better long term platform for my work?

Every year or two I generally waste a lot of time in January and February (not so busy then) puzzling about whether I should upgrade my laptop or desktop. This year it was my laptop. After some thought I realised that I probably wasted a bit of time in the last 4 years with laptops that were cheaper and the hardware didn't last all that long.  So this year I vowed to spend a little more money.

My goal every time I buy a new machine is more and more about ergonomics. This time round I did spend quite a bit of time in the Apple store because an Apple Mac Book Pro wins big on that score. In case you didn't know, Macs come with bootcamp and also an internal virtual system that can Windows 7 and XP if you want.  So I started to dream of software like garage band for my sons and other neat programs that run on a mac that I have never used.  In the end I didnt go for a Mac Book Pro for the following reasons.

I liked the 13 inch MacB Pro but it was as slow as my previous computer in terms of hardware. The 15inch MacB Pro felt really neat but it too was under specified. One thing that really irritated me was that the place where your wrists sat was quite hot. This also seemed a problem with a number of Windows machines. Disk space at 300-500gbytes also seemed a little light on as there would have to be two operating systems on the machine. I did find out along the way that some specialist firms would add extra memory and disk but by then the price was going up and up.   One thing that was apparent was that Mac's had good batteries.  Unfortunately a lot of the speed of a mac is due to the fact that the hardware is optimised for the Mac software as there is only one version of hardware to cope with.  Windows has to cope with all sorts of configurations and hence hardware/software cannot be thus optimised. But if you are running Windows on a Mac, a lot of this advantage disapears.  I didn't look at a 17 inch MBPro as I don't like walking around with a piano on my back.

In the end I went for a Sony total ultra light beast of a laptop with really fast solid state memory disk drives. This had the compromise of not much disk space but as a Windows laptop allows for more peripherals than a mac, I will be find other places to store all the files that I don't use very much. 

In the end I kept thinking of my business, have I every made any money from someone using a Mac and the answer is no. Maybe if they bring out Access for Mac, then that may change. That said, I see programs like LogmeIn running on an IPAD as being a very good way to run Access and Office software on a remote machine.  This is an interesting area, contact me if you agree

Other favourable mentions were HP Envy and Dell machines, and watch for Samsung, their next machine could really rock as they own the sold state drive market.

Garry Robinson - Access programmer from way back then till now.

Sunday, February 27, 2011

The New Access Unlimited Newsletter

People who are reading this are the early adopters of the new Access Unlimited newsletter. This letter is being driven by a company called AWeber and the content is derived automatically from my blog at vb123.blogspot.com. The way this works is that every few hours Aweber looks at the RSS feed from blogspot and if something is new then it adds this to the content of the newsletter.  Then every 2 weeks (my current setting) all the new articles in that period are sent in the newsletter. In other words, I produce my blog, you get a newsletter.  Please tell your programming friends about Access Unlimited.

Garry Robinson

Tuesday, February 22, 2011

Steve Jobs - A Truly Remarkable Documentary

This video goes for 45 minutes, it explains all about Steve Jobs and it is well worth watching. The bit that I really liked was the reminder that whilst Steve Jobs was in the wilderness, he founded and almost spent all of his money on two companies "Pixar" and "NEXT". Pixar was an astounding company IMHO and NEXT was taken over for 400 million dollars by Apple because they wanted the Operating System. Since then the successes have flowed at an astounding rate.

But before you think I am going all mushy about Apple, they are still a company who basically produces consumer products and their products still cost too much.

At the moment, the best Apple purchase for Microsoft focused people is the Apple ITouch, its an iPhone without the phone and it works the same on a wireless network.  See the video

Wednesday, February 02, 2011

Installing Office(s)

Article on installing different versions of Office together on one machine.