Real world Access applications

Steve Schapel, Access MVP, has compiled a list of 12 "real world Access" examples, which will be continuously expanded. Application we have built for A.P. Møller is also mentioned there - Vodka. Well done, Steve!

SQL Prompt

SQL Prompt – is a great addition to MS SQL Server 2000 Query analyzer, now I feel myself there like in VS 2005 because of intellisense and other great features. Most exciting feature – that it is FREE until 1st September 2006!

How to deal with missing references

Doug Steele, Access MVP, has made a very good page Access Reference Problems, covered all aspects of missing references. But still, some people can not find references dialog. I made a short, 2 minutes, video, which shows how to do this: MissingReference (1.1 MB).

This is my first public video of such kind, your comments are welcome!

UserControls, subforms and Events

What I found missing in Access – is UserControls. Fortunately – Access has subforms, and using subform you can do almost the same. But how to notify main form that something was changed in subform? In VB this task is solved with Events. What about Access? Here a sample how we can use Events in Access.

Make a new form, that will act as subform, say frmDateSelector. Declare event in its class module:
Public Event DateSelected(datFrom As Date, datTo As Date)

Add 2 textboxes for date entry, and add following code:
Private Sub txtFrom_AfterUpdate()
RaiseEvent DateSelected(Me.txtFrom, Me.txtTo)
End Sub

Private Sub txtTo_AfterUpdate()
RaiseEvent DateSelected(Me.txtFrom, Me.txtTo)
End Sub

So, once data was changed – we raise DateSelected event. How to catch this in a main form?

In main form class module declare subform object with events:
Private WithEvents ds As Form_frmDateSelector

Initiate it in main form Open event:
Private Sub Form_Open(Cancel As Integer)
Set ds = Me.frmDateSelector.Form
End Sub

That’s all!
Now, like in VB, you can select “ds” in top left combo of main form calss module, select DateSelected in right combo – and Access will insert an event procedure:
Private Sub ds_DateSelected(datFrom As Date, datTo As Date)
End Sub

You just need to modify your form’s rowsource or applyfilter – and that is!

Now you can use your frmDateSelector in other forms, similar to VB UserControl

Why it was named Access?

Tony Toews, Access MVP, has recently pointed to the following blog entry:
Where did the name for Microsoft Access come from?

New records in a query, based on linked table

I am building client-server applications, with Access as FE and SQL Server as BE, already several years, but only today I noticed the following. If you create a query in Access, or open DAO recordset, on a SQL Server linked table – you should have table’s primary key in field list (or just all fields using *) in order to append new records to this query or recordset. No primary key – you cannot add new records, you can only update existing. If you have an Employee table with ID as primary key and Name as text then this query:

Select Name from Employee

Do not allow new records, but this will allow:

Select ID, Name from Employee

A "thank you" from newsgroup poster

Thanks for your work in this area.  I have been successful in implementing my first treeview type control and making it deliver the desired results, primarily because you and a few other have been so kind as to share your knowledge with the rest of us.
Mr B

Such words make me feel great!
:-)

And again on Name AutoCorrect

Got an interesting response on my last post from Tom Wickerath, Access MVP. Now Name AutoCorrect option has adjusted a query SQL, here a thread on this issue.

So, best advice here is SWITCH Name AutoCorrect OFF.

Hey Julie -- I second Tom's opinion!

More on AutoCorrect option

Another bad thing on AutoCorrect (AutoCorrupt :-) option – is once you created a new database – AutoCorrect is switched on for it. I did not find any global option or registry key to change this. Fortunately – you can control it via code, here a procedure to create database with AutoCorrect switched off, which I got from Allen Browne:

Courtesy of Allen Browne
http://allenbrowne.com

Sub CreateDatabaseDAO()
    Dim dbNew As DAO.Database
    Dim prp As DAO.Property
    Dim strFile As String
  
    'Create the new database.
    strFile = "C:\SampleDAO.mdb"
    Set dbNew = DBEngine(0).CreateDatabase(strFile, dbLangGeneral)
  
    'Create example properties in new database.
    With dbNew
        Set prp = .CreateProperty("Perform Name AutoCorrect", dbLong, 0)
        .Properties.Append prp
        Set prp = .CreateProperty("Track Name AutoCorrect Info", _
            dbLong, 0)
        .Properties.Append prp
    End With
  
    'Clean up.
    dbNew.Close
    Set prp = Nothing
    Set dbNew = Nothing
    Debug.Print "Created " & strFile
End Sub

“User Friendly” Name Autocorrect option

Some days ago I had to send few new queries to my colleague. I created a new Access database, imported there new queries and sent it to my colleague. I do not send queries such way very often, last time it was in Access 97 time, and it worked fine there...

If a few hours he replied that application (which uses these queries) does not work, hangs, etc. How come? After some investigations – I found a following feature of Access Name Autocorrect – when you import a query into database, which does not have source table(s) – all joins between missing table(s) just get deleted without any notification! Very bad behavior! So please be careful and as soon as you create new database – switch Name Autocorrect option off. Immediately! This will save you some time later.

Other problems, caused by this “User Friendly” option you can find at Allen Browne site.

Microsoft Access Executable

Michael Kaplan telling us the history of MDE acronym: You've never heard of Access MDX files? Thanks for sharing this, Michael!

AttachDB 2.0.100 was released

AttachDB 2.0.100:

• correctly determine latest SQL Server build 9.0.2047 (2005 SP1)


• other small fixes

Registered users will get new version via internet update

One more blog from Access MVP

Now Steve Schapel, from New Zealand, started a DMS Facets blog. There are no Access related posts (yet?), but you can find interesting posts on ISV and software business.