Similarities between Access Databases and Projects

Similarities between Access Databases and Projects

About indexes

Article About indexing fields and records in an Access database (MDB) About indexes (ADP)

About keys

Article About primary keys (MDB) About keys (ADP)

About queries

Article About types of queries (MDB) About select and crosstab queries (MDB) About action queries (MDB) About creating queries (ADP) ##Side:Start;Align:left##  Tip …

About table relationships

Article About relationships in an Access database (MDB) About table relationships (ADP)

About tables

Article About tables (MDB) About tables (ADP)

Add columns to a query

Article Add or remove a field in the design grid (MDB) Add columns to a query (ADP)

Backup and restore an Access file

Article Back up and restore an Access database (MDB) Backup and restore a database or Access project (ADP)

Create a join in a query

Article Enable or disable automatic joins in queries (MDB) Join tables and queries in a query (MDB) Create a join between tables on multiple columns in a query…

Create a relationship between tables

Article Define relationships between tables (MDB) Create a relationship between tables (ADP)

Create an append query

Article Create an append query (MDB) Create an append query (ADP) ##Side:Start;Align:left##  Tip     Access 2010 includes improved tools and layout that help create…

Create an index

Article Index one or more table fields in Access 2003 by using the table designer.

Create an input mask

Article Create an input mask (MDB) Create an input mask (ADP)

Create an update query

Article Create an update query (MDB) Create an update query (ADP)

Delete a join in a query

Article Delete a join line in a query (MDB) Remove a join in a query (ADP)

Delete a relationship

Article Delete a relationship (MDB) Delete a relationship (ADP)

Delete an index

Article Delete a table index in Access 2003 by using the table designer.

Modify a join in a query

Article Set or change the join type (MDB) Change the type of join in a query (MDB) Modify a join operator in a query (ADP)

Run a query

Article Run a query (MDB) Execute a query in the Query Designer (ADP)

Total records in a query

Article Total records in a query (MDB) Count rows in a query (ADP) ##Side:Start;Align:left##  Tip     In Access 2010, you can calculate data directly in a table…

Troubleshoot queries

Article Troubleshoot queries (MDB) Troubleshoot queries (ADP)

Microsoft Access 2012 Wish-List

I ran into this on the internet.. I think that it’s funny.. because I think that Access Data Projects include all 5 of these features already:

Q: New Feature 1 – Recording Macros
A: I use SQL Profiler for this!!! It works great, especially on large Access conversions!

Q: New Feature 2 – One, better macro language
A: I use TSQL for this! IF WHEN THEN BEGIN WHILE, that’s all done on the SQL Server side, buddy!

Q: New Feature 3 – Get rid of the bugs
A: I stopped using Jet 10 years ago because it’s not stable, it’s not possible to stabilize.

Q: New Feature 4 – Include an SQL Editor
A: Access Data Projects -IS- a SQL Editor!

Q: New Feature 5 – Deter Casual Users
A: Access Data Projects take about an extra week to teach developers to be productive. BFD, It’s worth it.

Microsoft Access 2012 Wish-List – Five Things We’d Like to See in the Next Version of MS Access
By Andy Brown
Dec 21, 2010
Sometimes it’s not easy to see the database wood for the trees. This article attempts to stand back and consider 5 features which Microsoft could include in the next version of Access (2011? 2012?).

New Feature 1 – Recording Macros

Microsoft, the time has come! You can record Visual Basic macros in Word and Excel, and you used to be able to record in PowerPoint too until recently. We recognise that it wouldn’t be an easy feature to include, but it would make writing VBA macros so much easier!

New Feature 2 – One, better macro language

You keep trying to improve the Access macro language, adding better comments and much more in Access 2010. Ultimately, however, it’s Visual Basic which provides the power that most developers need.

We’d like to see Microsoft announce that they will no longer invest in the Access macro language, but will work to improve the VBA language. Specifically, please can we get rid of DoCmd and RunCommand, and also abandon the forms![formname]![controlname] convention? Word and Excel VBA has converged: it’s time to bring Access into the fold.

New Feature 3 – Get rid of the bugs

The worst feature of Access, by far, is that it isn’t reliable. Every time that we use it to develop a system, we swear that we will never do it again (and then forget this promise the next time!).

We’d like to see the Compact and Repair feature removed from Microsoft’s next Access version – because it wasn’t needed.

New Feature 4 – Include an SQL Editor

For those who know SQL Server but – understandably – prefer the Access user-interface, a SQL editing window would be a godsend. If we could have it with colour-coding, indentation, comments and autocompletion of commands, so much the better. Basically, couldn’t Microsoft include the SQL Server Management Studio 2008 window within Access?

New Feature 5 – Deter Casual Users

We’re not entirely serious about this last new feature, but …

We’ve seen far too many companies building Access “databases” which consist of a single table, and which would have been better created in Excel. So, how about a feature which displays a warning message when you create a single-table database (or a multiple-table database with no relationships) saying something like “This database might be better created in Excel!”.

If anyone from Microsoft would like to comment on this article, they’d be very welcome!
About the Author
Andy Brown has been building Access databases and running Microsoft Access training courses for Wise Owl Business Solutions for many years. You can see all of the courses that Wise Owl run at

Sun is Setting on MVC?

I am so glad that I don’t waste my time learning the latest and greatest client-side BULLSHIT.

I still think that most logic deserves to reside on the server-side.
Sorry, but I’ll always feel that way.

Sun is Setting on Rails-style MVC Frameworks

Lately I’ve been thinking a lot about the impact of the move to a thick client architecture for web applications, and I’m becoming more and more certain that this means that Rails-style MVC frameworks on the server-side are going to end up being phased out in favour of leaner and meaner frameworks that better address the new needs of thick-client architecture.

There are a few major reasons for this:
The server is no place for view logic

The devices available for running a web app are vastly different from when these web frameworks first sprung up. The slide towards thicker / richer clients has been proceeding on pace with increases in processing power since the Web 2.0 days. It’s much simpler to handle views and view logic in only one place, and that place is slowly moving away from the server side. MVC has always been a strained pattern for a server-side, non-gui application and it is been a confusing and complicated trade-off to have the backend generating front-end logic. Front-end frameworks like backbone.js, as well as advances in web technologies like HTML5’s history.pushState are now making server-free views a realistic quality of cutting-edge front-ends. Rendering on the client-side also gives us the opportunity to create responsive designs based on device capability rather than having the server try to somehow figure out what the capabilities are without actually running on that device.

The kinks aren’t all the way out yet, but I do think the trend is clear.
Server-side Templating and .to_json are both underpowered and overpowered for the actual requirements of JSON APIs

There’s no need for templating on the serverside (or view helpers, or any view-related cruft) to generate simple JSON documents, but there are a tonne of problems left unsolved when we fail to see that generating a JSON API is more than just a serialization problem.

How should dates look? (RFC339 / ISO8601 of course!). What should the JSON error document look like when you provide a 400 and want to tell the client why? How should links to other resources in the API look? How does a collection look? How does pagination look?

These aren’t just serialization concerns, and they have nothing to do with templating.
HATEOAS is not just an academic pursuit

A thick client does not want to maintain a vast list of static strings representing all the crazy URLs that it will have to call in a non standard API. As an API designer, you don’t want them doing this anyway, because hard-coded URLs and URL structures make it a real pain for you to change your API.

The AtomPub protocol, if you ignore its XMLiness, gets this right — The thick client just knows the root URL (which serves up a simple service document) and is aware of a bunch of relationship types (the ‘rel’ attribute on ‘link’ and ‘a’ tags) that it can follow as necessary. If a game client needs to access a list of players, it just goes to the root url, and follows the link with the rel property called ‘players’ (and probably remembers that link until the server says it has moved via 301 status code). JSON has no concept of links or rel, but this is still easy to imagine and implement, and while it’s a teeny bit of extra work up front, the standardization buys you the ability to start writing smarter HTTP/REST clients for your frontend that take care of much more for you automatically, so you can spend time on real business logic and actually do something more productive than fiddle with the javascript version of a routes.rb file.

(A really great API framework might generate some or all of these links on its own and automatically put them in the JSON documents. It’s pretty easy to imagine pagination links like next/back being generated automatically, or even links amoung resources in some cases, possibly based on some internally declared relationship.)
Rails-style MVC frameworks have a horrible routing solution for RESTFul JSON APIs

In a resource-oriented API, the router need not be concerned with the http methods that are or are not allowed for the resource. That’s the concern of the resource and the resource alone. When the router tries to manage that, you get the unnecessary verbosity of a route for every method supported by the resource, and you get the app incorrectly throwing 404s instead of 405s when a method is not supported. This probably means that ‘controllers’ need to go away in favor of ‘resources’, and routes can be vastly simplified, if not completely derived/inferred by the framework. Because we keep thinking in this conventional MVC style though, we miss the possibility and potential of vastly more simple applications that actually do a lot more more for us.
The Application Developer shouldn’t have to Deal with these Details

There’s no reason for us to all separately think about these problems and solve them in a million different ways every time we’re confronted with them. Aside from the years of wasted time this involves, we’ve also got a bunch of non-standard and sub-standard APIs to interact with, so all the client code needs to be custom as well and nothing is reusable. This is why being RESTful is not just academic and this is why being concerned with the details is not pedantic.

As I said earlier, AtomPub gets a lot of this right. A lighter-weight JSON equivalent would be a huge improvement to what people are doing today, because the conventions would mean that the framework can take care of most of these API details that we reimplement ad nauseum — or worse, not at all. It also means that frontends can start to abstract away more of the HTTP details as well. This is already starting to happen in the new frontend MVC frameworks but in almost every case, the HTTP end of things still needs to be handled in a custom way for every API endpoint. There is still way too much work left to the application developer, and we’re silly to continue to do it over and over without coming up with a better abstraction.

CouchApps and WebMachine are just starting to touch on this style of architecture. Backend-as-a-service platforms like Parse certainly understand how far this simple architecture can go, but ultimately there’s a huge need for a framework that can create more complex RESTful APIs (in a language that’s more general purpose and “blue collar” than Erlang).

Rails-style MVC frameworks are both too much, and not enough at the same time. It really is time for a new framework to support this new architecture.

Don't Distract New Programmers with OOP

Wow.. I finally found someone who makes some sense.

I think that OOP is an example of over-engineering.. I use OOP on the database side ALL THE TIME.
Stored procedure inherits from a couple of views.. THAT is practical OOP.

So so so true that MOST developers don’t understand the point of OOP. It is SUPPOSED to make you more efficent as a programmer, but it generally does NOT.

There’s one caveat to using Python as an introductory programming language: avoid the object-oriented features. You can’t dodge them completely, as fundamental data types have useful methods associated with them, and that’s okay. Just make use of what’s already provided and resist talking about how to create classes, and especially avoid talking about any notions of object-oriented design where every little bit of data has to be wrapped up in a class.

The shift from procedural to OO brings with it a shift from thinking about problems and solutions to thinking about architecture. That’s easy to see just by comparing a procedural Python program with an object-oriented one. The latter is almost always longer, full of extra interface and indentation and annotations. The temptation is to start moving trivial bits of code into classes and adding all these little methods and anticipating methods that aren’t needed yet but might be someday.

When you’re trying to help someone learn how to go from a problem statement to working code, the last thing you want is to get them sidetracked by faux-engineering busywork. Some people are going to run with those scraps of OO knowledge and build crazy class hierarchies and end up not as focused on on what they should be learning. Other people are going to lose interest because there’s a layer of extra nonsense that makes programming even more cumbersome.

At some point, yes, you’ll need to discuss how to create objects in Python, but resist for as long as you can.

How can Microsoft fix their problem with mainstream developers?

Resuming Classic VB as a mainstream product would also mean best PR for them, too. The years ago, there were 6 million “professional” VB6 programmers. All of us, no matter what additional tools,languages and platforms are we using today, still have to maintain and extend VB6 applications, and most of us still hold a grunge against MS for attempting to kill a vital tool of our trade.

Don't rewrite old code 'just becase it's old'

            Joel on Software

Things You Should Never Do, Part I

by Joel Spolsky
Thursday, April 06, 2000

Netscape 6.0 is finally going into its first public beta. There never was a version 5.0. The last major release, version 4.0, was released almost three years ago. Three years is an awfully long time in the Internet world. During this time, Netscape sat by, helplessly, as their market share plummeted.

It’s a bit smarmy of me to criticize them for waiting so long between releases. They didn’t do it on purpose, now, did they?

Well, yes. They did. They did it by making the single worst strategic mistake that any software company can make:

They decided to rewrite the code from scratch.

Netscape wasn’t the first company to make this mistake. Borland made the same mistake when they bought Arago and tried to make it into dBase for Windows, a doomed project that took so long that Microsoft Access ate their lunch, then they made it again in rewriting Quattro Pro from scratch and astonishing people with how few features it had. Microsoft almost made the same mistake, trying to rewrite Word for Windows from scratch in a doomed project called Pyramid which was shut down, thrown away, and swept under the rug. Lucky for Microsoft, they had never stopped working on the old code base, so they had something to ship, making it merely a financial disaster, not a strategic one.

We’re programmers. Programmers are, in their hearts, architects, and the first thing they want to do when they get to a site is to bulldoze the place flat and build something grand. We’re not excited by incremental renovation: tinkering, improving, planting flower beds.

There’s a subtle reason that programmers always want to throw away the code and start over. The reason is that they think the old code is a mess. And here is the interesting observation: they are probably wrong. The reason that they think the old code is a mess is because of a cardinal, fundamental law of programming:

It’s harder to read code than to write it.

This is why code reuse is so hard. This is why everybody on your team has a different function they like to use for splitting strings into arrays of strings. They write their own function because it’s easier and more fun than figuring out how the old function works.

As a corollary of this axiom, you can ask almost any programmer today about the code they are working on. “It’s a big hairy mess,” they will tell you. “I’d like nothing better than to throw it out and start over.”

Why is it a mess?

“Well,” they say, “look at this function. It is two pages long! None of this stuff belongs in there! I don’t know what half of these API calls are for.”

Before Borland’s new spreadsheet for Windows shipped, Philippe Kahn, the colorful founder of Borland, was quoted a lot in the press bragging about how Quattro Pro would be much better than Microsoft Excel, because it was written from scratch. All new source code! As if source code rusted.


The idea that new code is better than old is patently absurd. Old code has been used. It has been tested. Lots of bugs have been found, and they’ve been fixed. There’s nothing wrong with it. It doesn’t acquire bugs just by sitting around on your hard drive. Au contraire, baby! Is software supposed to be like an old Dodge Dart, that rusts just sitting in the garage? Is software like a teddy bear that’s kind of gross if it’s not made out of all new material?


Back to that two page function. Yes, I know, it’s just a simple function to display a window, but it has grown little hairs and stuff on it and nobody knows why. Well, I’ll tell you why: those are bug fixes. One of them fixes that bug that Nancy had when she tried to install the thing on a computer that didn’t have Internet Explorer. Another one fixes that bug that occurs in low memory conditions. Another one fixes that bug that occurred when the file is on a floppy disk and the user yanks out the disk in the middle. That LoadLibrary call is ugly but it makes the code work on old versions of Windows 95.

Each of these bugs took weeks of real-world usage before they were found. The programmer might have spent a couple of days reproducing the bug in the lab and fixing it. If it’s like a lot of bugs, the fix might be one line of code, or it might even be a couple of characters, but a lot of work and time went into those two characters.

When you throw away code and start from scratch, you are throwing away all that knowledge. All those collected bug fixes. Years of programming work.

You are throwing away your market leadership. You are giving a gift of two or three years to your competitors, and believe me, that is a long time in software years.

You are putting yourself in an extremely dangerous position where you will be shipping an old version of the code for several years, completely unable to make any strategic changes or react to new features that the market demands, because you don’t have shippable code. You might as well just close for business for the duration.

You are wasting an outlandish amount of money writing code that already exists.

Is there an alternative? The consensus seems to be that the old Netscape code base was really bad. Well, it might have been bad, but, you know what? It worked pretty darn well on an awful lot of real world computer systems.

When programmers say that their code is a holy mess (as they always do), there are three kinds of things that are wrong with it.

First, there are architectural problems. The code is not factored correctly. The networking code is popping up its own dialog boxes from the middle of nowhere; this should have been handled in the UI code. These problems can be solved, one at a time, by carefully moving code, refactoring, changing interfaces. They can be done by one programmer working carefully and checking in his changes all at once, so that nobody else is disrupted. Even fairly major architectural changes can be done without throwing away the code. On the Juno project we spent several months rearchitecting at one point: just moving things around, cleaning them up, creating base classes that made sense, and creating sharp interfaces between the modules. But we did it carefully, with our existing code base, and we didn’t introduce new bugs or throw away working code.

A second reason programmers think that their code is a mess is that it is inefficient. The rendering code in Netscape was rumored to be slow. But this only affects a small part of the project, which you can optimize or even rewrite. You don’t have to rewrite the whole thing. When optimizing for speed, 1% of the work gets you 99% of the bang.

Third, the code may be doggone ugly. One project I worked on actually had a data type called a FuckedString. Another project had started out using the convention of starting member variables with an underscore, but later switched to the more standard “m_”. So half the functions started with “_” and half with “m_”, which looked ugly. Frankly, this is the kind of thing you solve in five minutes with a macro in Emacs, not by starting from scratch.

It’s important to remember that when you start from scratch there is absolutely no reason to believe that you are going to do a better job than you did the first time. First of all, you probably don’t even have the same programming team that worked on version one, so you don’t actually have “more experience”. You’re just going to make most of the old mistakes again, and introduce some new problems that weren’t in the original version.

The old mantra build one to throw away is dangerous when applied to large scale commercial applications. If you are writing code experimentally, you may want to rip up the function you wrote last week when you think of a better algorithm. That’s fine. You may want to refactor a class to make it easier to use. That’s fine, too. But throwing away the whole program is a dangerous folly, and if Netscape actually had some adult supervision with software industry experience, they might not have shot themselves in the foot so badly.

how to import all the spreadsheets in a folder

I love Access / Visual Basic for importing files.. I just laugh when I talk about doing this same thing in SSIS, it takes MUCH less time to write a simple loop in script than to do it in GUI like SSIS.


Option Compare Database
Option Explicit

Public Sub ImportAllSpreadsheets()
On Error GoTo errHandler

Dim wsh As New FileSystemObject
Dim fld As Folder
Dim fil As File
Dim tblName As String
Dim filPath As String

Set fld = wsh.GetFolder("C:DataABC")

For Each fil In fld.Files
If Right(fil.Name, 4) = ".xls" Then
tblName = Replace(Replace(Replace(Replace(fil.Name, " ", ""), ".xls", ""), "&", ""), "-", "")
filPath = fil.Path

' Stop

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tblName, filPath, True

End If
Next fil

Exit Sub
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
End Sub

how to remove spaces in column names

I use this method for EVERYTHING.. I learned this method from a book called ‘SQL Server 7.0 Secrets’


select 'exec sp_rename ' + char(39) + + '.[' + + + ']' + CHAR(39)
+ ', ' + CHAR(39) + REPLACE(, ' ', '') + CHAR(39) + ', ' + CHAR(39) +
from syscolumns sc
inner join sysobjects so
on =
where so.xtype = 'u'
and like '% %'