Microsoft Access

221K

Solutions

51K

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 54

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 54

Expert Comment

by:Dale Fye
Disregard previous post.  I didn't read far enough through the Fast Table Lookups article.
0
I titled this "The great PK debate: Natural Keys vs. Surrogates (again)", because on almost any developer list or forum that deals with databases, this topic always comes up  at one point or another and usually with very strong opinions on both sides.  What started me on this article was that several days ago I tripped over an article on primary key's that was just flat out wrong.  

After poking around a bit on the net I realized that over the years, a lot of myths and misconceptions have grown up around this topic.  It seems that as the years go by, more and more gets published and discussed on this topic, but things only get cloudier.  For example, are auto number fields really a surrogate key?  Can they function as a primary key?   I hope that through this article, I will be able to clarify and explain fully enough the answers to questions such as those and hopefully, another great PK debate will not ensue (or at least if it does, you'll have plenty of ammo for the debate<g>).

When I was looking at articles out on the net, one thing that struck me about almost everything I read is that not many started off on the right foot. So the first thing to clear up is what relational theory actually is. Relational theory (the big "R" as some call it), was developed by E.F. (Tedd) Codd while working at IBM.  His first paper "Derivability, Redundancy, and Consistency of Relations" was published in 1969 as a research paper. While Codd was working on ways to store data in a…
11

Expert Comment

by:developingprogrammer
Jim thanks so much for taking the time to write this article!! It has really helped me a lot and your writing style has transformed something abstract and difficult to understand for a beginner like me to some easy to digest and logical!! Thanks once again and your efforts are greatly appreciated by me and all the rest!! = ))
0
LVL 25

Expert Comment

by:Bitsqueezer
Hi,

good article about the difference between what "internal" and "external" keys are in the sense of "exposed" or "hidden" keys like harfang said above.

But I completely disagree about the term "PKs are not 'special'". That's in most cases wrong. The big difference between PKs and other indices made unique (where logically is no difference, you can apply a PK on an autonumber field and also a unique key) is the way they influence the physical order of records in a database.

In most relational databases including Access and SQL Server, the PK is a "clustered index" (in SQL Server you can create PKs without clustering, as far as I remember, but in Access you can't and in SQL Server it is the default that a PK is always a clustered index).

That means: If you have an ID field with "1,2,3" as records the physical order of the records will be "1,2,3". If the ID field used for the PK is not an autonumber field so that you can change it manually and you change the first record to "4" then the physical order of all records will be changed, after changing the ID it will be "2,3,4" and not, in case of a simple unique index, "4,2,3" (means: the physical order has not been changed).

This is a VERY important speciality of PKs which is often forgotten in such discussions as it makes a big difference in performance. Using a "natural" key with informations like a phone number or a currency code means: If it will be changed, the complete table will be reordered physically on the data storage volume. So this is the most often reason why developers mostly uses a PK with an autonumber field: This guarantees that the physical order will never change as the next saved value is of course higher than the last. For the same reason it is not a very good idea in common to use a GUID as PK: It is a very random value, very long, text based and useless for communication (can seldom be used as for example customer ID). Used as PK that means it is guaranteed that it will reorder the physical structure of a table each time you write a new record.
(Access has not the same problem here so much: It reorders the records physically only if you use "compact & repair", but a database server does this permanently (of course with optimization about the point in time).)

The physical sorting of a table in the order of a PK is not "just for fun" - it could have been created like a normal unique key without clustering, but the idea of the developers of the PK idea is to make this one the fastest index of all other, this is the reason why it IS the Primary Key, not because it is the "most often used" unique key or the key with the highest importance. If that would be the case the PK would have no advantage against other unique keys, you would create i.e. more than one unique key and use one time this and other time that, you would have no difference. In that case the statement would be true that you can use a combination of two ID fields in an m:n table as PK without any difference.
The reason why it normally should NEVER be a combined index is exactly the physical sorting of the table. If you have m:n-tables they often changes its contents, and each time you change or add data to such tables it would be for example (in order of creating) "2,3 / 4,2 / 1,3" (each one a pair of IDs in an m:n table). So if you use a PK on both columns the table will be resorted as "1,3 / 2,3 / 4,2" which is not needed for any reason - no one ever looks into such a table and wants such sort order. But if you add an additional PK (although the PK itself is mostly never used for an own relation) as autonumber you have a PK which never changes , the only exception would be a deletion of a row.

Another reason why combined columns as PK are mostly a bad idea is that you always would need both columns (in an 1:n relation) to uniquely identify the wanted row in the other table, that's unnecessary overhead.

In real life scenarios it most often is the case: If you do not have a single column with a PK that never changes and has in most cases no "natural" meaning (exception would be i.e. a CustomerID) you will always add one later in development of an application/database. It's even often the case that you would create an artificial additional ID with a meaning although you already have an autonumber ID in cases where you must make sure that the created unique number has no gap in the numbering like invoice numbers where a finance office would not be satisfied if you have a gap in the number and cannot explain why this number never was an invoice number because someone started to add a record, the ID was created and he has undone that - the ID will not be used again in an autonumber field.

I also sometimes thought about using a meaningful attribute as PK when it is sure to be unique, but in most cases I regret that later because of the stated reasons and so I nowadays don't think much about PKs and always create a long integer autonumber field for each and any table and have never problems doing so.

Cheers,

Christian
1

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
Applies to: Access 95 and up     Level: Intermediate.

Ever had one of your Access databases stop executing code correctly or not want to compile?  Or are unable to create a MDE no matter what?  Or do other strange things related to code?   An undocumented command line switch exists within Access that may help.

Access uses VBA (Visual Basic for Applications) for its code support. The VBA project file, which contains all the code for your Access application is stored by Access in a JET database and used when needed.  In that project file is a copy of the source code and also a copy of the same code in a compiled state (if you have compiled your application).  This is done so that at run time, code can be executed faster because the code has already been compiled.

========================================================
TIP: Always make sure your Access applications are compiled when released.  Your code will execute faster, your app will be snappy, and as a result you'll look better!  Also be aware that there are things that you can do in your application that will cause the app to become un-compiled at runtime (like switching a form into design mode and altering it).  If you want to make sure your app is always compiled, consider distributing it as a MDE; MDE's lack source code, so the DB must run in a compiled state.  There are drawbacks to MDE's though, so be aware of those issues when developing.
========================================================

Often …
6
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
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 48

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 48

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

I often hear the opinion that "Access isn't fit for serious applications". Finance is of course very serious... and extremely serious in Switzerland; needless to say that I never expect much business from that particular branch. The main reasons are of course security and volume. You don't want numbered account information travelling on a laptop, and you won't handle a million daily transactions or fifty thousand cash distributors with Access. Let's be serious.

But there are also many small financial institutions like fund managers, and clients of finance like pension funds. Their needs in terms of data management often comes down to a few dozen items, each with a couple of thousand numbers in a small selection of currencies, and only a few active users. They still hold the opinion that "Access can't handle it", even while happily attempting to do so with Excel...

When analysing a project in this context, it appears that the main, or only remaining problem is the difficulties in performing lookups. In a spreadsheet architecture, this is solved by dependency chains. Since financial historical data is very much static, the update chain of depending cells is rarely triggered, so the overall performance is good even when numerous complex lookup operations are involved. Not so with a database architecture, where all derived data is recomputed every time a query is run.

This article will show that the nature of financial …
4
LVL 58

Author Comment

by:harfang
Thank you for for the very fast "EE approved" and "Community Pick" awards, I didn't expect them, or at least not before I got some positive votes from the "was this question helpful" survey. I'm grateful to all "community members" for their approval and welcome.

The acting editor was again aikimark, who did an excellent job in reviewing the content and the language. More than language, in fact, because he pointed to some flaws in graphical semiology as well, when the charts and illustrations could be misunderstood. If you do understand the article at all, please join your thanks to mine for his time and his pertinence.

If the article also helped, please click 'yes' above. If it helped only a little, click only a little!

(°v°)
0

Microsoft Access

221K

Solutions

51K

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.