Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

I do… Ever since I discovered the short-cut in Access 2.0, notably in the property sheets, I double click on all sorts of combo boxes — or their labels — to select the next item in the list.

This was a smart interface choice by the Access development team: you almost never need to double-click on a word in a combo box to select it, for example, and the feature is especially useful for combo boxes with short lists: “Yes/no”, “not indexed / indexed (with duplicates) / indexed (without duplicates)”, etc. It speeds up design and avoids having to create check boxes for properties that are really just flags.

To implement the same feature in your own applications, one line of code is enough (or three here, for readability).
Private Sub MyCombo_DblClick(Cancel As Integer)
    With MyCombo
        .Value = .ItemData((.ListIndex + 1) Mod .ListCount)
    End With
End Sub

Open in new window

This sets the combo to the next item (list index plus one), wrapping to zero after the last item (modulo list count). The event can also be cancelled, but that is rarely useful.

It's not much code, but I use it so often that I normally create this function in the module dealing with the user interface:
Function ComboNextItem(cbo As ComboBox, Optional OrNull As Boolean)
' Selects the next item in the passed combo box.
' Set 'OrNull' to True (or 1) to select Null after the last item.

    If OrNull Then
        ' let Null happen by overflow of list index
        With cbo
      

Open in new window

6
LVL 75
"I suppose. Have you ever encountered a situation like this?"
Well, not specifically.  Usually ... it would be a Text box.  But I was mainly ... just saying ....

"A WinAPI call (GetKeyState) would do the trick, though."
That's what I used for years.  I have that function in my mda library. Pretty simple really.

'Return the state of keyboard key requested....
Declare Function adiSWA_GetKeyState Lib "user32.dll" Alias "GetKeyState" (ByVal nKey%) As Integer

Public Function SWA_GetKeyState(argKeyName)
'Actions:Determine via Windows API call if specified key was depressed.
'             The argKeyName passed must be one on the Global keycode Constants
'------------------------------------------------------------------------------------------
    If adiSWA_GetKeyState(argKeyName) < 0 Then
        SWA_GetKeyState = True
    Else
        SWA_GetKeyState = False
    End If
End Function

Example usage:
If SWA_GetKeyState(vbKeyShift) = True Then    ' whatever

So, the GetKeyState could also be in the module that contains your ComboNextItem() function ... all self contained.

mx
0
LVL 58

Author Comment

by:harfang
Thank you MX

Your comments are a good addition to the article. This makes is simple to hook the “select next item” function to Ctrl+double-click (or Alt+click for that matter) and also a “select previous item” function to another combination, as you suggested.

Select previous, change line 8:
            .Value = .ItemData(.ListIndex - 1)

Open in new window

and line 13:
            .Value = .ItemData((.ListCount + .ListIndex - 1) Mod .ListCount)

Open in new window

This is with wrapping, which somehow seems less intuitive in this case, but would still be correct for simple Yes/No combos or similar.

(°v°)
0
Introduction

When typing a search string, one normally expect the engine to disregard the case and the accentuation. For example, looking for "geneve" on Wikipedia will redirect automatically to Genève (in French), or Geneva (in English). This common mistake is anticipated, because the name "Geneve" -- formally incorrect -- is widely used. When searching for "sesamoide" in French, no automatic redirection occurs, but pages containing the word "sésamoïde" are presented. The same occurs in Internet search engines (along with more elaborate search tips).

The Jet Engine already performs case insensitive matching of textual data against a search criteria. 'A' equals 'a', 'Z' is greater than 'a', 'Spring' is like 's*ing', 'C' is between 'a' and 'b', etc. Note that this behaviour isn't configurable; how to perform case sensitive searches would be the topic of another article.

More often than not, a case insensitive search should also be accent insensitive. In many database engines, this is configurable, but not in Access. As a matter of fact, neither the Jet Engine nor Visual Basic expose any method or function specific to accented characters, although they are handled correctly as far as sorting is concerned.

This article briefly introduces how Latin characters are handled in Access, and presents methods to create accent insensitive search expressions. This is particularly useful when searching for names -- people's names…
13
LVL 4

Expert Comment

by:Dennis Johnson
I was looking for something like this.
I'll try to implement it in my project for the Greek characters (Windows-1253).
0
LVL 58

Author Comment

by:harfang
Addendum 2011-07-19

The suggested syntax

Between 'z' And 'zZ'
relies on the fact that Z is the last letter of the alphabet. As I recently found out, this is not the case for all collating orders. In Estonian, the last letter is the Y, in Icelandic the Ø, and in Norwegian and Danish the Å... This makes the suggestion to use ChrW(446) instead of Z even more meaningful.

Many thanks to zorvek and krishnakrkc for a very interesting discussion about this topic!

(°v°)
0
Introduction

In a relational database, most relations are between two diffent types of objects. A Product is assigned to a Category (a many-to-one relationship), Suppliers are found for Products (a many-to-many relationship), Products are sold to Customers (an indirect many-to-many relationship, via Orders and Details). In all cases, the left and right side are different tables.

Some relationships are so called self-joins, where both sides are in fact the same table. For example, a field "reports to" in the Employees table selects another Employee as supervisor. The supervisor can in turn have his or her own supervisor, creating a hierarchy (and potentially cycles).

Access, or rather the "Jet Engine", has little to offer to manage hierarchies. They are treated just like any other one-to-many relationships, and no special SQL commands are available to navigate hierarchies.

This article will explore what can be done with such data, and show that specialised functions might be needed for large or complex trees. When the tables are large, these functions should be of the "fast table lookup" variety, making use of the index(es) defined on the table(s), or using specially constructed indexes.

Many techniques are easier to understand using an actual database, so a demo file is attached. The article should be readable without it; it was at least written assuming the database wasn't opened in parallel. If it is, small indications in brackets and italics like
10
LVL 55

Expert Comment

by:Dale Fye
Markus,

Great article.  I used it and your Fast Table lookup article to come up with a solution to this problem.

But that created another problem.  When I call the GetTable function and pass it the name of a table that is a linked Access table, I get the following error message:

Run-time error '3251':
Operation is not supported for this type of object.
error highlighted
But the linked table contains an index titled "PrimaryKey", so I don't know why the GetTable function is generating an error.
0
LVL 55

Expert Comment

by:Dale Fye
Disregard previous post.  I didn't read far enough through the Fast Table Lookups article.
0
INTRODUCTION

Joining tables is a way to tell the DBMS (Access in this article) what data to hold in memory from a group of tables. The purpose of a DBMS is to store, retrieve, and analyze data. Join is a way to limit the retrieved data that is stored in memory for further processing.  Joins include cross join, inner join and outer join types.

This article addresses the inner join and introduces a way to help design the FROM clause of a SQL statement using INNER JOIN of many tables. It removes the confusion of ordering and bracketing pairs of tables in the join sequence. This is treated with reference to the way a relation is drawn in query design mode.

This is a step by step tutorial using the Access environment.  A sample database is included to reduce the effort of recreating the necessary setup needed to continue with the tutorial.

A new approach will be presented to compile the join part by knowing the order of relations between tables, and the start and end fields of each relation

AUDIENCE

You understand the need for joins between tables.
You know the types of joins between tables.
If you experience some confusion in writing  the SQL by INNER JOINing the five tables, keep reading.
If you are looking for a concrete way in writing the join clause, keep reading.


PREREQUISITES

It is assumed that you:
understand  the difference between a Table and a query;
know how to use the query designer to relate tables;
6

Expert Comment

by:funaroma
"That is fine, so giving the readers an example will not be difficult or time consuming."

When did this become MY job?  You're the "expert." who wants votes.  =)  I simply proposed that a real world example would reach more people.  I stand by that statement.

"As an instructor, concrete does not mean using Employees instead of E."

Depends on what, and whom, you are instructing.  You haven't mentioned who you feel your target demographic for this article actually is.  As an instructor, that's the first thing you should understand.  If you were to come back to me and tell me "this article is intended for an audience of Computer Science majors, at least 2nd or third year" then I'd not have an issue.  Beyond that, I think your article is difficult to approach for the average Access user.

"This is important because I am trying to write an article ".... from concrete to abstract"."

This would be great if it were true.  But you couldn't have started out any more abstract:

"For a database of 5 tables, A(a, r, x), B(a, b, r, x), C(b, c, r, x), D(c, d, r, x), and E(d, e, r, x), and a table of relations between these tables"

Notations do NOT lead to more concrete understanding.  Quite the contrary - they are just another layer of abstraction.

I've met a lot of people, right out of college, that studied with instructors "teaching" exactly this way.  Frankly, their problem solving skills left a lot to be desired, and they couldn't code (or troubleshoot, or even convey meaning) their way out of a paper bag.  If they can even remember which concept to apply (because they never had a concrete, real-world example to which their minds could attach the exercise or technique), they often apply it completely incorrectly.

This was not intended to be a pissing match.  Heck - someone already voted "yes" to your article long before I came along, and as of this comment, 5 of 6 people found it "useful."  So it looks more and more likely that I'm completely off base here.  I feel the suggestion I've made couldn't HURT you any...  I think you'll get your (valuable!!) message across to a lot more people, with a broader range of experience and needs, if you consider updating and changing your writing style for this or for future articles for this site.  This site is not a CS400 college text book, it's a site where people with much less skill and knowledge come to get help.  Writing for that audience is a skill all its own, and would highly compliment the skills you clearly already possess.  It's HARD to make things look easy... but the challenge is worth it.  If more college professors understood this, many more college students would graduate with more useful skills, right from the start.

Take it for what it's worth... I have no personal bone to pick with you of course.  It looks like I might be the odd man out on this one, but give the suggestion some thought anyway... perhaps just consider it an exercise for your OWN development as a writer.  I'd be interested to see what you came up with.
0
LVL 31

Author Comment

by:Hamed Nasr
funaroma:

My last comment on this side argument.

"When did this become MY job? '
I was expecting a real example of the type you think a user can better learn from.


"You haven't mentioned who you feel your target demographic for this article actually is"

In the second subheading in the article:

AUDIENCE

You understand the need for joins between tables.
You know the types of joins between tables.
If you experience some confusion in writing  the SQL by INNER JOINing the five tables, keep reading.
If you are looking for a concrete way in writing the join clause, keep reading.
0

This tutorial is written as a how-to for a novice to master at creating, manipulating and coding MS Access databases. This tutorial assumes you have a general understanding of how to use the basic functions within MS Access, as well as how to view the underlying VBA code. I have included a sample database to allow you to see the code in action, and how it is utilized, as well as some other helpful resources that come in handy when using this method. The code is also included below for easy reference.

Please note that as with anything there is more than one way to do things and everyone attacks a problem from his or her angle or perspective.

I searched for a Mouse Over like effect for a project I worked on in 2001, and it was then that I realized that Microsoft failed to include some common functionality in the Access application. I found some solutions that partially did what I needed, but none were exactly what I was looking for. So I went to work and created a few test applications trying different things and identifying the bugs. The outcome provided a set of standard tools that I now use in many of my applications.

This is my guide to "Mouse Over Effects" within Microsoft Access.

The type of object to which you are applying Mouse Over Effects will determine which way is best. For instance, the effects are applied differently for a text box or label than they are for a shape, such as a square or a border. This tutorial covers the most straightforward implementation of my technique, but it can be changed to obtain more elaborate visual effects.

 

1. Mouse Over for text box and label

This section will cover applying a Mouse Over Effect to a text box, using a change in the font size, the font color, and font face.

 

 Create a new form save and rename it "frmYOUR FORM NAME".


 Open the form in design view, and add a text box to the form.


 Select the text box, and view the properties pane, select the other tab and name the text box "txtbxYOUR TEXTBOX NAME" repeat for the attached label "lblYOUR LABEL NAME".

Screenshot1

 Keep in mind the font will be increasing inside the label or textbox area, therefore the label or textbox must be large enough to accommodate the increase in size. Increase the size of the label or textbox in both height and length:

Screenshot2

 Click on your text box to select it and view its properties.


 Select the Event tab, then in the On Mouse Move event and click on the button with three little dots. This will open a window allowing you to choose how you want to control this event. Select Code Builder and click OK.

Screenshot3

 Code Builder will open the MS Visual Basic editor for the current database. You will use this interface to make all of the code changes and additions to your database project.


 In the editor call the control for the text box and label you just created, using "Me." and the control name.


 The code below will increase the font size, font color and font face of your control.


Private Sub txtbxMouseOverTest_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
                                        'Create a Mouse Over Effect for the text box by increasing font size, changing font color, and font face
                                        
                                        Me.txtbxMouseOverTest.FontSize = 14
                                        Me.txtbxMouseOverTest.ForeColor = vbRed
                                        Me.txtbxMouseOverTest.FontBold = True

 


 Now set the default state for the controls. This means anytime the mouse is moved off of the text box or the label, and over the Detail section of the form the textbox and label will return to their default state.


Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
                                        'Set the default state for the controls that will have an onMouseOver effect applied
                                        
                                        Me.txtbxMouseOverTest.FontSize = 12
                                        Me.txtbxMouseOverTest.ForeColor = vbBlack
                                        Me.txtbxMouseOverTest.FontBold = False
                                        
                                        'If you want the label to change as well add its default state here
                                        
                                        Me.lblMouseOverTest.FontSize = 12
                                        Me.lblMouseOverTest.ForeColor = vbBlack
                                        Me.lblMouseOverTest.FontBold = False
                                        End Sub

 


 Now that the default state is set, you can test the Mouse Over Effect.

 


 Open the form in form view, and hover your mouse over the text box.  You should see the text font size increase, as well as the color change to red.  Let's move on to mouse over effects on shapes.

Remember: The control must be oversized enough to accommodate your choice of font size so it may be necessary to tweak this if you are going to increase the font size drastically.


End Tutorial for Mouse Over Effects on text box and label.


 

2. Mouse Over for a shape

This Section covers how to apply some of the same thinking to a shape on your form.

 

 On the form you created, add a rectangle using the rectangle tool.


 Name it "bxYOUR REC NAME", and set its format properties so that it has a 3 point solid border, with a background color that matches the form detail.

Important: The shape must have a normal background. If you set it to transparent the Mouse Move event will only be effective when your mouse is over the line that creates the border of the shape. By filling in the shape the effect will work anytime you mouse is over any part of the shape.

 Screenshot4

 Set the shapes default settings in the detail on mouse move event. Set the default state using the code below.


Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
                                        'Set default state for rectangle
                                        
                                        Me.bxMouseOverTest.BorderColor = vbBlack
                                        Me.bxMouseOverTest.BorderWidth = 3
                                        
                                        End Sub

 


 Now add the desired Mouse Over Effect. For my example, I am just going to have the rectangle border color change. It is simple but effective for a shape.


Private Sub bxMouseOverTest_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
                                        'Create a Mouse Over Effect for the rectangle, changing its border color to red
                                        
                                        Me.bxMouseOverTest.BorderColor = vbRed
                                        
                                        End Sub

 


 Now test it to ensure it works properly. Open your form in form view and move your mouse over any part of the shape, you should see the event trigger and the border color change to red.  If so, you have successfully completed your first Access Mouse Over effects programming. Well done!!

End Tutorial for Mouse Over effects on shapes.



NOTES:


In some cases, the Mouse Over effect may appear to flicker or flutter, if this occurs you more than likely have some sort of a form repaint or requery action occurring. If that is the case you may have to throw some IF statement onto the form detail section to ensure that your text box or label is in the desired default state prior to the mouse moving over them see code example below.


Example:

 

'This will give the label a on mouse over effect - return to original font size
                                        If Me.lblYOUR LABEL.FontSize = 10 Then Me.lblYOUR LABEL.FontSize = 9
                                        If Me.lblYOUR LABEL1.FontSize = 10 Then Me.lblYOUR LABEL1.FontSize = 9
                                        If Me.lblYOUR LABEL2.FontSize = 10 Then Me.lblYOUR LABEL2.FontSize = 9


The example provided only covers changing control colors using built-in vb colors, limiting your color options. If you refer to the attached Excel document, you will find the conversion table for over 300 colors. It provides information to convert from HEX to RGB and MS Access. The code has to change slightly when using a different method of choosing colors. For instance, if you were to use an MS Access color code the syntax would be as follows.

 

Me.lblYOUR LABEL NAME= 221695

This example is an orange color.



This concludes my tutorial on how to force MS Access to emulate a Mouse Over Effect programmatically. I hope you found it helpful. If you have any questions or comments please feel free to me me here on EE.


Mouse-over-effects.accdb

Programing-Color-Referance.xlsx

6
LVL 58

Expert Comment

by:harfang
Good article!

I understand that this is a tutorial to explain the basic concept, but I tend to take the final comments seriously: this technique may indeed cause “flicker or flutter”. The technical reason is that a formatting change forces a form redraw, which in turn forces an empty mouse move event (even if the mouse didn't move). This causes a continuous cycle of redraws.

I always try to perform formatting changes only when needed (e.g. not set the colour to black if it's already black), exactly like in the suggested code at the end. But perhaps this is because I have worked on very slow computers in the past...

Voted yes!
(°v°)
0

Expert Comment

by:Alan Bell
Thanks - I found this quick tip extremely useful - I like to use mouse over to highlight to the User exactly what cell they are looking at.......and the flickering was really annoying me......Simpler is better...!!

Thanks
Alan Bell 09/03/2017
0
Have you ever written a loop or a multi-step procedure during which you would have liked to keep your user appraised of your process?  Are you dissatisified with just changing the cursor to an hourglass to inform your users that a time consuming process is executing?  If so, then this article will provide you with a couple of alternatives.

Access provides us with several built-in methods for informing our users about ongoing processes.
1.  You can change the cursor from a pointer to an hourglass.  Although this method provides your users with a visual indication that something is happening, it does not tell them much.  Even so, this is a useful way to give an indication that something is happening.  There are two ways to accomplish this:

    a.  Docmd.hourglass  - This method allows you to change the cursors image from its default to an hourglass, or change it back.
    b.  Screen.Mousepointer – Mousepointer is a property of the Screen object.  You can determine the type of pointer currently in use, or change the type of pointer to one of 6 different options.  The parameter values for the default cursor and hourglass are 0 and 11.  I find it helpful when I have turned on the hourglass, and then open a dialog box, to check for the value of the mousepointer, store that value, then set the pointer to the default.  Then, when I close the dialog form, I set the mousepointer property back to what it was when I opened the form.  Whenever you use either of …
11
LVL 61

Administrative Comment

by:mbizup
EvertJor,

That is not an issue in the sample database as posted -- which works fine as is.

If you are having trouble with your own implementation of this, please post it as a question in the MS Access Topic area, preferably including your database as an attachment to illustrate your issue.

mbizup
EE Page Editor/Access Topic Advisor
0

Expert Comment

by:Adam Borkowski
Got the same problem,

Change the line in sub: cmd_Generate_Click()

            strSQL = "INSERT INTO tblRandomNumbers (ID, OuterLoop, InnerLoop, RandomNumber) " _
                   & "Values (" & (intOLoop - 1) * Val(Me.txt_InnerLoop) + intILoop & ", " _
                                & intOLoop & ", " & intILoop & ", " & Rnd() & ")"

into
            strSQL = "INSERT INTO tblRandomNumbers (ID, OuterLoop, InnerLoop, RandomNumber) " _
                   & "Values (" & (intOLoop - 1) * Val(Me.txt_InnerLoop) + intILoop & ", " _
                                & intOLoop & ", " & intILoop & ", " & Replace(Rnd(), ",", ".", 1, -1, vbBinaryCompare) & ")"

For testing purposed it will be enough
0
After experimenting a while with the new split form in Access 2007 I found that it has too many problems to be usable, especially if you need subforms.

The idea is good but internally Access uses two different form objects to create a split form and that's often a big problem. I tried to use an object variable inside of a split form and after a lot of tests, wherein I asked myself "why am I not able to access the stored data inside of the datasheet object", I found out that two objects are instantiated when opening a new split form -- one for the single form and one for the datasheet form. Access handles the form in VBA "as one" so any access to the form uses only the single form. Only constructs like "Screen.ActiveDatasheet.Form" can be used to access the datasheet form.

Another annoying point is the automatic creation of subdatasheets if a subform is created on the single form part. Every line in the datasheet part gets a "[+]" sign and you can open the subform directly at the chosen line. That would be good if both forms would be synchronized -- but if you change something in one subform it is not shown in the other part. If you have more than one subform, only the first one in the tab order is bound to the datasheet part. No chance to change this. If you try to change the SourceObject property of the subform to change the subform to another form then Access crashes if you try to access the [+] in the datasheet part...

I found no way to switch off this feature.
9
LVL 25

Author Comment

by:Bitsqueezer
Hi Joseph,

you must create an own collection and insert the references to the created forms into the collection:

Code in the form which should create the other forms:

In the header of the form's code:
Private colForms As Collection

Open in new window


Form_Unload Event:
Set colForms = Nothing ' This will automatically close all forms opened with the collection

Open in new window


Button_Click Event:
Dim objForm As Form_YourFormName ' <- here is the right place to use that
If colForms = Nothing Then Set colForms = New Collection
Set objForm = New Form_YourFormName ' <- here also, now the form is loaded but not visible
colForms.Add objForm, "Add a meaningful name here for each different reference (unique)"

Open in new window


Now you can make each reference to the form visible either with the objForm variable (inside the code above) or by accessing the collection:

colForms("TheMeaningfulName1").visible = True

Open in new window


Cheers,

Christian
0
LVL 1

Expert Comment

by:Joseph Krausz
Hi

I just test it and you're right, using the form_formname for form instance purposes (frm as form_formname) will not break the listener, in the mean time i've improved my code form_formName free.... Anyhow, it's working and really

Regards
Joseph
0
Problem:  Anyone who has worked with database applications for any length of time has developed forms for doing complex filters.  These usually involve comboboxes, multi-select listboxes, and the occasional checkbox.  These controls are great for filtering on fixed length fields with specific values, but when it comes to filtering text strings, or memo fields, we (I) have generally settled for allowing users to type a couple of keywords into a textbox.  We then build criteria strings which parse these key words and concatenate them together with a series of OR statements, resulting in a criteria which looks something like:

[Field1] Like “*Keyword1*” OR [Field1] Like “*Keyword2*” OR [Field1] like “*Keyword3*”

Unfortunately, this is not very flexible, and when we get the results, we realize that the criteria is too broad, or not broad enough, so we play around with it a little, and eventually settle on a criteria string which is decidedly not what we had hoped for.  Well, not anymore!

Solution:  I recently had a requirement to provide a client with the ability to search three different text fields for a variety of keywords, including being able to group them (keyWord1 AND keyWord2) and to exclude words (AND NOT keyWord3) from the result set as.  Some of the more advanced web search engines can to this, and I’m sure they use a similar technique.  Basically, I created a function which will accept a string of keywords and an array of fields to which the …
10

Introduction

One of the leading measures of central tendency is the average, or arithmetic mean.  Microsoft Access makes it easy to calculate the average for a data set by using the Avg aggregate function.  However, the arithmetic mean is not the only type of mean that exists, and for certain analyses, other types of means may be more appropriate.

This article will demonstrate how to calculate several two other useful, yet less-commonly used means, using Microsoft Access:


Part 2 of this article will extend the discussion to include the following other types of means:


Note: the SQL statements required for these “special” kinds of means are somewhat complex, but should be within the grasp of any intermediate or advanced Access user.  For that reason, I have not provided VBA versions of these functions.  Further, by relying solely on native Access functions, your application will exhibit faster performance.


Sample File

For examples of all of the cases discussed below, please refer to the attached sample file:
Special-Means-Part-1.mdb
This file contains all of the data and query definitions used in the four numbered examples in this article, and you may find the file useful for extending these examples or creating your own new examples.


Weighted Average

A weighted average
5
by Patrick G. Matthews


Introduction



Access provides functions for computing several common descriptive aggregate statistics for column data, offering such aggregate functions as Count, Sum, Min, Max, standard deviation and variance (StDev, StDevP, Var, and VarP), and average (Avg).  In addition to these typical aggregate functions, Access also offers the so-called "domain aggregate" counterparts for each of the aggregates already mentioned.

In a recent article, I described how to extend that list to include the median, mode, skewness, and kurtosis statistics.

However, none of these statistics tell us anything about how a paired columns of data relate to each.  This article addresses that gap by demonstrating how to use Microsoft Access to calculate the covariance and correlation between a pair of columns.

The next two sections will take covariance and correlation in turn, and demonstrate how to perform the computations first using native Jet SQL, and then using the new Visual Basic for Applications (VBA) functions that provide "domain aggregate"-like capabilities for covariance and correlation and are included with this article.

The concluding sections of this article will contain:
The source code for these new user-defined functions
Provide sample files and instructions for implementing the new functions in your Access projects
Suggest additional resources for more rigorous statistical analysis


Covariance



The covariance statistic
10

Expert Comment

by:Ron Indy
I tried the SQL code and got numerous #Error errors. This was due to not having any variance in one of the columns - all values in that column were the same for two-level grouping I was using. If so, the correlation would be zero, right?
0
LVL 93

Author Comment

by:Patrick Matthews
NeedHelpNow,

Not zero, but rather undefined: arithmetically, division by zero is undefined.  So, if at least one of the variances is zero, then the denominator is going to be zero.

Patrick
0
By Patrick G. Matthews


Introduction



In Microsoft Access, Switch is a very useful and powerful function that allows you to build conditional branching logic into your expressions in queries, forms, and reports.  In essence, you pass Switch one or more pairs of expressions; within each pair, the first expression is evaluated as a boolean expression, and the second is evaluated for its result.  The Switch function evaluates each pair of expressions, and returns the result corresponding to the first boolean expression in the list that evaluates to True.

Switch is often compared to the CASE statement in SQL Server, or the DECODE function in Oracle.

In my opinion, Switch is an underused function, and a better alternative to the somewhat more common "nested IIf" expressions often used in logical expressions requiring more than two branches.

This article provides an introduction to the Switch function, with sections that:
Describe the syntax for the Switch function;
Discuss alternatives to Switch;
Present sample use cases for Switch, based on actual Experts Exchange questions; and
Cover common "Gotchas" and challenges in using the Switch function.


Switch Syntax



The basic structure of a Switch expression is as follows:

Switch(expression1, value1[, expression2, value2 ... [, expressionN, valueN]])
27

Expert Comment

by:Feral Cypher
Why do you say Access doesn't have a Case  Statement?  I use it all the time.   Please clarify.
0
LVL 93

Author Comment

by:Patrick Matthews
VBA has the Select Case construct, but that is not what I was referring to.  What I was referring to was the CASE statement in SQL. SQL Server supports that, but Access does not.
0
by Patrick G. Matthews

1. Introduction


A common request in the various database zones at Experts Exchange involves returning a concatenated list of the various items in ColumnB for each distinct value in ColumnA from a particular table or query.  For example:

I have a table that currently holds student subject enrolments with the following fields

Student_ID, Subject_ID
1001, 11001
1001, 11023
1001, 11049
1002, 11023
1002, 11046
1003, 11023
etc

So a student can appear in the list a variable number of times depending on how many subjects they are enrolled in. Some students may be in 10 subjects some maybe in as little as one. I want to be able to transpose the information stored in this table and export it in a linear format something similar to.

Student 1, subject 1, subject 2, subject 3 ... subject n
student 2, subject 1, ,,,subject n

Like other databases, Access does not offer native functions that will do this.  One could use subqueries to return columns for each subject as above.  However, the following limitations apply:
In a Jet SQL solution, you would have to know how many detail values to allow for, and you could not simply let the SQL statement determine it dynamically
You could use VBA to dynamically generate a SQL statement, but that is beyond the skill of beginning Access developers, and many intermediate developers as well
Any such scheme relying on subqueries would require a column providing an ordinal for the detail items within the group
46
LVL 49

Expert Comment

by:aikimark
This is really weird.  I'm populating a recordset from the second and third sample queries.  The qryProjectsByAcct query returns expected (published) results.  However, the qryTasksAndUsersByProject query shows garbage characters in the DConcat() field when the DConcat() results are greater than 255 characters.  The function is returning the correct string.  Somehow, the query result isn't populating the recordset correctly.
Note: The query datasheet view shows correct results.
Note: I'm running this in an Access 2007 environment.
* * * * qryProjectsByAcct * * * *
Acct1         11111, 49632
Acct10        50396
Acct2         38278, 42987, 51304
Acct3         42805, 48450, 51853, 51994
Acct4         50167, 51908
Acct5         50819
Acct6         51906
Acct7         54883
Acct8         53683
Acct9         55444

* * * * qryTasksAndUsersByProject * * * *
 11111        Project Management, User2
 38278        Analysis & Model Config Design, User1; Analysis & Model Config Design, User2; Benchmarking Analysis & Findings, User1; Benchmarking Analysis & Findings, User2; Benchmarking Model Building, User1; Benchmarking Model Building, User2; Central Administrator T   ??? .??????    ???????    ??????? '    h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??????  ?  ? ?    ?????????­???? I???
 ??????      ????????0 ????????? 0 ???  ??????????????????????   ?????????????  ??0   ??????????D ?<????   <????   :??? ?        
   ?  
   ?                   ????????    ??  ??    ?   ??     ?      :      :       ??  ???1??? ???????????  ??<???<??  ??        ?<?7????????<???<?  ????:  ?7?1  ?;???    ?  ??????  ? ??        ???7??????????????  ????:?7      ??      ?      ?      ?      ?    ???      ?      ?      ?      ?      ?    ???      ?      ?    ??      ?    ???    ??     ?    ???    ???    ???    ???    ??    ??  ?;??    ???      ?      ?  ?  ?  ???/?  ?;?
 42805        Analysis Add On, User1; Analysis and Model Configuration Design, User2; Analysis and Model Configuration Design, User6; Data Collection, User16; Data Collection, User2; Data Collection, User6; Data Collection Preparation, User16; Data Collection Preparati   ??? .??????    ???????    ??????? j    h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??????  ?  ? ?    ?????????­???? I???
 ??????      ????????0 ????????? 0 ???  ??????????????
 42987        Data Integration, User1; Data Integration, User2; Data Integration, User3; Findings and Recommendations, User1; Findings and Recommendations, User2; Installation, User1; Model Building, User1; Model Building, User2; Model Building, User5; Model Configurat   ??? .??????    ???????    ??????? =    h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??????  ?  ? ?    ?????????­???? I???
 ??????      ????????0 ????????? 0 ???  ??????????????????????   ?????????????  ??0   ??????????D ?<????   <????   :??? ?        
   ?  
   ?                   ????????    ??  ??    ?   ??     ?      :      :       ??  ???1??? ???????????  ??<???<??  ??        ?<?7????????<???<?  ????:  ?7?1  ?;???    ?  ??????  ? ??        ???7??????????????  ????:?7      ??      ?      ?      ?      ?    ???      ?      ?      ?      ?      ?    ???      ?      ?    ??      ?    ???
 48450        Analysis Support, User1; Data Integration, User1; Data Integration, User3; Existing Model Information Capture, User1; Extended Model QA, User1; Matrix, User1; Model Build, User1; Model Build, User5; Model Design, User1; Model Design, User5; Model QA, User   ??? .??????    ???????    ??????? Z    h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??????  ?  ? ?    ?????????­???? I???
 ??
 49632        Project Management, User2; WES PM, User1
 50167        Configuration, User1; Configuration, User7; Final Analysis, User1; Planning and Approach, User7; POC Monitoring, User1; POC Monitoring, User7
 50396        WES Project Manager, User1
 50819        Analysis and Model Configuration Design, User1; Call Center (Branch) Set-Up and Configuration, User1; Data Collection, User1; Data Collection, User8; Data Collection Prepartation, User1; End User Training, User10; Forecast Build, User9; Forecast Install,    ??? .??????    ???????    ??????? ( #   h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  
 51304        Transactions Report, User1
 51853        Auto Generate Reports, User1; Consolidate Report Date, User1; Extract Branch Attributes, User1; Monitor Report Progress, User1; Scheduling Query, User1
 51906        Configuration, User1; DAM, User3; Planning and Approach, User1
 51908        DAM, User1; DAM, User3; Foundation, User1; Foundation, User3; Planning and Approach, User1; Process Analysis Configuration, User1
 51994        ABR Analysis (Change Order IIII - 54644), User1; Change In Sales Averaging Period, User1; Change In Wait Time Analysis, User1; CSR Team Consolidation (Change Order II - 54134), User1; Extended Wait Time Pilot (Change Order I - 54135), User1; Wait Time Pil   ??? .??????    ???????    ???????  (   h?  ??   ????       ???
 53683        Analysis and Model Configuration Design, User1; Data Collection, User1; Data Collection Preparation, User1; Forecast Build, User11; Forecast Build, User3; Forecasting Training, User13; Planning, User1; Planning, User11; Planning, User3; Scheduling, User12   ??? .??????    ???????    ???
 54883        DAM, User1; DAM, User3; Process Analysis Configuration, User1
 55444        Adherence Adapter Development and Test, User1; Adherence Adapter Development and Test, User12; Analysis and Model Configuration Design, User1; Central User Training, User14; Forecast, User14; Forecast Build, User1; Forecast Build, User12; Forecast Build,    ??? .??????    ???????    ??????? K +   h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??

Open in new window

Testing code here.  The vItem variable is the sample query name.
        Set rs = DBEngine(0)(0).OpenRecordset(vItem, dbOpenSnapshot)
        Debug.Print clsPerf.StopTimer, "Openrecordset"
        rs.MoveLast
        Debug.Print clsPerf.StopTimer, "Movelast"
        rs.MoveFirst
        clsPerf.StartTimer
        Do Until rs.EOF
            Debug.Print rs.Fields(0).Value, rs.Fields(1).Value
            rs.MoveNext
        Loop

Open in new window

Feel free to message me to explore/solve offline.
0

Expert Comment

by:Thirumurugan Thiyagarajan
hi,


i don't want Sort the details in the consolidated cell , what i was list the same order need to be consolidated in single cell

how to remove the Sort option from VB code,

i try to remove the but get error

pls advice me

thank & regards,
0
by Patrick G. Matthews


Introduction



Linear regression analysis is a common statistical technique used to infer the possible relationships between a dependent variable and one or more independent variables.  While Microsoft Access does not have any native functions that specifically address regression analysis, it is possible to perform regression analysis in Access via queries.

This article provides a basic introduction to linear regression analysis, as well as instructions on how to perform a so-called "simple" linear regression (i.e., the model uses a single independent variable to estimate the dependent variable) in Access, using first a purely native Jet SQL approach, and then using a Visual Basic for Applications (VBA) user defined function to simplify the query definition.  (This article addresses neither multiple linear regression--i.e., regression analysis using more than one independent variable--nor any regression method producing anything other than a linear relationship between the independent variable and the dependent variable.)

This article also provides the source code for this new DSimpleRegress function, as well as sample files demonstrating the techniques described here.  Finally, this article discusses limitations to the approach described here, as well as other products that may be useful for regression analysis.


The Basics of Linear Regression Analysis



Note: It is not the intent of …
14
LVL 49

Expert Comment

by:aikimark
You might also try simplifying your SQL this way.  I create a throw-away field where I calculate a value that is used ten times in the SQL statement.
Example:
SELECT Code, (Avg(X * Y) - Avg(X) * Avg(Y)) As Term1,
    (Term1) ^ 2 / (VarP(X) * VarP(Y)) AS RSquared, 
    (Term1) / VarP(X) AS RegressCoeff, 
    Avg(Y) - ((Term1) / VarP(X)) * Avg(X) AS Intercept, 
    ((Count(X) / (Count(X) - 2)) * (VarP(Y) - (Term1) ^ 2 / VarP(X))) ^ 0.5 AS SE_Resid, 
    ((1 / (VarP(X) * (Count(X) - 2))) * (VarP(Y) - (Term1) ^ 2 / VarP(X))) ^ 0.5 AS SE_XCoeff, 
    ((1 / (Count(X) - 2)) * (VarP(Y) - ((Term1) ^ 2 / VarP(X))) * ((VarP(X) + Avg(X) ^ 2) / VarP(X))) ^ 0.5 AS SE_Intercept, 
    ((Term1) / VarP(X)) / (((1 / (VarP(X) * (Count(X) - 2))) * (VarP(Y) - (Term1) ^ 2 / VarP(X))) ^ 0.5) AS T_XCoeff, 
    (Avg(Y) - ((Term1) / VarP(X)) * Avg(X)) / (((1 / (Count(X) - 2)) * (VarP(Y) - ((Term1) ^ 2 / VarP(X))) * ((VarP(X) + Avg(X) ^ 2) / VarP(X))) ^ 0.5) AS T_Intercept
FROM Set1
GROUP BY Code;

Open in new window

If you like that result, I would suggest playing with doing a similar VarP() and Count(x) expression substitutions like I did with Term1.
SELECT Code, (Avg(X * Y) - Avg(X) * Avg(Y)) As Term1,
    VarP(X) As VarPX, VarP(Y) As VarPY, Count(X) As CountX,
    (Term1) ^ 2 / (VarPX * VarPY) AS RSquared, 
    (Term1) / VarPX AS RegressCoeff, 
    Avg(Y) - ((Term1) / VarPX) * Avg(X) AS Intercept, 
    ((CountX / (CountX - 2)) * (VarPY - (Term1) ^ 2 / VarPX)) ^ 0.5 AS SE_Resid, 
    ((1 / (VarPX * (CountX - 2))) * (VarPY - (Term1) ^ 2 / VarPX)) ^ 0.5 AS SE_XCoeff, 
    ((1 / (CountX - 2)) * (VarPY - ((Term1) ^ 2 / VarPX)) * ((VarPX + Avg(X) ^ 2) / VarPX)) ^ 0.5 AS SE_Intercept, 
    ((Term1) / VarPX) / (((1 / (VarPX * (CountX - 2))) * (VarPY - (Term1) ^ 2 / VarPX)) ^ 0.5) AS T_XCoeff, 
    (Avg(Y) - ((Term1) / VarPX) * Avg(X)) / (((1 / (CountX - 2)) * (VarPY - ((Term1) ^ 2 / VarPX)) * ((VarPX + Avg(X) ^ 2) / VarPX)) ^ 0.5) AS T_Intercept
FROM Set1
GROUP BY Code;

Open in new window

If you do, please let me know about any performance gains.
0

Expert Comment

by:Josh Bartusch
Patrick,

This may not be the place to ask this question, but here goes.
I'm trying to use the vba function in standard vba (rather than part of an sql statement).
It seems to be reading the function and executing, but i keep getting the 3115 error code.
Any recommendations on what i need to define my variable name as (variant?, double?, etc.) to get it to work out?
Any advice is appreciated.

Thanks!
0
by Patrick G. Matthews

Introduction


Like other database applications, Access provides support for computing basic descriptive statistics for a data set, offering such aggregate functions as Count, Sum, Min, Max, standard deviation and variance (StDev, StDevP, Var, and VarP), and average (Avg).  In addition to these typical aggregate functions, Access also offers the so-called "domain aggregate" counterparts for each of the aggregates already mentioned.

That said, there are other useful descriptive statistics for data sets that Access does not support via native functions.  This article will describe how to compute four additional descriptive statistics in Microsoft Access:
Median
Mode
Skewness
Kurtosis

In the next four sections below, this article will describe how to compute the median, mode, skewness, and kurtosis statistics in Access, using examples that I replicate in the attached sample files.
Where feasible, I demonstrate how to calculate these statistics using Jet SQL (Access's native SQL dialect).
I also demonstrate using user defined "domain aggregate" functions, developed in Visual Basic for Applications (VBA), to compute these statistics.

In the Source Code section of this article, I include the VBA source code for four user defined "domain aggregate" functions: DMedian, DMode, DSkewness, and DKurtosis.

This article's Sample Files
29

Expert Comment

by:Chris Galwey
Brilliant! Very useful thanks.
0

Expert Comment

by:lynn kanieski
Code worked great in your example access database but not in my access 2010 database.  I am assuming there are differences in the versions.   I didn't know where to start to modify your code.
0
by Patrick G. Matthews

1. Introduction


All major SQL dialects allow for a Count aggregate function, and Access's native Jet SQL is no exception.  As in other database platforms, Access's Count() (as well as the "domain aggregate" DCount()) function returns the number of records pertaining to a grouped result set and meeting the criteria specified in the query (in the case of Count()) or in DCount() in the third argument of the function.

Consider these example data:

Category  Num
-------------
x         1
x         1
x         2
x         3
y         1
y         1
z         1
z         1
z         2

Open in new window


In this case, it is very simple to get a count of items for each Category:

SELECT Category, Count(*) AS Items
FROM SomeTable
GROUP BY Category

returns:
Category  Num
-------------
x         4
y         2
z         3

Open in new window


Suppose, however, that instead of getting the count of each item for a given category, you instead wanted to know the count of distinct items for each category.  SQL Server's Transact SQL and some other SQL dialects support the Count(Distinct ColumnName) syntax:

SELECT Category, Count(Distinct Num) AS DistinctItems
FROM SomeTable
GROUP BY Category

returns:
Category  Num
-------------
x         3
y         1
z         2

Open in new window


However, Access's Jet SQL does not support this syntax.  This article will demonstrate three techniques for computing distinct counts.  The first approach uses two queries for each distinct count to be performed.  The second uses sub-queries to accomplish the same thing in one SQL statement.  Lastly, the third uses Visual Basic for Applications (VBA) code to create a user-defined function, thus simplifying the operation further but at the cost of some execution speed.

2. Distinct Counts Using 'Two-Step' Queries


One way to generate distinct counts in Access is to utilize what I refer to as a "two-step" process:
22
LVL 19

Expert Comment

by:david251
Great Article!  Thanks

-David251
0
LVL 1

Expert Comment

by:csehz
Patrick thanks for this article very much, your sample database was the biggest help to me to apply in my work

0
How Cloud Computing Applies to the Microsoft Access Community

Cloud computing will be a huge benefit to the information worker and Access community. Instead of worrying about the hardware and deployment issues around applications, one can focus on building the solution and using the enterprise quality cloud platforms which previously didn't exist or were prohibitively expensive and difficult to use. With Microsoft Access 2010 and SharePoint 2010, Access applications (in limited form) can be deployed over the Internet. With Microsoft Windows Azure and SQL Azure, one can create .NET applications and/or SQL Server databases in the cloud.

Using Microsoft Access to Connect to the SQL Server in Azure

From a Microsoft Access database, you can connect to a SQL Azure database and use those tables the same way you could link to SQL Server database on your network or SQL Express on your desktop. For a fraction of the cost of buying and setting up a SQL Server box on your network, you can have Microsoft do it for you without worrying about licenses, downtime, hardware, etc., and it's available over the Internet to anyone you give the credentials for logging into it. It's pretty simple:

Open an Azure account and create a SQL Azure database
Install SQL Server Management Studio (SSMS) for SQL Server 2008 R2 on your machine
Use the ODBC administration tool to create a file containing the connection to the SQL Azure database

5
LVL 56

Expert Comment

by:Mark Wills
Good Article.

Went to a MS meeting the other day. Azure and the cloud are very important initiatives for Microsoft, so great to revisit this Article and vote "Yes".

Would like to see more here in EE :)
0
This tutorial will show you how to take a solution from an expert on EE in VBA and apply it to your database object; very good for the beginner, that has never had to mess with VBA before.

If you are reading this then you have probably at least played around with MS Access, Microsoft’s answer to locally accessible, record limited, relational database application.

Now I am going to make some assumptions, I know I shouldn’t it never seems to be a good thing but I must.  I am going to assume that you have read the above and that you have opened MS Access and at least attempted to create some tables, forms and report; that you know how to create a form or report and add controls to them (naming them as you go).

Sites with Naming Convention Information:
http://en.wikipedia.org/wiki/Leszynski_naming_convention
http://www.mvps.org/access/general/gen0012.htm
http://ezinearticles.com/?Naming-Conventions-for-Microsoft-Access&id=186934


Now is when a lot of newbies find their way to places like Experts-Exchange (EE) looking for answers to a question that has arisen as you attempted to do something new.  This is a good thing; that is why we sit here and monitor sites like these.  We were all new to this at one time as well, and we have all had problems that stumped us. If you do some digging you will find some very good tutorials for all skill levels on sites like EE, so read up, do your research and see if a solution is already out there.  If not then go ahead and open …
13

Expert Comment

by:diakhate
I have a problem with my Form could you help!!!
I have two tables that are related by type and Number.
I would like to be able to see my subform populate every time my somebody enter on my main form the type and number I want the subform to show Type, Number, Description Weights Ect....
COuld you help
0

Expert Comment

by:Herbatron
The 2 links above the Events list did not work.
0

Introduction


One feature painfully missing in Access is the ability to edit calculated fields. To do so anyway requires unbound control techniques, i.e. using the form events to write the calculated field into an editable control, and to propagate updates to the underlying field... with one exception!

Changing the state a check box can happen in only two ways: the user clicks on the control (or its label), or presses the space bar while it has the focus. By trapping both events, the form can perform the underlying edit. The same can be said for option buttons and for toggle boxes, the two other controls dealing with yes/no fields.

The technique is useful for data where yes/no information is stored as 'Y'/'N', 1/0, or any other data type incompatible with the underlying -1/0 representation used throughout Access, including Jet and Visual Basic. It can also be put to good use in some special demonstrated in the attached demo database.

This article shows how the solution is reached step-by-step; experienced developers or hasty readers might simply skip to the end and study the final class module directly. It can be easily adapted for option buttons and toggle buttons as well.



Combo Box Mechanics (Form View)


Even if you never paid close attention, you will have registered, at some level, that checking a check box is a two-step process. When the mouse button is depressed, the background turns to grey. You can then move the mouse and …
2
Sometimes, when developing a report in Access, one might wish for more than one single Detail Section. Perhaps 22 inches is just not enough, perhaps conflicting settings should be applied to different areas of the section, perhaps a section break could serve as a conditional page break...

There is a simple trick to obtain as many Detail Sections as needed. Well, there is a limit — isn't there always? — of ten user-defined sections on any report (and 200" total height). There is also a prerequisite: the rows must have a unique identifier.

If there is an “identity” field, or a combination of fields that serve as unique key, then you can create a group on that field or fields. Since the key will change for every record, the “group” will contain only a single record, and thus both the group header and the group footer will behave just like the detail section...

For example, if you are designing a complex financial report for your customers, based on the Customers Table, having the field ID as key field, you simply create a group on that field:

Open the report in design view,
activate the “Sorting and Grouping” sheet,
select the field ID as “Field/Expression”,
(if you have more than one key field, select them one by one on succesive rows),
set “Group Header: yes” (on the last field of multi-field keys),
optionally set “Group Footer: yes” for a total of three Detail Sections.
header and footer on ID fieldTip:
4

Expert Comment

by:kamal dev
man can any body tell how to connect access 2016 v july 2017 with any external reporting tool. as it has limitations creating bills and invoices.
0
Page headers and footers are naturally very useful. It would have been unthinkable to create a reporting engine without them. However, they are not printed on sub-reports, the rationale being that the main report provides them. If one needs sub-report page headers or needs to customise them in any other way not allowed for the built-in section, there is a trick to create one's own.

Group headers (but not footers) have the option “Repeat Section: yes”. This means that the header will become an additional page header over all pages of that group. Now if there was a way to create one single group...

It's quite simple:

Open the report in design view,
activate the “Sorting and Grouping” sheet,
enter as first “Field/Expression”: =1
set “Group Header: yes”,
display the group's properties,
set “Repeat Section: yes”.
custom page headerThat's it. The header will behave almost like a page header: it can include page numbering expressions like “=Page” or “=Page & ' of ' & Pages”, labels, horizontal rulers, etc. If there is also a regular page header, it will always come second. The differences are that:

it will print when the report is a sub-report,
it can grow and shrink,
it appears after the report header on the first page.
If the last point is a problem, the page header can be modified to mimic the custom page header, which can be turned off on the first page. This trick is not explained in detail here, but one solution is the following “on print” event hander:

Open in new window

4
I just got finished migrating over forty five Access 97, 2000 and/or 2003 databases and the first "issue" I was presented with was the new security used in Access 2007.  

I also discoved the easiest way to walk a user through setting up the security is with a document.  Now granted, I had to walk more than a few via phone but this document should help most of us when it comes to migration and even database application moving.

The first time you open your migrated database you may have an “Security Warning” and even an Error message.  This is due to the new security that is provided by Access 2007.  The following steps will solve the issue.
1)  Open your Application via the shortcut or once inside Microsoft Access 2007 (MS Circle | Open)
2)  Answer OK to the error window (if there is one.)
3)  If there is no Security Warning bar on top of the main window, you are done.  If there is OR if you open an different Database and get the security bar, complete the following:
4)  Click the "Options..." button on the Security Warning bar.
5) This opens the Security Alert Window.  
 Security Alert6)  Click on Open the Trust Center link on the bottom of the Form.
 Trust Center7)  Click on Trusted Locations (Left window, second from the top)
 Trusted Locations8)  If the Applications is on a Network Drive, Be sure to click the "Allow Trusted locations on my network"
9)  Click on Add New Location
 Add Location10)  Enter the path of your …
1
Here is a quick function that will dump any Access table or query to a Tab delimited file with headers. If you don't want the header, comment out the lines 38 to 47.

I built this function because, historically, Access databases will get corrupted and then have to be imported into a blank new database. Many times the drop down would be missed to check the box to import the existing Export and Import specifications. So then at the end-of-month, quarter, year the export would be tried and it will fail. The reason is that the specification is lost and would have to be rebuilt by hand.

It takes longer to build these custom import and export functions. However, once they are built built the first time, it becomes easier to adapt for the next situation.

I did this with a tab delimited (Chr(9)) file in mind. But you can easily change if it for a comma, pipe "|", or any other character you would like to use. Just substitute the Chr(9) code with either another Chr() code or a quoted value. To find other special characters do a search for "ASCII" in the Access help. It will give you a table of characters to use. If you wanted a double or single quoted export you can change the " & Chr(9) & " to "& Chr(xx) & Chr(9) & Chr(xx) &". Chr(39) is a single quote mark; Chr(34) is a double quote mark.

Just put in the TableOrQueryName as the Table or Query you want to export. Then put in the full file name and path (i.e "C:\Temp\MyFile.txt") as the FileNameAndPath value. That should …
4
LVL 38

Author Comment

by:Jim P.
You mean :

'FileNameAndPath = "C:\MyDirectory\MyFile.txt"


That is rem code for testing. I assume a valid from the end-user. I could probably build the FSO system, but that is not relevant for the code I'm posting.
0

Expert Comment

by:EdLB
Jim, I am a highly unsophisticated ACCESS user but I could really use this function. A couple of questions:
1) When I download the db and run the form, I get an error "MS database engine could not find the input table or query 'MyQueryName'. Make sure that it exists and that it's name is spelled correctly." So I can't get it to work in it's initial db. How do I fix this?

2) If I want use this in another db, can I just export the Form and the function to the other database? If not, what is needed to do that?  This way I don't have to code anything (I hope).

Thanks,
Ed
0
Introduction

Nobody prints calendars any more. This used to be a frequent request, but today, one consults calendars only on screen: linked to email software, on-line collaborative solutions, gadgets on portable devices. Why write an article about something obsolete?

One reason is that the few times it makes sense to print a calendar, nobody remembers how to do it. In those cases, the calendar often ends up being produced and formatted using a text processor or a spreadsheet. With a simple template available, this won't be necessary.

The second reason is that printing calendars provides a very good introduction to Visual Basic. Even complete beginners can learn the concepts and techniques needed to print a calendar within hours, and several major topics can be introduced naturally: event programming, controls, variables, handling dates, loops, etc. The problem at hand is simple to explain and the result can be validated by anyone; it doesn't look or feel like a programming assignment.

This isn't a class. However, if you have only a little experience using Visual Basic -- and a little more with reports, you might want to try following the instructions in the first section before looking at the solution. Even if the code looks simple, it isn't entirely trivial either; if you don't understand something, you can of course ask questions in the Access Reports area. On the …
7
LVL 50

Expert Comment

by:DanRollins
Great article!
0
LVL 61

Administrative Comment

by:mbizup
Awarded Editor's Choice.

mbizup
EE Page Editor
0
This article is part of the app development series, a series of articles on Experts-Exchange.com that explores common application development problems and their solutions.

This article presents code written in VBA executing on Windows platforms, but the techniques demonstrated here could be used with any language or application.

Level: Intermediate

THE PROBLEM

One common problem in development is how to prevent users from running multiple copies of an application.  In some cases users need to be allowed to do this, but in others they need to be restricted from doing so.  Sometimes it's simply a performance issue that dictates this.   It might also be a development one (the app will not function correctly if multiple instances are run on the same station).   It may be only to help a user out.  Users will often start an application again and again only because they cannot locate an application that is already running (an application window may be hidden behind another or be off screen).

To be able to prevent multiple instances from running, we'll need to use a semaphore (a flag) to signal when our app is running.  This flag needs to be visible to every process running on a station.  Often, developers will try and create such a flag by saving a file on disk, setting an entry in the registry, or saving data in a database or file.  This flag gets set when a user starts the application and removed when the user exits.  Sounds like that would work…
11
LVL 29

Expert Comment

by:IrogSinta
Great code and well written article!
0
LVL 62

Author Comment

by:Jim Dettman (EE MVE)
Glad you like it Ron.

Jim.
0
Applies to: All versions of Access    Level: Intermediate

Introduction:

One of the things all of us tend to loose sight of is that what we think of "Access", is really a couple of different components working closely together.   Access is made up of a user interface and objects (forms, reports, and macros), JET, which is the default database engine, and Visual Basic for Applications (VBA), which provides a coding language.  Because of this, there are certain things that you would expect to work somewhat the same, and yet are very different.

One of these areas is the use of CurrentDB() vs. dbEngine.Workspaces(0).Databases(0)   (or dbEngine(0)(0) for short).  While the two seem similar in that they both give a reference to a object for the current database, they are not the same.

The problem:

CurrentDB() is an Access function and is processed by the UI layer, which returns a new reference to the current database.  As a result, it's always up to date with current information (i.e. it would include a form that was just added) in its collections.  Using dbEngine(0)(0) on the other hand is getting a pointer to the DAO (Data Access Object) reference maintained by JET.   In using this reference you are bypassing the UI layer, which means that you won't know if something has been done in the UI unless you do a refresh of a collection.  That refresh can be quite expensive in terms of performance.

So why would you want to use a dbEngine(0)(0) reference …
14
LVL 62

Author Comment

by:Jim Dettman (EE MVE)
That is, as far as hope you'll agree, a real world app

 That's closer, but you also keep talking about the number of records, which for this is irrelevant.  What matters is the number of objects in the DB container.

 And keep in mind, this was written almost ten years ago...Access was different back then as well as PC's and this was old news back when I wrote this.   What was true back then might not be true today given that eighteen years have passed since A2000.

 I haven't even thought about this in a long time....I've just used it in all my apps since then.

Jim.
0

Expert Comment

by:Colin Riddington
Jim

The whole point of my last post was to say the results were still very similar in a database with a large number of objects.

Anyway, I've modified the tests to add 10,000 records to a table in 10 loops of 1000 each

#1 Set db=CurrentDB followed by db.Execute followed by Set DB = Nothing - after clearing the data & again after each 1000 record loop
#2 As above but with db=DBEngine(0)(0)
#3 No variable set/destroyed. Used CurrentDB.Execute in each step
#4 No variable set/destroyed. Used DBEngine(0)(0).Execute in each step

I repeated each test  5 times and calculated the averages

Speed Test Results
As you can see, there's still not a huge difference in values between the tests
BUT overall method #1 using Set db=CurrentDB is faster than method #2 using Set db = DBEngine(0)(0)
Similarly #3 using CurrentDB.Execute is very slightly faster than method #4 using DBEngine(0)(0).Execute

And of course there is still the need to factor in refreshes as necessary using DBEngine(0)(0)

And keep in mind, this was written almost ten years ago...Access was different back then as well as PC's and this was old news back when I wrote this.   What was true back then might not be true today given that eighteen years have passed since A2000.

 I haven't even thought about this in a long time....I've just used it in all my apps since then.

Exactly the point I've been trying to make throughout.
I'm happy to accept your point about performance benefits using DBEngine(0)(0) may well have been true once.
However, so far I've seen no evidence that is still the case now.

I'm attaching my new test db - feel free to modify the tests if you still think these aren't appropriate

Cheers

Colin
SpeedComparisonTests---NEW.zip
0

Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.