Microsoft Access





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

I am trying to change the backcolor of just one record being displayed in a continuous form with:

    If Me.txtQty1Select = "X" Then
        Me.txtQty1.BackColor = vbYellow
        Me.txtQty1.BackColor = vbWhite
    End If

Open in new window

But all of the txtQty1's are changing to vbYellow.  Is there a way to overcome this issue?
In ms access sometimes we make some tables as system tables like the ribbon table its always a system table, and its always hidden , can other people import system tables without first un hiding them.

I am trying to copy a sheet from source.xlsx and paste it on target.xlsx. I got "subscript out of range" error message. Please have a look
I need to run below code from MS Access database

Public Sub CopyWorkSheet()

Dim sh As Worksheet, wb As Workbook

Set wb = Workbooks("C:\ab\Target.xlsx")
For Each sh In Workbooks("C:\cd\Source.xlsx").Worksheets
   sh.Copy After:=wb.Sheets(1)
Next sh

End Sub

Open in new window

Thank you
need to add a leading zero to a string if it does not exist.

There are plenty of examples on the internet for this but,

I have a  textbox  that will get a value for example  888888890, 873646369,
always starting with an 8
HOWEVER, people are copying and pasting data from SAP, EXCEL, that may pose a problem.

Basically, i Need to pad the beginning of the string with a  <ZERO> if it does not exist.

Most folks will be pressing a command button after copying and pasting.

Can anyone suggest a good strategy for doing a regular compact & repair for multi-user databases? I'd like to do one perhaps daily but at least weekly. However, a lot of the users leave themselves signed in to the database overnight so getting exclusive use of it virtually never happens.

My only idea is to use code to kick out the users at, say, midnight and then do a scheduled compact & repair at 1 AM or something. But before I implement that, I was curious if there is a better approach.

Thanks in advance!
I want to be able to call a Sub in an Access form from a control on a ribbon. I have no trouble calling Subs which are in "standard" modules but I can't figure out how to call a sub in a form module. Can this be done? If so, how?

Thank you.
I want to create a form in Access 2010 where I can enter information in English, then press a button which will show that information translated into another language. Is there a way that I can do this directly in Access?
Access 2010 Translator Sample Form
I have a control on a form header that counts values using control source = =Count(IIf([StatusID]=8,1,Null)).  I need it to count based on unique values from another field.

For example, using the table below, the Count = 14.  However, I need the count to equal the "unique" values of ProviderID which should be 12.

I can do it with SQL:  SELECT COUNT(DISTINCT ProviderID) FROM tbl WHERE StatusID = 8.  Do I have to create a function and use it as the control source?  Or do I have to create a query and link it to the form's underlying query?  I need the count for several StatusIDs, not just 8.
I need to create a txt file for a client from my Access 2013 application. The file will contain a header record of length 21, 1 or more detail records of length 36 and a trailer record of length 26.

I have all of the logic in place to create the records and I am currently storing them, in the order created (hdr, detail....., trailer), in a local table (tblExportBatch_Work_Local )with one field 'ExportText' of length 36.

To create my output file I now use

DoCmd.TransferText acExportFixed, _
                   "WRBPayExportSpec_Trimmed", _
                   "qryExportPaymentBatch_TrimmedSpaces", _
                   wkOutputFileName, _

'wkOutputFileName' is the name I want to assign to the created output file.

It works but the client does not want spaces at the end of each header and trailer record.  As it stands now all records in the output file are 36 characters in length.

Is there any way to accomplish outputting the record without spaces after the header and trailer using the transfertext command?

Perhaps I have to change my methodology.  I've seen but never used other methods of creating a text file using something like
  fHandle = FreeFile
  Open Output_File For Append As #fHandle
                Print #fHandle, wkHeaderRec

                              Print #fHandle, wkDetailRec  (the detail write is in a loop)

                Print #fHandle, wkTrailerRec
Close #fHandle

Will this new method create the …
Hi Experts,

I have the following code that processes files and then writes to a log file the file name and time .

Public Sub CallImportDataToCaspio()
   Dim StrFile As String, strTable As String, sFileStr As String
   Dim sDir As String
   Dim l As Long, s As String, i As Long
   Dim db As Database
   Set db = CurrentDb
   sDir = "E:\AppDev\FTP\Caspio\"
   'sDir = "E:\AppDev\FTP\Caspio\"
    'StrFile = Dir(sDir & "*PatChanges*")
    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0
        If Not IsFileOpen(sDir & StrFile) Then
            ''        Do While IsFileOpen(sDir & StrFile)
            ''            ' do nothing
            ''        Loop
            If InStr(1, StrFile, "Full") = 0 And InStr(1, StrFile, "Part") = 0 Then
                i = CountOfRecords(sDir, StrFile)
                If i > 1 Then
                    'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                    If InStr(1, StrFile, "PatChanges") > 0 Then
                        strTable = "Patients"
                        l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                    ElseIf InStr(1, StrFile, "SchChanges") > 0 Then
                        strTable = "Schedule"
                        l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                    ElseIf InStr(1, StrFile, "CGChanges") > 0 Then
                        strTable = "Caregivers"
                        l = ImportDataToCaspio(strTable, sDir & 

Open in new window

I have a string variable that contains XML data from an API call. I can easily write it out to an XML file and import it into my Access database using:\

     Application.ImportXML sFileName, acStructureAndData

However, the API call returns sensitive data that I'd rather not write to a file. Yes, I know I can kill the file immediately afterwards, but I'd like to avoid writing the file, if possible.

If I have the XML in a string or variant variable, is there an easy way to push it into a table?
In Access 2016/2010 I use the VBA code below to get last modified date of a file - and it works fine, excep when it comes to mdb files on a Sharepoint server - all other file extensions are fine, but not when it comes to mdb files - anyone know a reason and a workaround for this ? Same scenario if I use the built-in  FileDateTime function.

Maybe a built-in feature of /%¤/%¤ Sharepoint, as I see a lot of similar questions, but no solutions:

Function GetDateModified(strPath As String) As String
    Dim fso As Scripting.FileSystemObject
    Dim file As Scripting.file

    Set fso = New Scripting.FileSystemObject
    Set file = fso.GetFile(strPath)
    GetDateModified = file.DateLastModified
    Set fso = Nothing
    Set file = Nothing
End Function

Open in new window

Need to apply a custom number format in Access VBA like this:

     lngCAN = Format(!CAN, ["18-"0000])

I am getting "Compile error: External name not defined"

The variable "lngCAN" is a number (1,2,3 ...) and the desire is to write "18-0001" and so on.
I have a table with 8 text fields that contain names.  IE; Lead Instructor - Instructor 2 - Instructor 3 - Instructor 4 - Monitor 1 - Monitor 2 - Monitor 3 - Monitor 4. I need to create a table that contains all of these fields in a single text field, "Name".

Hope I am clear.
So please note the images and the code I'm trying to run -

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, sTable, sFileLink, True, sTable

Open in new window

How can I overcome this to ensure the file is linked to MS Access?
The 1st image is the error and the second image is where this error occurs - what the values are in the general window and proof that the file and the folder all exists properly.

What am I going to do to make this work properly?
Interesting side fact - if I could trust that MS Access would refresh the link of the Excel file when I save over it at times... then I wouldn't need to delete and then re-link it... but I don't trust it and rather start from scratch.   However I don't think as long back as VBA existed - that the link function truly worked and properly why I went with the import feature over link.
Need help with syntax of query to avoid error:  Data type mismatch in criteria expression.
Please note attached image file of query being ran that gives an error.

This is a simple table with two fields that have Short Text as the data type.
I'm trying to do a between date after it's converted using CDate - what am I please doing wrong?

Please especially note the last sample - img with the values of data it's converting also.  This originally was a flat Excel file out of SQL Server with the date columns as an example:  2011-06-30 00:00:00.0000000

Perhaps I need to parse out just the date without the time first ?   If so , what's the most efficient means to do this?
Windows 64Bit -- Access 64Bit -- GhostScript 64Bit

Trying to Use GhostScript 64Bit Exec to Merge Two PDF files... I am NOT familar with Ghostscript commands inside shell file... I AM familar with VBA.

I have this code so far... my code DOES find the 64Bit Windows GScript exe... it does not give an error but it does not merge these files...

Trying to test merge my1.pdf + my2.pdf to Merged.pdf

I think/AM PRETTY SURE the output part of GScript syntax with file names is wrong for Ghostscript...

I have seen this web page...

Public Function GS()

  Dim gsdir As String, gsscript As String
  gsdir = "C:\Program Files\gs\gs9.27\"
  gsscript = gsdir & "bin\gswin64c.exe -dQUIET -dNOPAUSE -sDEVICE=pdfwrite -dBATCH -sOutputFile=C:\Users\projectmgr2\Documents\Merged.PDF my1.pdf my2.pdf"
   Shell (gsscript)
End Function

Open in new window

Hi Experts,
I have three questions about Access 2016
1).  I tried to import queries, forms, reports from Access 2007 to Access 2016, I notice that queries randomly loss fields or criteria, event I tried from Access 2016 to Access 2016, it still has the same issue, did I do something incorrectly?
2).  When I used Linked Table Manager in Access 2007, it has "Always prompt for new location" option that I found it very useful but in Access 2016, it doesn't have that option, is this hide in somewhere?  
3). on the reference in Access 2016, what's the difference between:
     Microsoft ActiveX Data objects 2.0 Library
     Microsoft ActiveX Data objects 2.1 Library
    Microsoft ActiveX Data objects 2.5 Library
    Microsoft ActiveX Data objects 2.6 Library
   Microsoft ActiveX Data objects 2.7 Library
   Microsoft ActiveX Data objects 2.8 Library
   Microsoft ActiveX Data objects 6.1 Library
 I have check the "Microsoft ActiveX Data objects 2.1 Library" in Access 2007, which one I should use on Access 2016 or it doesn't matter.  I currently check the "Microsoft ActiveX Data objects 2.0 Library" on the Access 2016
How can I stop the error msg in MS Access
view to keep my original header labels on field names

I have a subform where in the header i have labels for my field names. The textboxes are in the Detail section.
The form is set to continuous form in the properties.

I'm running this command to change the view
DoCmd.RunCommand acCmdSubformDatasheet
But it will not keep my label names when changing the view ?

I need to keep the label names from the header section.

i would like to add sequential numbering to a filed headquarters at runtime in a ms access query (vba)
I created a query to run data by date. the date field type  is a txt, not date/time. I added Cdate before the date that I want to convert to date, however, some of the dates are not converting correctly. the data I am running is from Jan to July. the query result is showing date for December. it looks like the July 12 date is being see as December 7h., the April 8 data is showing as August 4th........

SELECT Tbl_Patient_Details.ID, CDate([ED_Arrival_Time]) AS A
FROM Tbl_Patient_Details;
I have created my balance sheet using sub reports for both current year and prior year same period dates as well as the income statement. Now all what I want is to have the following sub reports have equal number of rows so that the sub totals are equally aligned.

Sub report A (2019)
Sub report B (2018)

For example if Sub report A (2019) has 4 rows and Sub report B (2018) has 2 rows then in Sub report B (2018) tow extra rows with ZEROS must be create to much the Sub report A (2019). For sure some code on load event of the report is required but I do not know how to do it.

See example the balance sheets in question

Access 2016 Option Group Control.  I have setup an option group to select either Single, Married or Divorced.  The default value is Single.  When the frmPersonalAddress is first opened Single is selected indicated by the "Black Dot".  Problem #1 the value at this point is "1" not "Single".  Problem #2 if I then select Married the value becomes "Married" as it should BUT the "Black Dot" is nowhere to be seen.  If I then click Divorced or Single again the correct value is assigned but no "Black Dot"  The code for the option group and the form are below.  I have included a screenshot of the initial screen and then after I selected Married.  The second Marital Status below the option group is there for troubleshoot and will be removed.

Option Compare Database
Option Explicit

Private Sub Form_Current()
     Select Case optMStatus.Value
          Case "Single"
               MaritalStatus.Value = 1
          Case "Married"
               MaritalStatus.Value = 2
          Case "Divorced"
               MaritalStatus.Value = 3              
     End Select
End Sub

Private Sub optMStatus_AfterUpdate()
     Select Case optMStatus.Value    
          Case 1
               MaritalStatus = "Single"
          Case 2
               MaritalStatus = "Married"
          Case 3
               MaritalStatus = "Divorced"    
     End Select
End Sub
Access 32Bit Working Fine 3 Years... Trying To Convert to 64Bit... Not Working

In 64Bit Code the fail is on the LoadLibrary 64Bit function it does not load the library in the 64 vs the 32 code...

have all the dills in all the right places same for both and when i debug the CurrentDB dir is correct for Lib to load... color me confused.

Access 32Bit Code (working)
Option Explicit

Public Declare Function MergePDFDocuments Lib "StrStorage.dll" _
    (ByVal PDFMaster As String, _
    ByVal PDFChild As String _
    ) As Boolean
Private Declare Function FreeLibrary Lib "kernel32" _
(ByVal hLibModule As Long) As Long

Private Declare Function LoadLibrary Lib "kernel32" _
Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long

'Instance returned from LoadLibrary calls
Private hLibDynaPDF As Long
Private hLibStrStorage As Long

Public Function LoadLib() As Boolean
Dim s As String
Dim blRet As Boolean, A As String

On Error Resume Next

' *** Please Note ***
' If you are going to process many reports at once then to improve performance you
' should only call LoadLib once.

' May 16/2008
' Always look in the folder where this MDB resides First before checking the System folder.

LoadLib = False

' If we aready loaded then free the library
If hLibDynaPDF <> 0 Then
    hLibDynaPDF = FreeLibrary(hLibDynaPDF)
End If

' Our error string
s = "Sorry...cannot find the DynaPDF.dll

Open in new window


Microsoft Access





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.