Microsoft Access

219K

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

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
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

“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 11

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 24

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 22

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
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.
0
Cloud Class® Course: Microsoft Office 2010
LVL 12
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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
LVL 2
Comment Utility
This is perfect
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 50

Expert Comment

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

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
Microsoft Access
Implementing simple internal controls in the Microsoft Access application.
0
LVL 54

Expert Comment

by:Gustav Brock
Comment Utility
Please edit the article to hold the code in code blocks with normal indention to make it readable and allow for copy-paste.
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
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, this article addresses the 'improvement'...
0
Print Preview
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
0
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple. The final solution for this task involved creating a custom text parser and then reading the data via the SQL Server Bulk Insert command.
0
LVL 50

Author Comment

by:Dale Fye
Comment Utility
I recently ran into a more complicated CSV file which had instances where there were more than one comma embedded within the same set of quotes ("abc, def, ghi") and even found a couple where the comma was the last character inside the quotes, and the code shown above did not resolve the issue, so I modified my ParseCSV function to the following:

Public Function ParseCSV(SourceFile As Variant, DestFileName As Variant)

    Dim ReplaceWhat As String
    Dim ReplaceWith As String
    Dim strLine As String
    Dim strLineOut As String
    Dim aryLine() As String
    Dim SourcefileNum As Integer
    Dim DestfileNum As Integer
    Dim lngLoop As Integer
    Dim lngLineCount As Long
    Dim bEmbedded As Boolean
    
    Debug.Print "Start: "; Now()
    
    ReplaceWhat = ","
    ReplaceWith = " "
       
    SourcefileNum = FreeFile()
    Open SourceFile For Input As #SourcefileNum
       
    DestfileNum = FreeFile()
    Open DestFileName For Output As #DestfileNum
    
    'Loop through the source file, parse it, and reconstruct it, one line at a time
    While Not EOF(SourcefileNum)
        Line Input #SourcefileNum, strLine
        aryLine = Split(strLine, ",")
           
        strLineOut = ""
        bEmbedded = False
        For lngLoop = LBound(aryLine) To UBound(aryLine)
           
           If (bEmbedded = True) And (aryLine(lngLoop) = Chr$(34)) Then
               strLineOut = strLineOut & ReplaceWhat
               bEmbedded = False
           ElseIf Left(aryLine(lngLoop), 1) = Chr$(34) And _
               Right(aryLine(lngLoop), 1) = Chr$(34) Then
                aryLine(lngLoop) = Mid(aryLine(lngLoop), 2)
                If Len(aryLine(lngLoop)) > 0 Then
                    aryLine(lngLoop) = Left(aryLine(lngLoop), Len(aryLine(lngLoop)) - 1)
                End If
                aryLine(lngLoop) = Replace(aryLine(lngLoop), ReplaceWhat, ReplaceWith)
                strLineOut = strLineOut & aryLine(lngLoop) & ReplaceWhat
            ElseIf Left(aryLine(lngLoop), 1) = Chr$(34) And _
                   Right(aryLine(lngLoop), 1) <> Chr$(34) Then
                aryLine(lngLoop) = Mid(aryLine(lngLoop), 2)
                strLineOut = strLineOut & Replace(aryLine(lngLoop), ReplaceWhat, ReplaceWith)
                bEmbedded = True
            ElseIf Right(aryLine(lngLoop), 1) = Chr$(34) And _
                   Left(aryLine(lngLoop), 1) <> Chr$(34) Then
                aryLine(lngLoop) = Left(aryLine(lngLoop), Len(aryLine(lngLoop)) - 1)
                strLineOut = strLineOut _
                           & Replace(aryLine(lngLoop), ReplaceWhat, ReplaceWith) _
                           & ReplaceWhat
                bEmbedded = False
            ElseIf (bEmbedded = True) Or (lngLoop = UBound(aryLine)) Then
                strLineOut = strLineOut & Replace(aryLine(lngLoop), ReplaceWhat, ReplaceWith)
            Else
                strLineOut = strLineOut _
                           & Replace(aryLine(lngLoop), ReplaceWhat, ReplaceWith) _
                           & ReplaceWhat
            End If
        Next
           
        Print #DestfileNum, strLineOut
    
        lngLineCount = lngLineCount + 1
    Wend
    
    Debug.Print "Done: "; Now()
    Debug.Print "Lines: "; lngLineCount

    Close #DestfileNum
    Close #SourcefileNum
    
End Function

Open in new window

Hope this helps anyone that needs to use a similar process.
0
Cloud Class® Course: CompTIA Healthcare IT Tech
LVL 12
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
0
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
0

Expert Comment

by:Geoffrey Smith
Comment Utility
Thanks for putting this together.  I tried implementing in Excel and there seems to be a difference in the way that the FileSystemObject.OpenTextFile function works.  The parameter "ForWriting" is not valid in Excel; and need to specify 8 (which allows for appending to file).
0
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
2
Unlock VBA Project Password Excel File
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
0
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
1
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 handles schema table exceptions.
0
LVL 8

Author Comment

by:Annaliese Dell
Comment Utility
Hi Andrew! Thank you for your kind words and for editing the article. I don't know how I got along without QODBC in the offce. It would take volumes to document all the things I do with it but at least a few articles about the simple things might help a few people.
0

Microsoft Access

219K

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.