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

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.
3
LVL 54

Expert Comment

by:Dale Fye
good article, Jim.
0
LVL 62

Author Comment

by:Jim Dettman (EE MVE)
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 59

Expert Comment

by:Gustav Brock
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 54

Author Comment

by:Dale Fye
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
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:Marc Zosiac
What about zipping two or more files in the same destination?
0
LVL 59

Author Comment

by:Gustav Brock
You mean into the same destination?
That it cannot do. That would require an array or collection to be passed as parameter and/or acceptance of wildcards.

But you could copy the files to a temp folder, then zip that folder to the location where you wish the zip to be finally located - though that zip would contain the temp folder and its files.

I'll add it to my (long) to-do list.
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

Expert Comment

by:Member_2_7967726
You wrote "Access 2010 (and maybe later versions)". Have you had the possibility to proove that behaviour with Access 2013 or later meanwhile? Maybe the problems don't exist there any more?
0
LVL 25

Author Comment

by:Bitsqueezer
Hi,

no, I cannot say if that problem still exists. As the problem came up in a large project with a lot of modules it may also not appear in smaller projects. But on the other hand Microsoft did not change much in VBA since years so it is very likely that the problem still exists (and also because interfaces are not so much known/used by VBA programmers).

Cheers,

Christian
0
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 9

Author Comment

by:Annaliese Dell
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
Quickbooks
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
0
Dynamic Printer Selection
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with the Access and Word Printers collections.
1

Expert Comment

by:Denis Bill
Excellent! I was finding the solution of printer selection issue. I found this article and followed steps as given. And finally got success. Such a great article.
Dell Customer Service
0

Expert Comment

by:Factory reset Hp laptop
Hello, printing a report or a Word document filled with Access data.  For your own computer, you can hard-code the printer choice, after getting a list of the printer names as used in code.
0
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
2
Pictures from URLs
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retrieve the pictures automatically from the URLs?
6
LVL 25
excellent article, gustav -- very thorough. Thank you.
0
Split MS Access Database
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and keep database secure from corruption.
4

Expert Comment

by:Shan G
Thanks, this was a great stuff.
1

Expert Comment

by:John
Thanks, this was a comprehensive article.
Many no. of times user fails to repair corrupted MDB file with Compact & Repair utility . This is the phase where user might get stuck. In such condition User may try 3rd Party Access database repair tool to fix MS Access database corruption error.
1
Windows Phone Theme Colours
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
3

Expert Comment

by:John Clark
Gustav,

I adapted my application to this and I could not believe how efficient and easy this was to do. I see myself deleting a HUGE amount of redundant code and using Class Module in the future. They have always intimidated me and quite frankly, I did not see the use of them until I needed something similar to what you did here. The link you provided to John Colby's Blog was spot-on and VERY informative.

A question on this method however, the particular functionality I am trying to achieve applies to Text and Combo boxes. Reading JC's Blog led me to believe that I would need another Class to handle each type of Object.

Did I comprehend that correctly or can I include more than one Object in this Class?

Great stuff!

John
0
LVL 59

Author Comment

by:Gustav Brock
Yes, you implement many classes, perhaps one for each type of control.

Or you can modify this check:

    If Control.ControlType = acTextBox Then

to check for other control types as well. And then either apply the same or different methods.

Or do both. Or add more conditions; for example only apply a method to a control with a control source.

/gustav
0
Merge to Word
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and installed automatically, for .mdb and .accdb databases.
0

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the many intricate ways privileged accounts can compromise Active Directory environments.


On the subject of “Tracking and Securing Privileged Users in Active Directory”, Derek Melber, technical evangelist for the ADSolutions team at ManageEngine, outlined that number as Microsoft's own observation.


That’s why companies like ManageEngine are working to educate users and provide simple-to-use tools for protecting the popular Active Directory infrastructure.


Melber explained that when companies are breached, they usually aren’t aware of the breach for up to 146 days. That means a hacker can be in your organization with domain administrator credentials, undetected, for 5 months—something Melber appropriately described as a “terrifying level of access.” According to Microsoft’s research timeline, when the first host is compromised (typically a desktop) the admin domain credentials are compromised in two days or less.


So how do companies combat these risks and stay ahead of hackers?


Melber said a great place to start is to follow these 5 steps for tracking and securing privileged credentials:


  1. Run reports on privileged access accounts
  2. Analyze data from these reports
  3. Configure settings
  4. Monitor settings and access
  5. Set up alerts for when access changes


These steps help companies follow the practice of creating a least privileged environment, something ManageEngine believes in. Following this for all endpoints, Melber explained companies can reduce vulnerabilities within Internet Explorer by 100%.


Individual privileged accounts, however, aren’t the only thing to monitor. Melber discussed the importance of following the same protocol with privileged groups. In privileged groups, users have uninhibited access to important files. He gave the example of a privileged group member accessing financial servers and backing up files or folders, regardless of the permissions set on those documents.


In order to audit this activity, tools are needed to run reports and control access. With the right tool, Melber says it’s possible to track access, monitor settings and behaviors, configure password resets, receive real-time alerts, and launch automatic reports.


“It all goes back, unfortunately, to breaches. Attackers are one step ahead of us. Attackers are using configurations against us. We need to flip that around. We need to know who has privileges. We can then help reduce the breaches that are in our environment,” says Melber.


For more details on tips provided in this webinar—or to watch the presentation—click here.


*Please email Derek Melber with any Active Directory questions at derek@manageengine.com


3
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
1
LVL 25
very nice, Dale.  If there is only need create a file, is there any performance advantage of this method over GetObject? thanks ~ crystal
0
LVL 54

Author Comment

by:Dale Fye
Crystal,

GetObject will only get the first open Excel application.  If you have multiple Excel sessions running you cannot use GetObject to identify each of them.

Dale
0
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will discuss how to use VBA to automate Excel.
2

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.