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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening. This article shows how to create and use them.
0
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are given for Office versions from 2003 through 2016.
0

Expert Comment

by:Roy Luce
Suggest adding a notation regarding hidden files.

Spent over an hour this morning trying to locate Normal.dotm in Word 2016.  
It took an hour because the AppData folder in C:\Users\[User Name]\AppData\Roaming\Microsoft\Templates is a hidden folder.  
Only after finding and changing the display option for hidden folders was I able to locate and update Normal.dotm
0

Expert Comment

by:Kali
I am still having problems accessing the created templates using Word 2013.  I understand we need to direct the File Locations to the proper directory, which I have done.  Now what?  I can't see any way to access these files AS templates.  We could do this in Word 2007 and Word 2010, but NOT in Word 2013.  I do not know how to properly access them.

Thank you for your help.
0
AutoNumbers should increment automatically, without duplicates. But sometimes something goes wrong, and the next AutoNumber value is a duplicate. This article shows how to recover from this problem.
0
LVL 31

Author Comment

by:Helen Feddema
Will do.
0
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.
1
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
1
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
3
Brown Paper Envelope on Table
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challenge.
1
Silver and Gold Coins
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
1
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
1
CC0 License
Some of you might know that emails by design have two types of sending and receiving addresses. Yes, you read that right – it’s not just the one you see on your email.

The first type of sending address is the envelope sender which is not visible to the user on their mail client or even in the headers. You can co-relate this with a physical letter, which after being put in an envelope has the address on the outside which is used by the courier to deliver the envelope to the intended destination. Similarly, the envelope sender is used for routing purposes on the Internet.

The second type of sending address is the header From address. This is the one you see displayed on your email or in the headers as From: address. In our metaphor, this would be the address you write on the letter itself. In real life, the envelope can be addressed to A and the letter can be addressed to B. Similarly, email does not require the envelope sender to match the header From address.

So the email that you see coming in from friend@yourdomain.com could easily be from hacker@bigbadwolf.com .

This is one of the ways the bad guys send out CEO Fraud emails or as termed by the FBI, ‘Business E-Mail Compromise’. The emails almost always originate from an external source. This means that the envelope sender is from an external domain but the header From address is spoofed to look like it is coming from your domain. There are very simple steps you can follow when using our email filtering to …
1
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
1
LVL 54

Expert Comment

by:Dale Fye
Jim,

Great article.  I use a similar process in a procedure I use for reading from or writing to Excel.  The problem is that occassionally, the user might want to read from or write to a workbook which is already open, and if the user has more than one instances of Excel open it is vertually impossible to select the correct version.  With a method very similar to this, I am able to identify all available instances of Excel and allow the user to select the appropriate instance based on the workbook(s) opened in those instances.

Dale
0
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
1
Detailed instructions on how to install an Access add-in in recent versions of Office and Windows (with screen shots)
0
Access 2007 introduced the new Properties FilterOnLoad and OrderOnLoad with default values of TRUE. This changed the default behavior of forms. Dealing with this default in mixed Ribbon/non-Ribbon environments was a pain...until now.
6
Professional Access developers know that applications should never expose database objects directly to users. Unfortunately, the tendency of the NavPain [sic] to become visible when merely importing or linking to external files, even after having been hidden, is infuriating.
13
LVL 54

Author Comment

by:Dale Fye
Nick,

The db vs. Currentdb issue has been around for a while, but I think you've got it backwards.  If my recollection is correct, the Currentdb reference will always force Access to refresh the various object collections, while the db version creates a static object based on the Currentdb when the db object is instantiated.

Whenever I'm doing loops in code that reference a database object, I will generally declare the db object and use it, but in cases like this, I don't think it is necessary.

Another point about using db vs Currentdb is that if you are working in the immediate window, use the db method, otherwise, references to various database objects will loose their scope.
0
LVL 54

Author Comment

by:Dale Fye
Nick,

I hope this is useful, I know you seemed really frustrated with the NavPain during that thread we were working on together the other day.

Dale
0
DatabaseModeling
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
1
LVL 67

Expert Comment

by:Jim Horn
Excellent work, voted yes.
0
LVL 25

Author Comment

by:Bitsqueezer
Hi Jim,

thanks for publishing the article and the upvoting!

Christian
0
This article will show you how to use shortcut menus in the Access run-time environment.
1
LVL 14

Expert Comment

by:Bill Ross
Hi Dale,

Do you have code to duplicate the built-in commandbars?  I'm interested in  the Text Filters and Number Filters that leads the users to a dialog box for input.  I've got the Sort and other items done, just the sub-menu process is stumping me.

Thanks,

Bill
0
If several users try to update the same record simultaneously, an error pops up asking what to do. That's fine, users know what to do. Contrary, if two processes driven from code do the same, there is no one to handle the situation, and it fails. That's bad. Here is a method to avoid this.
8
LVL 59

Author Comment

by:Gustav Brock
Updated to version 1.0.2 with extended error handling in SetEdit to prevent an endless loop in case of other errors than a concurrent edit.

/gustav
0
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user and avoid errors.
1
LVL 95

Expert Comment

by:David Johnson, CD
Depends on the street - there is a world outside the US Try and explain it to the full 73 percent of American citizens are incapable of identifying their home country on a map of the United States. :->
http://recoilmag.com/poll-73-percent-of-americans-unable-to-locate-america-on-map-of-america-2/
0
LVL 59

Author Comment

by:Gustav Brock
Great. On the other hand, extremely few Europeans can point out all the states of the US. Most can label Hawaii, Alaska, California, and Florida, perhaps Texas, but that's it.

/gustav
0
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take you a long step deploying successfully.
2
LVL 67

Expert Comment

by:Jim Horn
Nice article, voted yes.   I think this article can be improved if the script were rendered in pieces-parts code blocks, with helpful text explanation on what the script does, so that experts can get an understanding and manipulate if they have to.
0
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching all errors.
5
LVL 59

Author Comment

by:Gustav Brock
Thanks. Have a nice weekend!

/gustav
0
LVL 67

Expert Comment

by:Jim Horn
Nice job walking through code and explaining it, and thanks for attaching examples.  Voting Yes.
0
Tired of manually adjusting the width of query columns so you can see all the data? This quick solution will ensure your query columns are the right size every time.
1
LVL 67

Expert Comment

by:Jim Horn
Some supporting images would really help here, as the article speaks to cosmetics.
1

Expert Comment

by:Bill McKay
I needed this exact solution.  I used your code Paul and in 5 minutes it was humming along nicely!  Thank you sir!
1
This collection of functions covers all the normal rounding methods of just about any numeric value - at extreme precision.
3
LVL 59

Author Comment

by:Gustav Brock
Finally I have these assembled. Through the years bits and pieces were collected, but now I had the option for a rewrite.

/gustav

PS: There are minor flaws in the layout but the editor is somewhat strange so I couldn't get it completely straight.
0
LVL 67

Expert Comment

by:Jim Horn
Nicely laid out.  Voted Yes.
0
Background
What I'm presenting in this article is the result of 2 conditions in my work area:
  1. We have a SQL Server production environment but no development or test environment; and
  2. We have an MS Access front end using tables in SQL Server but we are not using any other components of SQL Server (i.e., we are not utilizing stored procedures, views, table-valued functions, etc.).
Without these 2 conditions, I would not present this article as a possible work around.  Even without the second condition, if you have a development or test environment, I would recommend using those environments instead of what is presented in the article.  Also, I acknowledge that there may be several other alternatives to what I'm presenting but recognize that's usually the case with any challenge we face.

Why Develop this VBA
I have created a MS Access database front end using SQL Server as the backend for data storage only.  Although eventually I'd like to run all my processes through stored procedures in SQL Server so that the front end platform could be changed, the volume of data is small enough to run my processes through MS Access VBA.  Also, my experience with SQL Server is progressing but not to the point of being able to quickly write the code in the time needed to complete this project.  So, I'll come back to writing the code later.

We issue the MS Access front end to members of our team through an …
0

Expert Comment

by:Paul Rudy
Good job. Quite useful to create an off line copy of a SQL back end database to used during a network or server outage.
0

Introduction:

Have you ever been given the advice NOT to use domain functions inside queries?  I have, and I have repeated that advice over and over again; why?  The main reason is that this process is inherently slow.

But what if you absolutely need that value in your dataset?  I recently ran into this problem, and no matter what I tried, I could not get a particular field into a dataset and keep the rest of the recordset updateable, without using a DLookup().  This was extremely frustrating for users of the application, because it took close to 30 seconds for the datasheet to finish "calculating" every time the form was loaded or refreshed (this with only about 450 records). 

There are two primary reasons for the slowness associated with including domain functions in queries:
1.  Every call to a domain function requires that a new recordset be created using the table you want to grab data from and using the critieria you provided.  This is the same thing that happens when you use a correlated subquery (sample below) in your queries.
SELECT A.ID, A.Description,
(SELECT B.SomeValue FROM B WHERE B.SomeID = A.ID) as SomeValue
FROM A

Open in new window

2.  When you use a domain function (DLookup, DMax, DMin, DSum) or a user defined function in your query, the query parser is unable to see inside the function, so it has no means of determining the optimal execution plan.  This is the main reason you don't want to use Domain functions (or UDF's that return data) in …
6
LVL 67

Expert Comment

by:Jim Horn
Nice job.  Voting Yes.
0
LVL 15

Administrative Comment

by:Eric AKA Netminder
Dale,

The Page Editors have determined that this article is worthy of Awarded status.

Congratulations!

ericpete
Page Editor
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.