vb123

Ipads - Good for ordering chinese food

2012-01-29T20:56:00.000+11:00

Back in the 90s, many of the engineers and geologists in the mining company that I worked for hated mainframes and loved pcs. We had good word processing and spreadsheets on the mainframe so this wasnt rational thought in the view of the IT department that I worked for. Nevertheless, we eventually gave in and let them have their PCs and the users went from hating us to loving us. They also started to mention that they like the mainframe (it was actually a Vax Mini computer or 2) a lot more than they used to. I suspect this phenomenon is just starting to occur between Tablets and PCs.

This article from september last year discusses this topic and as us PC developers are now the new IT department to tablet users, we need to get a handle on what is happening with our tablet users. Eventually when tablet users have had their fun, we can pull them back to less fragmented solutions.

The sun maybe getting ready for a cold phase

2012-01-24T19:48:00.001+11:00

One of my geology buddies, Ian Levy, found the rather cold Australian summer was getting him down. So he wrote a paper on global temperature trends. Unfortunately for all those global meltdown enthusiasts, he put together a thesis that I concur with, Man does effect global temperatures but there are much bigger forces at play.
Ian writes "The soon‐to‐be released mean global surface temperature results for calendar year 2011 will be more important than most years for those who believe that the sun dominates our climate to such an extent that human‐induced carbon dioxide is only a minor influence on our global climate." Read the full article called 2011 – A Watershed Year for Global Temperature Trends? here

Moving Files Using DropBox And Processing Them Later

2012-01-23T10:08:00.000+11:00

This article shows how I move files to a remote pc using Dropbox and then rename them on the remote server using Logmein and an Acccess database. Its mainly a pictorial but it only involves files and folders and no Internet uploads and downloads. So I like it. Here is the article

Saving All Tables To Comma Delimited Text Files

2012-01-23T10:07:00.000+11:00

Before I started programming using Access 2, I was involved with a database of geological data that cost $50 million to put together. Another company bought the project, did nothing with the data for five years, and in the end couldn't read the backup tapes. When we were asked to help, we managed to recover the text data backups from our tapes, and these were used to build a reasonable copy of the database. We also recovered the database files from tapes, but the format was proprietary, and the software that could read the proprietary format was long gone. This is why I occasionally export database tables to text files. I dont trust files that that cannot be read with a text editor.

This story shows you how to export all the tables in an Access database, linked or standard Access format, to text files that you can read with Excel or a text editor. It then shows you how to read them back into a blank MS Access database.

Save All Objects To Text

2012-01-22T13:58:00.000+11:00

The article from last months newsletter on Save all Objects to Text had a wrong link to the article (and another one that worked). Here is where the article is at vb123.com

Windows Excel, Word, Powerpoint on an Ipad2

2012-01-20T10:26:00.001+11:00

This ZDnet article shows how Onlive Desktop runs on an Ipad. There is a discussion on using Dropbox as well, a topic that I will cover here soon

Dynamic Access 2007 and 2010 Reports - Get more from your database

2012-01-10T11:21:00.000+11:00

This article explains the dynamic report view that came with Access 2007 and shows how you can add hyperlinks to your reports to open other reports and forms. The article is written for managers as it is not a complex topic. There is also a section on how to highlight data on your report using conditional formatting using colour in a special way.

See how hyperlinks and colour can be added to reports

Backup and recover queries, forms, reports and modules to/from text

2012-01-05T15:32:00.002+11:00

A Microsoft Access database is a complex object that is subjected to much stress in network environments and by programming. As a result you need backups of your work. In this article I will show you the

Application.SaveAsText and Application.LoadFromText methods

to save and then recover queries, forms, reports, macros and modules. To do this you will export all objects to text files in a sub folder using code that I will provide you. Then you copy and paste some vba code that will be autogenerated and use that to populate an empty database with all your newly exported objects.

This whole process can also help you diagnose and sometimes help you recover an already corrupted database.

It will take you less than a quarter of an hour once you get the hang of it. Read the article here

A Happy New Year to all of you.

Garry Robinson
MS Access MVP 2006 - 2012

vb123.com has been refurbished

2011-12-13T08:46:00.000+11:00

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

Microsoft Office On the Ipad

2011-12-01T10:37:00.003+11:00

Office at 10$ on the IPad, read this article on zdnet.

Austrian Economics

2011-11-23T16:07:00.000+11:00

Since my business was smashed in 2009 for 6 months, I have spent quite a lot of time trying to understand the beast that is the new world of economics and investment. My summary is this.

Keynesian spending, the economics of most governments of the modern world is totally flawed. The way the world ran for 300 years before Keynes came along was under a different model called Austrian economics. I am not an economist so I commend that you search for the following "Peter Schiff" to understand the fundamentals, "Franklin Sanders - Gold" to understand the charts, "Ed Steer - Gold" to get a handle on the latest behind the scene stories. If you want to make a difference in the USA, research Ron Paul, he has written a number of books on Austrian economics. Sorry to bang on about this but its as likely the world will be poorer into the future than richer and I dont want my friends to get their pockets pinched by over zealous governments interested in re-election. Garry Robinson - Editor of vb123.com

2003 Articles - My Race is now over

2011-11-23T15:49:00.002+11:00

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

2011-11-23T15:27:00.000+11:00

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 ?

2011-11-23T14:58:00.001+11:00

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.

Lighter than a feather

2011-11-21T08:48:00.000+11:00

Australian scientists have invented a material that is so light that it takes 10 seconds to float to the ground from shoulder hight. Interestingly it is made from nickel and at the moment they don't know what it will be used for.

Love Your Ipad

2011-11-08T09:28:00.001+11:00

Here is an article on why I love my Ipad and because I have an Ipad, here are my thoughts.

The I stands for Individual / Island / Isolatated

The Ipad will empower your reading of online content and it will give you access to many apps that you never thought you wanted but now do for a while. But it wont do much for the Access programmer apart from give them a look at the dark side.

For me if I had a choice now, I would buy a lightweight powerful computer with 4hrs battery life for taking around and a 6inch Amazon black and white Kindle for easy reading and 15 day+ battery life. I plug my lightweight laptop into a decent screen and keyboard when in the office.

The Ipad has shown the way, its now upto Apples competitors to finally get a good tablet out there and allow us more choices than heavy laptops with low battery life.

Garry Robinson

PS. You cannot right click on a iPad, that leaves most Apps with fewer context sensitive options.

Cool Tricks for the Sophisticated Developer

2011-11-03T07:32:00.000+11:00

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

2011-11-03T07:26:00.001+11:00

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

2011-11-03T07:23:00.002+11:00

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

Microsoft Profits Up Again

2011-10-24T18:21:00.002+11:00

Here is an article discussing Microsoft's latest quarterly results. Office and Sharepoint still flying, Kinect and XBox exploding and Windows and Windows Live doing very nicely. Nothing to worry about there folks.

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

2011-10-10T22:17:00.002+11:00

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

UK Access User Group National Seminar

2011-10-10T16:58:00.000+11:00

The next UK Access User Group National Seminar will be help on the 10th November 2011 at the Heritage Motore Centre, Banbury Road, Gaydon, Warwickshire, CV35 0BJ: full details can be found at: www.ukaug.co.uk

Making your Access Database more Efficient by Peter Vogel

2011-10-10T08:13:00.001+11:00

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...

Chronology Of Early Days of Apple

2011-10-09T19:06:00.000+11:00

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.

Making History by Doug Den Hoed

2011-09-28T20:59:00.001+10:00

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.

Matching Data for Analysis

2011-09-24T22:37:00.001+10:00

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

2011-09-24T19:07:00.002+10:00

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

Windows 8 - Its going to be different

2011-09-20T10:38:00.000+10:00

Windows 8 has some dramatic UI changes - here is is good page discussing the topic

Have I Thrown In the Towel

2011-09-17T21:28:00.003+10:00

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.

Golden Facts

2011-09-12T23:01:00.000+10:00

I follow the gold price and gold stories and here are tips if you think you need to get into gold. The first one is read Franklin Sanders blog, he occasionally sounds a little nutty but I have read his blog every day for 2 years, he is not. I believe he is one of the worlds great long term chartists and he calls it as the charts tell him. He is also a very funny writer (us Aussies are a little warped). The second tip is in all the great gold booms over the centuries, the gold price divided by the silver price trends to a ratio of 15-20 to 1 at the top of the boom. At the moment it is 45 to 1. It was 80 to 1 in 2003. So buy some silver as well. The third tip from other Gold opinion leaders like Peter Schiff is that Gold and Silver mining shares at the moment are 30% down on their usual ratio to the gold price. In other words, they are probably one good place to get into gold. Good luck what ever you decide. Garry

Optional parameters in SQL Server stored procedures

2011-09-12T22:56:00.001+10:00

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

Access Developer Needs a Date

2011-09-08T09:42:00.001+10:00

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

Ribbon Vs Apple - A study of the UI

2011-09-07T07:44:00.000+10:00

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?]

Lucky me

2011-08-31T16:16:00.000+10:00

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

2011-08-31T12:43:00.000+10:00

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.

Spatial Progamming in VBA

2011-08-23T19:20:00.001+10:00

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.

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

2011-08-21T22:49:00.001+10:00

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

2011-08-21T22:37:00.001+10:00

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

Access on a File Server - Whats the performance story

2011-08-13T21:17:00.002+10:00

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

Demystifying Joins

2011-08-08T21:13:00.001+10:00

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

List Boxes, Slow Forms, Access Bugs, and More

2011-07-26T09:05:00.001+10:00

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

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

2011-07-25T20:35:00.001+10:00

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."

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

2011-07-10T20:28:00.001+10:00

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

2011-07-10T18:12:00.001+10:00

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

Hide Your Input, and it’s Pretty Common

2011-06-26T16:41:00.001+10:00

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

Danny at DBJournal discusses Special Access 10 Data Types

2011-06-22T21:23:00.000+10:00

Special data types in Microsoft Access 2010 are well worth looking at if you're sure you won't be upsizing to another database engine in the future. Read Danny Lesandrini's article at Database Journal

Error Trapping with a Code Builder

2011-06-21T09:37:00.001+10:00

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

2011-06-21T09:32:00.002+10:00

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

Dates, Data Access, and Presentation

2011-06-19T22:14:00.001+10:00

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

Google ChromeBook - At Least this is something Microsoft Doesnt Have to Worry About

2011-06-17T08:39:00.000+10:00

A review about the new Google Chromebook PC thingy - a problem searching for a customer

Navigation Through Recursion: Part 2

2011-06-14T09:57:00.001+10:00

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

2011-06-14T09:53:00.002+10:00

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

2011-06-14T07:44:00.001+10:00

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

2011-06-14T07:35:00.000+10:00

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

Is Dropbox useful for an Access Developer / Office Power User

2011-06-05T20:55:00.008+10:00

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."

Managing Cursors, Quotes, Subforms, and Missing Data

2011-05-25T21:05:00.000+10:00

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

Choosing and Working with Option Boxes

2011-05-24T10:09:00.000+10:00

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 ?

2011-05-24T09:46:00.000+10:00

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

2011-05-24T09:26:00.000+10:00

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.

As always with www.vb123.com/kb There are other links at the bottom of the article to similar topics.

Cool Thing In IE 9

2011-05-18T08:24:00.001+10:00

If you click on a tab in Internet explorer 9 and then drag it, it pops up in a new window. See the great effect in the picture.

Access 2010 - VBA Book

2011-05-17T22:44:00.003+10:00

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

2011-05-17T22:27:00.001+10:00

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

2011-05-17T22:18:00.002+10:00

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

2011-05-17T22:05:00.001+10:00

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

Switching Access Versions

2011-04-19T11:46:00.000+10:00

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

We do what we do but is it enough ?

2011-04-18T23:11:00.001+10:00

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

The First Ever Edition of Smart Access - August 1993

2011-04-16T13:34:00.000+10:00

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

Facebook or Tweet or Forward Newsletter

2011-04-13T22:11:00.001+10:00

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.
http://vb123.blogspot.com/

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

2011-04-13T22:01:00.000+10:00

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

Backward Compatibility from Access 2010 to Access 2007

2011-04-05T11:08:00.002+10:00

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

PDF: Flexible Normalisation and Denormalisation Helen's Way

2011-04-01T08:48:00.001+11:00

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

Adding A Data Macro To Automatically Fill in a Field

2011-03-31T15:09:00.000+11:00

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

Video: Photo Renaming and Display using Microsoft Access

2011-03-26T20:54:00.000+11:00

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.

The Second Ever Edition of Smart Access - September 1993

2011-03-23T01:14:00.001+11:00

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.

Solid State Drives and Office

2011-03-22T07:30:00.001+11:00

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.

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

2011-03-20T22:58:00.001+11:00

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

2011-03-20T13:15:00.003+11:00

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
Tables
List Boxes and Combo Boxes
Controls, check & text boxes, buttons etc
Queries
Forms and Menus
Sub Forms
Reporting
MS Word & MS Excel
Outlook
Performance
Editorials
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
Reviews
Access Answers
Dates and Time
VBA, DAO, ADO

Real Time from Google

2011-03-20T07:51:00.001+11:00

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

Microsoft - Millions of Reasons Why They Are Still The Force

2011-03-18T21:35:00.006+11:00

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.

The Third Ever Edition Of Smart Access - October 1993

2011-03-12T09:43:00.004+11:00

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.

Please tell your friends about this newsletter.

Garry Robinson ~ Access MVP for 5 years

Invisible Forms Part 2: Park Your Global Values Here

2011-03-11T11:20:00.002+11:00

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

2011-03-11T11:14:00.000+11:00

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

2011-03-11T10:38:00.003+11:00

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.

Fire Sheep

2011-03-08T19:06:00.000+11:00

Beware as the WiFi snoopers now comes in sheeps clothing. If you use public networks, watch out as this very popular program can easily grab your login details

Getting an Apple - Is it a good idea?

2011-03-05T22:00:00.003+11:00

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.

The New Access Unlimited Newsletter

2011-02-27T08:34:00.000+11:00

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

Steve Jobs - A Truly Remarkable Documentary

2011-02-22T23:40:00.001+11:00

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

Installing Office(s)

2011-02-02T07:18:00.000+11:00

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

Protecting Your Application when Upgrading to Access 2010 from Access 2003

2010-10-19T09:17:00.002+11:00

Here is a question and answer from the UK Access Users Group
I am not a huge fan of Switchboard Manager in Access but it does save a bit of form coding and does serve a purpose. Anyway, I have one in an Access 2003 application which works fine.

Today, I opened the 2003 application in Access 2010 for the first time.

Our company it moving to 2010 early next year so I am playing. I was quite expecting to have to upgrade the application to 2010 or at least, recompile it but I didn't seem to need to.

Once I got around the Security Macros stuff all was fine but a strange thing happened with the Switchboard Manager form.

The application opens with a Home (Startup) form and that looked fine with just the ribbon visible at the top (not sure about the ribbon being visible for a deployed [shipped] application). A button is available on the Home form that opens the Switchboard Manager. When the SB Mgr form opened, the left hand navigation pane was suddenly visible with access to all the database objects - tables, forms, queries, modules etc.

Has anyone experienced this or have any ideas about stopping the Navigation pane suddenly appearing? I don't want Users poking around in the queries etc.

Thanks, Andy.

A. from Bob Cresswell

I hide the ribbon
DoCmd.ShowToolbar "Ribbon", acToolbarNo

and disable the Bypass Key on Startup (as I am Macro-intolerant these statements are in my Splash form which is the start up form in the application).

SetProperties "AllowByPassKey", dbBoolean, False

The Navigation Pane is hidden using the Access Options.

For authorised users, I have an option on the Main Menu (or Switchboard) that gives them options to show the ribbon, navigation pane and enable the Bypass key (close database and restart with shift required for this).

I have three check boxes on the form, and an Apply button has this code:

' Show/Hide Ribbon
If Me!chkShowRibbon Then
DoCmd.ShowToolbar "Ribbon", acToolbarYes
Else
DoCmd.ShowToolbar "Ribbon", acToolbarNo
End If

' Enable/Disable Bypass Key

SetProperties "AllowBypassKey", dbBoolean, Me!chkBypassKey

' Show/Hide Navigation Pane

If Me!chkShowNavigationPane Then
Application.SetOption "Show Navigation Pane Search Bar", True
DoCmd.SelectObject acTable, , True

Else
DoCmd.SelectObject acTable, "tCATVersion", True
DoCmd.RunCommand acCmdWindowHide
End If

Access Unlimited Newsletter October

2010-10-08T08:50:00.000+11:00

Includes these topics

Data Macros in Access 2010

Over at Database Journal, Danny Lesandrini writes a practical introduction in Access 2010 data macros. This covers "the AfterUpdate event of the table and figuring out how to debug the macro."

I've Got Plenty of Nothing

Doug Steele starts by looking at a technique for finding unused fields in tables

Handling Price Ranges in Microsoft Access

Rebecca Riordan provides two methods for handling the common case of multiple prices for a given product, depending on the quantity ordered–the customer pays $1.79 each for buying up to nine items, but only $1.69 each when buying 10-14 items, and so on.

Embedded quotes in SQL statements - DAO Code

Adrian Murphy pointed out to Doug Steele that Parameter queries are another approach to solving the problem of having embedded quotes in the values being used in SQL statements. He sent along the following code sample:

Advanced Data Shaping - Using Hierarchial Recordsets

Mike Gunderloy provides some examples of ADO's SHAPE_APPEND statement in action, including how to synchronize child and parent Recordsets. Mike also introduces the SHAPE_COMPUTE statement.

Doug Steele shows how to simulate Cue Banners in Access

Internet controls such as search boxes have something called "cue prompting" that can help indicate what the user should be entering. Is there some way I can still do something like this?

Creating Paired Listbox Controls, Part 2

Rebecca Riordan continues her examination of paired Listbox controls by adding two additional functions: deferring data updates until users explicitly commit their changes, and restoring the contents of the paired Listbox controls to their initial state.

# Convert Access to SQL Server #

Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query translation and web form conversion. MUST now supports Access 2010.

PivotTables in Access

Russell Sinclair shows how to summarize data using PivotTables

An Access E-mail Application

Adding an e-mail facility to your Access application is a convenient way to communicate critical data to multiple users, while at the same time producing a trail linked to key records in your database. Keith Bombard shows you how.

From Zoom Box to Custom Dialog

For some applications you just can't get enough screen real-estate, especially when you have fields that display a large amount of text. Mike Toole describes an alternative to the Zoom box that not only looks and works better but avoids the Zoom box's spurious updates. His design can be used for creating any sophisticated custom dialog.

Manage your Update Tables with Query Lists

In this article, Keith Bombard shows off a routine that can reduce the time it takes to create a form to manage your update tables. This general-purpose routine can be used with any small table for editing, updating, adding, and deleting records.

Making the grade

Doug Steele looks at a common problem in database design: converting from one data classification schema to another. He then moves on to a related question: ensuring that there are no overlapping records in a list of ranges (for example, a list of scheduled events). This results in some thorny SQL, but the results can be used in a wide variety of circumstances.

Transfer All Tables from One Database To Another

This page is updated to show how I transferred from a database to another database (It was actually a SQL back to an Access ACCDB format) using a third database that was linked to both. There was no relationships in the target database so order of transfer wasn't important.

http://www.vb123.com.au/toolbox/news/issue201010.htm

Global Error Handler v2 now released!

2010-09-03T00:42:00.003+10:00

Previously known under the name of "SimplyVBA Global Error Handler", we've just released version 2 of the product, now called vbWatchdog.

Designed specifically for VBA, the product offers a robust mechanism for catching and logging errors that occur in your VBA code on a global scale.

The main features are;

- No DLLs required - vbWatchdog is coded neatly inside your VBA environment
- Identify the source procedure and module name where the error occurred
- List the exact line number to identify the line of code that failed
- Report the full callstack that lead up to the exception
- List values of variables within each procedure on the callstack
- Offers a Try-Catch paradigm for simplifying local error handling
- Prevents the Access Runtime from closing on unhandled errors
- Includes a customizable HTML based error dialog at your fingertips
- Includes a detailed online manual

Why not take a look at the Sample.MDB to see what you're missing out on...

More Information

Wayne Phillips

Outlook Exchange Online

2010-08-20T03:24:00.000+10:00

I recently purchased a HTC HD2 (a good) phone running windows mobile 6.5. More importantly I upgraded to Outlook Exchange Online edition. Now my phone syncs thru the web and not thru the PC. This is fantastic. If your pc is on and downloads emails or contacts or tasks, it doesn't disapear off the web. If you change anything on the phone or the pc, it is immediately available on either of the 3 devices. And then there is the online version of Outlook which is truly the most feature rich web program that I have ever used by a long way and it behaves almost the same as Outlook on the desktop.

Google vs Bing Images

2010-08-09T19:35:00.000+10:00

Have a look at how google images has changed to look like Bing. There are now selections down the LHS for country and image size just like Bing had a while back. Now google images are laid out in a really cool way that seemed to copy the Bing image display. Search for "fox cartoon" and select Image to see what I mean.

Workbench 10 is coming

2010-07-30T19:52:00.000+10:00

A new version of the popular workbench for Access 2010, 2007, 2003, 2002 is coming.

Rounded Corners

2010-07-28T18:55:00.000+10:00

Just picked up a old database and all the buttons were square and looked old (see picture below). Use the Windows Themed Contols option in Access Options and you will instantly have Rounded buttons.

Setting Up A Trial Access 2010 Installation

2010-07-16T13:58:00.002+10:00

You have an existing version of Microsoft Access and want to try out Access 2010. Well why not download the trial of Office Professional 2010 (600mb) onto one of your least important computers. Once you have done that and made sure you kept your trial registration number from Microsoft, you simply want to make sure that you DON"T REPLACE your older version of Access & Office. For the file location, select a different folder than the one you currently use for Office.

So follow these pictures and only install Access (not Word or Excel or Notes etc).

I have followed this system for 16 years without problems. I use the Access Workbench www.vb123.com/workbench to switch versions ( you will find both versions under the Microsoft Office menu in All Programs )

NOTE: Only edit software and objects in databases that are not needed in earlier versions.

Garry

Ipad Contenders in Windows Space

2010-06-29T10:52:00.000+10:00

Toshiba has a dual screen computer, bottom (or is it right side) is a glass keyboard

See picture

Australian Office Developers Conference

2010-06-28T09:48:00.000+10:00

Office DevCon is back in November.

Office DevCon is the largest and most popular annual community-driven conference that allows Microsoft Office developers and power users to come together in one location to hear expert speakers present on a wide range of Microsoft Office-related topics.

2010 will be our fourth year, and we are planning to build on the unprecedented success of the first three years.

Come along and discover how to do things you never knew you could do with the Microsoft Office suite of products.

14 Times Faster is that sad old Microsoft

2010-06-25T07:51:00.004+10:00

Microsoft Windows 7 is selling at a rate of 7 per second. Thats fourteen times faster than ipads. I think I would be buying Microsoft shares rather than Apple. Click to go to article

Its a sad state of affairs

2010-05-27T08:46:00.000+10:00

On this day Apple overtook Microsoft as the most valuable software company by stock market value. If apple had sold its original mac and earlier products at PC prices all those years ago, we would have all been mac gurus. Alas whilst the world believes that a music device with a white cord, a pc that doesnt do much for a business and a phone that has 100,000 mostly distracting downloads is important, this state of affairs will remain. Meanwhile China is building a power station a week, a city of a million people people a month, builds railroads at speeds of upto 5 km a week and has factories that make everything, its time for the white cord listeners to get back to work and innovate to make America the great country that it once was.