Improve company productivity with a Business Account.Sign Up

x

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

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
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

NSF to 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
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 53

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
Free Tool: Site Down Detector
LVL 12
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
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
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
Get 10% Off Your First Squarespace Website
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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
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:mgirdler
Comment Utility
Thank you Helen. Just what I needed to solve printer selection issues...

Michael
0
 

Expert Comment

by:Denis Bill
Comment Utility
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
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 24
Comment Utility
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
Comment Utility
Thanks, this was a great stuff.
1
 

Expert Comment

by:Manish
Comment Utility
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

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.