Microsoft Access

220K

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

Deploying a Microsoft Access application in a normal Windows environment is not difficult but takes a few steps. The method and script provided here will - literally - turn the process into a one-click process for the user, even in a Citrix environment.
0
Become a Microsoft Certified Solutions Expert
LVL 12
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

A common question in the Access topic area is how to pass values between forms or reports. These threads frequently evolve into a discussion on how to save values so they can be used throughout the application. This article will discuss some of those techniques.
3
LVL 24
Comment Utility
nicely written, Dale. Generally, I prefer database properties because they persist, and use a function to retrieve them that also defines them if they don't exist because maybe stuff was imported. Here is my code to set or get a property:
' set or change a database (or object) property
'~~~~~~~~~~~~~~~~~~~~~ Set_Property
Function Set_Property( _
   psPropName As String _
   , Optional pValue As Variant _
   , Optional pDataType As Long = 0 _
   , Optional obj As Object _
   , Optional bSkipMsg As Boolean = True _
   ) As Byte
's4p ... 130410, 160820, 170721
      
   ' PARAMETERS
   ' psPropName is the (database) property name to set
   ' optional:
   ' pValue is the value for the property
   ' pDataType is the Data Type: dbBoolean, dbLong, dbText, ...
   '   if not passed -- uses defaults
   ' bSkipMsg = True: don't give user feedback
   ' obj = database, field, tabledef, querydef,
   '   or other object with properties
   '   if obj is not specified, then CurrentDb is used
   '
   'EXAMPLE
   ' Call Set_Property("AppTitle", sAppTitle, dbText, db)
   '              where: sAppTitle is defined -- or a literal value
   '  call Set_Property("AllowAutoCorrect", true, dbBoolean, oControl)

   'set up Error Handler
   On Error GoTo Proc_Err
   
   Dim booRelease As Boolean
   booRelease = False
   
   If obj Is Nothing Then
      Set obj = CurrentDb
      booRelease = True
   End If
      
   'assume property is defined
   obj.Properties(psPropName) = pValue
   
proc_Done:
   On Error Resume Next
   If Not bSkipMsg Then
      MsgBox psPropName & " is " _
      & obj.Properties(psPropName) _
      & " for " & obj.Name, , "Done"
   End If

Proc_Exit:
   On Error Resume Next
   If booRelease = True Then
      Set obj = Nothing
   End If
   Exit Function
  
Proc_Err:
   'property is not defined
   obj.Properties.Append obj.CreateProperty( _
      psPropName, pDataType, pValue)
   Resume proc_Done
   Resume
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'
' get the value of a database (or object) property
' pass (optional) object to look somewhere other than CurrentDb
' pass (optional) default value
'~~~~~~~~~~~~~~~~~~~~~ Get_Property
Function Get_Property( _
   psPropName As String _
   , Optional obj As Object _
   , Optional pvDefaultValue As Variant _
   ) As Variant
's4p 8-9 ... 130831, 160820, 170721
    
   ' PARAMETERS
   ' REQUIRED
   '  psPropName is the (database) property name to return the value of
   ' OPTIONAL
   '  obj = database, field, tabledef, querydef,
   '   or other object with properties collection
   '   if obj is not specified, then CurrentDb is used
   '  pvDefaultValue is value to return if property cannot be read
   '
   'RETURNS
   ' Null (or pvDefaultValue) if property has no value or is not defined
   ' OR
   ' Value of property
    
   'EXAMPLE
   '  MyValue = Get_Property("MyDatabasePropertyName")
   
   On Error GoTo Proc_Err
   
   Dim booRelease As Boolean
   booRelease = False

   'initialize return value
   If Not IsNull(pvDefaultValue) Then
      Get_Property = pvDefaultValue
   Else
      Get_Property = Null
   End If
   
   On Error GoTo Proc_Exit
   
   If obj Is Nothing Then
      Set obj = CurrentDb
      booRelease = True
   End If
   
   Get_Property = obj.Properties(psPropName)
      
Proc_Exit:
   On Error Resume Next
   If booRelease = True Then
      Set obj = Nothing
   End If
   Exit Function
  
Proc_Err:
   Resume Proc_Exit
   Resume
End Function

Open in new window

1
LVL 52

Author Comment

by:Dale Fye
Comment Utility
Crystal,  I have similar code, thanks for posting that here.

About the only thing I store as database properties is AppTitle and AppVersion, beyond that, if I want to persist values from one session to another I will either use the registry, or use a table in the backend.

Dale
0
When converting amounts between different currencies, you need exchange rates. This article demonstrates how to obtain these from no less than nine different sources, either for free or - for intense or demanding use - for a fee.
0
Currency codes can be obtained from many sources. The modules here retrieve them from two sources: ISO itself (the definitive source) and Currency Converter API. Both methods will allow you to maintain a table of current currency codes.
1
Microsoft Access
If your application will be used by financial auditors, then never ever use the delete functions to remove incorrect documents from your ledgers because financial auditors will require to audit, even cancel or reject documents and reasons for their rejection.
0
LVL 57

Expert Comment

by:Ryan Chong
Comment Utility
It depends on local regulatory, financial data could be kept for at least N years before it can be purged. financial auditors will audit on the what, where, when, which, how of your data is being stored, modified, deleted.

the Confidentiality, Integrity, Availability (so called CIA) should always be taken into consideration for the information system that involving data.

fraud could also happen if the data integrity was compromised regardless it's whether delete query or flag tagging.

and it not only applied to MS Access but in general applied to other DBMS/ RDBMS as well.
0
Google Maps Distance
Many applications need to compute the road distance between two addresses. Numerous websites perform this function, but to build this feature into your application, you must use an API to call these features via the internet. This article discusses how I did so by using Google Maps API and VBA.
1
Generating sequential numbers is quite easy, but making them persistent to form updates, deletes, sorting, and filtering takes a little more. Here we will show how to accomplish this in several ways with either little or no code.
0
Generating random numbers is quite easy, but making them persistent to form updates, deletes, sorting, and filtering takes a little more work. Here we will show how to accomplish this with a few lines of code.
0
Are you ready for a Linked Table Manager (LTM): - with a sizable form - which identifies which connections are valid and how many tables are associated with the connection - which doesn't ask you to select the BE multiple times when you've identified tables from multiple sources to refresh?
5
LVL 25

Expert Comment

by:Andrew Leniart
Comment Utility
Hi Charlotte.

Andrew, my biggest pet peeve about Access training and "how-to" articles is that they address the tools in Access but rarely the underlying design principles that are essential in building a database.

Indeed. You make an excellent point, one that I hope Dale (author of this article) and other readers will take note of for future article submissions. Perhaps an article explaining just that? :) Yes, there are already many explanations to be found with Google.

The difficulty I see here is that the term "Database" is so broad that to a layman, can also sound such technical a term as to discourage the excitement of learning about it. A title like "Learn the underlying design principles involved in building a database" wouldn't likely attract my attention, nor dare I say it, most others who don't have a vested interest in learning Access or other related software.

Yet if I saw something I could relate to, like "Learn to organize your favorites so that all browsers can use them" and do that in Access, well, that might be something I'd click on and actually read. The trick is in finding a way to educate using a hook and subject matter that will entice people to want to read and want to learn more. That's why I think different ways of educating on the same topic is so important.

Take the question: What is a database?

If I "Google" that as kindly suggested to me earlier by Joe, then the first thing I get is that its "a structured set of data held in a computer, especially one that is accessible in various ways."

Sounds too much like programming to me and doesn't really entice me to continue digging in order to learn more. I think that's a core problem of why many don't turn to something like Access and use Excel instead - it's too much like programming.

It's only when the penny drops (and for me, it's by reading articles like this one out of need while editing) that you start to get more interested in digging further. Editing articles such as this one prompt me to ask the question, how can I relate what I do in Excel, to do it even better in something like Access?

I confess I have a vested interest in promoting for these types of articles, both as someone who wants to read them, and someone who gets to edit article submission here in a volunteer Page Editing capacity. We already have a ton of technical "how to" articles as you say, but I think that's a good thing and keep them coming I say!

Yet I think interestingly written articles that will attract the "Joe Averages" of the world, who use Excel to store information in mostly because it seems easier to come to terms with using, yet will never know until enticed to dig deeper just how much easier life could be by delving into the cryptic world of databases.

That's the type of "How to" article I'm trying to promote be written here. Is there a single way of doing that? I don't think so. I'm just trying to encourage readers who have the necessary knowledge to give it a go. Hope that makes sense? :)

Look for a book by Michael J Hernandez, "Database Design For Mere Mortals"

Thanks for the heads up. I'll most certainly do that.

Regards, Andrew
0
LVL 52

Author Comment

by:Dale Fye
Comment Utility
for those of you using the Linked Table Manager, the latest version (posted in the original article) is 1.14.07, which fixes several bugs, including one which caused the linking operation to fail if the path to the backend is > about 100 characters (has to do with the Title property of the FileDialog).

enjoy.
0
CA Premier Accounting Package
Copying and pasting of files will continue, at least for now. The front end MS Access 2016 and SQL Server 2016 back-end can be zipped together and emailed to clients like any other file without problems, I have thoroughly tested the detailed method below and can confirm that it works very well.
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

CA Premier Accounting Package
Migrating data tables from MS Access to SQL Server is not for faint-hearted people, you have to draw all your programming knowledge to a higher assumed expertise level. Be prepared to sort out programming issues at a higher level.
0
“The Microsoft Jet Database engine stopped the process…” while working with a Microsoft Access database. The database may be saved as MDB or ACCDB file depending on the Access version that you used to create the database file.
0
Microsoft Access

State of play:


Databases ensure data integrity and relationship through both primary and foreign keys, and MS Access is no exception.


Well, that's not completely true ...


  • A primary key should not be null.  - No problem, the NOT NULL constraint enforces that behavior
  • A primary key should be unique. No problem again, the UNIQUE constraint enforces that behavior


A primary key should be used only once, and this is where MS Access fails as the following sample will demonstrate:


  1. Create a table, add an ID column, set its data type to AutoNumber
  2. Now add a second column, the name and data type doesn't matter
  3. Add some data to the table (10 rows)
  4. Delete some rows in the middle and then delete the last row
  5. According to the 3rd primary key rule, deleted IDs should be lost, forever
  6. Now close your table, and compact the database.
  7. Open your table again and add a new row.


Surprise !! The Newly generated ID isn't 11 as we should expect, but 10 again (remember you deleted it?)


Why may you ask?


Microsoft Access internally keeps track of the last ID used, but this ID is reset to the max ID + 1 when you compact the database. This effectively breaks the 3rd primary key rule.


What can we do about this?


Data macros to the rescue


Since MS Access 2010, Microsoft introduced the Data Macro, which can perform various actions before inserting, deleting, updating data and after inserting and after updating data.


These macros (also known as Triggers in other database engines) are executed automatically whenever you add, insert or update data, manually, trough forms, or programatically. They are mostly used to validate data (i.e: ensure data integrity), but we can use them to generate a true ID number that will never - ever - be re-used, even if the database is compacted.


To achieve this, we first need a table that will keep track of the max ID is used. Also, we will need data macros that will pull the max ID before we add a new row, increase the max ID after we save a new row, and prevent any ID update.


So, in this example, create a table, name it IDtrackers


  1. Add a column, name it TableName, data type Short Text, don't allow empty strings, set it as a primary key.
  2. Add a column, name it maxID, data type Numeric long, don't allow null values.
  3. Add 1 row, values are "myTable" for the tableName column, and 0 for the maxID column.
  4. Create a second table named MyTable
  5. Add a column, name it ID, data type Numeric long, no null value, set it as a primary key.
  6. Add a column, name it data, data type doesn't matter.
  7. In the ribbon, on the Creation tab, choose the Create data macro button, and choose the 'Before update' event.
  8. Copy and paste the following code (hopefully, it becomes self-explanatory in the editor):


0
LVL 15

Author Comment

by:Fabrice Lambert
Comment Utility
@Bitsqueezer:
In regard to the fact that a primary key should be used only once:
Take an ID card as a sample, it have a unique number.
According to what you say, when the owner dies, the number is free to be used again.
So, the next person asking for an ID card will recieve the number of a dead. Hmmm, doesn't make sens to me.

Concerning performances issues, it depend on the database.

Concerning other database engines, the decision to use another database engine as a back-end is not the purpose of this article.

Concerning debugging, like with every new technologies, developpers will need to learn.
0
LVL 25

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Fabrice,

that's the old discussion about what to do with an ID. Normally, in a database the ID is not used for "real" purposes and then there is no problem with any ID. What you are talking about is a counter value like an invoice number - such numbers would never be used with an AutoID, that's not their purpose.
An AutoID should guarantee only that each ID is unique and each new ID is higher than the last because it is usually used as primary key which is also usally clustered index and so it should make sure that no physical data rearrange is needed when you compact as a clustered key is the physical order of the records.

To use a normal counter I would simply use a transaction, then add a new counter value in a counter table, get the ID (not the counter value) and store it in the table where I want to use it. Easy to follow and nothing can happen between these two actions.

Cheers,

Christian
0
It is not that simple from VBA to display a compressed help file as it should be. API calls and a few tricks are needed. This demo and full code will show you how.
0
CA Premier Accounting Package
The best software application must always have an error handling tool
0
LVL 24

Expert Comment

by:John Tsioumpris
Comment Utility
Even at novice level since you have gathered so much error information it would be better if you had it stored in a table so that you could easily edit/update.
I really liked your Analog Clock on the form...
0
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
0
Lotus Notes NSF to Outlook PST Converter
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
0
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
0
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
0
IT Pros Agree: AI and Machine Learning Key
LVL 1
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
1
CA Premier Accounting Package
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
0
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
0
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
2
LVL 52

Expert Comment

by:Dale Fye
Comment Utility
good article, Jim.
0
LVL 61

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Thanks Dale.

Jim.
0
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail on what to do to thwart those with bad intentions.
0
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007. A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL value.
0

Microsoft Access

220K

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.