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

Getting an error message: 'Microsoft Outlook - Sorry, something went wrong.  You may want to try again'

This only appears when sending to one specific client from MS Access, all other client emails work fine.

Any ideas how I could fix this?
I am not sure where to place my coding. I have a text box is being used as a less or greater than statement to pull values from two fields to filter data on the next page or form.

I put the following statement in the new page where it suppose to filter the data, written as following and I have criteria set in query that is making the list box display blank.

Private Sub Form_LOad()
        If Len(Form_002_Criteria.TxtExp & vbNullString) > 0 Then
            Me.Filter = "ExpMin <= " & Form_002_Criteria.TxtExp & " AND ExpMax >= " & Form_002_Criteria.TxtExp
            Me.FilterOn = True
            Me.Filter = ""
            Me.FilterOn = False
        End If
End Sub

Open in new window

I have a continuous form where I have a bound checkbox which I want to use for allowing users to select records and press a button to open a form with the selected records. After the second form is open then I clear the checkboxes. The form works perfectly when one user is using it, but when several users are using it does not work properly- different users' selections are mix and matched. How can I solve this problem?
I get this error when i try to connect an access database on the network to WinSQL

First time working with Access. I have a form that has 176k records. Basically is actually 9810 records 545x18. Then the data is being multiple again by another table is 176k. There 18 locations that have 545 records each. The selection form has 2 combo box and a list box. The first combo you choose a region, then it cascades to the second combo box to show locations, and last it cascades from the 2nd combo to the list more specific locations  to choose. When I click the submit button to go on to new form or page it filters from 176k to 9810, when it suppose to filter to 545. Is what I did so far

Is there any codes that someone can create as an example?
While writing a custom ribbon for Access, I'm having troubles removing 2 elementsin the info tab of the backstage. See attached image, I would like elements circled in red to be removed.

RibbonCreator did not help much as it hardly support the backstage view (or is that just the shareware ?), and isMso provided by Ribbon Wizard were frowned upon by Access (GroupFileProperties and GroupFileLocation).

Below, the XML I obtained:
<customUI xmlns="">
         <command idMso="Help" enabled="false"/>
    <ribbon startFromScratch="true"/>
         <tab idMso="TabInfo" visible="true">
                   <group idMso="GroupDatabaseCompact"/>
                   <group idMso="GroupDatabaseEncryption" visible="false"/>
                   <group idMso="GroupFileProperties" visible="false"/>
                   <group idMso="GroupFileLocation" visible="false"/>
         <tab idMso="TabOfficeStart" visible="false"/>
         <tab idMso="TabRecent" visible="false"/>
         <button idMso="FileSave" visible="false"/>
         <tab idMso="TabSave" visible="false"/>
         <tab idMso="TabPrint" visible="false"/>
         <button idMso="FileCloseDatabase" visible="false"/>
         <tab idMso="TabHelp" visible="false"/>
         <button idMso="ApplicationOptionsDialog" visible="true"/>

Open in new window


I have a subform in my form. In this subform I have several columns. In first one I have a combo field filled with data from a table A. When I choose a particular value from this combo I'd like to display appropriate value in the next column in my subform as a text field. This value is also from table A. How I can obtain?
Here is my data:

| OEMCurrentPartNumber | OEMDescription       | OEMSubNumber |
| 19M7796              | SCREW                |              |
| 19M8365              | USE PN 19M7796       | 19M7796      |
| AT256104             | USE PN 19M7796       | 19M7796      |
| CH12356              | USE PN 19M7796       | 19M7796      |
| M134162              | USE PN 19M7796       | 19M7796      |
| M74138               | USE PN 19M7796       | 19M7796      |
| AE42460              | BEARING WITH HOUSING |              |
| DA25307              | USE PN AE42460       | AE42460      |
| AH223030             | USE PN AH227807      | AH227807     |
| AH227807             | FRAME                |              |
| AH208565             | USE PN AH223030      | AH223030     |

Open in new window

| OEMCurrentPartNumber | OEMDescription       | OEMSubNumber |
| 19M7796              | SCREW                | 19M7796      |
| 19M7796              | SCREW                | 19M8365      |
| 19M7796              | SCREW                | AT256104     |
| 19M7796              | SCREW                | AT257892     |
| 19M7796              | SCREW                | CH10599      |
| 19M7796              | SCREW                | CH12356      |
| 19M7796              | SCREW                | M134162      |
| AE42460              | BEARING WITH HOUSING | DA25307      |
| AE42460              | BEARING WITH HOUSING | AE42460      |
| AH227807             | FRAME                | AH223030     |
| AH227807             | FRAME                | AH208565     |
| AH227807             | FRAME                | AH227807     |

Open in new window

| Item       | OEMItem  | Description  |
| AM19M7796  | 19M7796  | Screw        |
| AMAE42460  | AE42460  | Bearing Assy |
| AMAH227807 | AH227807 | Frame        |

Open in new window

The purpose
I am cross referencing OEM part numbers with my companies part numbers.

In the RAW_OEM_DATA table, any rows without a number in OEMSubNumber are the "latest" numbers. All other numbers are sub numbers that reference the "latest" .

The [OEMItem] in COMPANY DATA is the "latest" OEM number. when a user searches a part number my query searches the OEMSubNumber column and brings back the OEMCurrentPartNumber to match with the [OEMItem] in my companies numbers.

To get the raw OEM data to the format in the FINAL_OEM_DATA is what i'm trying to achieve. But I don't need all 1.6 million records in that table. just the ones that match the [OEMItem] from COMPANY_DATA.

Final Result
Both the RAW_OEM_DATA and COMPANY_DATA tables reside on my web server so employees can access via the secure website. It is a aspx site with MS SQL server. Twice a year i have to run my companies new item numbers through this process and update my tables with the new data.

Couple of notes
Some numbers in RAW_OEM_DATA "piggy back" one another. example
AH208565 subs to AH223030 which subs to AH227807
That means not all [OEMSubNumber] will be the "latest" OEM number.

The description for the "latest" OEM number has to replace all the "USE PN ***" numbers

My process in the past
run quite a few queries to get the OEM raw data to the above desired result

I am running into quite an unusual problem and was wondering if anyone has experienced this issue before. I have an ActiveX Control that I have made that uses a Picture Box and displays an image from Azure blob storage by converting that image's memory stream into an image. I use this ActiveX Control for a Microsoft Access 2002 application. This ActiveX Control works great on forms, but on reports it doesn't display fully (Please see attached images). Anyone know why?
Dear All,

I have a few questions regarding Microsoft Access reports (I am using 2016, but the same issues exist in 2013).

i) let's say I have created a report using the report wizard and when prompted I selected stepped for its layout. I have created and saved it, but my boss doesn't like it and he wants to use outline instead; I could not find a way of changing the layout, it seems to be only possible only through the Report Wizard or do I need VBA (or is it just not possible)?
ii) I have created a report with groups and I want the report header to be with the first group and the report footer on the same page after the last group. I am using Force New Page equals none for the group header and after each session for the group footer. This does the trick for the header but not for the footer. is there any way this can be achieved?
iii) (form question) when someone selects Justified as the form layout, would anyone be able to tell me where is the default value for the form's width (there doesn't seem to be anything like for Access, would that be in the Registry)? Also, if you have Justified layout and you change the width of the textfields, it looks like Access is not picking up automatically the new dimensions, but you would have to close and redo the form - any idea why this is or am I doing something horribly wrong?

Is there a way to NOT ALLOW a user to add to the dictionary when they run spell-check?  In other words disable the [Add] button?

Spell Check
I need help with updating my form coding on Microsoft Access to make sure everything works properly. I would prefer if someone remotely connects to my computer.
My software has an internal email module and I've figured out how to send mail via SMTP and to include attachments and of course the client wants to see what attachments were sent.

The problem is that SMTP sends the attachment by virtue of setting a path to the file being sent, and my software, being an MS Access application, works with document attachments via the OLE field and the Insert Object command to load the document into a MYSQL LongBlob field via a form with a OLE field.

So my table that stores any attachments to the email has two fields, a text field for the path the user selected to send the attachment via SMTP, and a LongBlob/OLE field the user has to make a second effort using right click, "Insert Object," and then browsing to hopefully the same file, thus having to browse twice for an attachment if they want it saved after sending via SMTP.

I built integration with MS Outlook, which would solve the issue for this client, but this client is using the 64 bit version of MS Office 2013, and my Office 365 64 bit Office version of Access seems to have updated to Office 16 when I wasn't looking and that broke my Outlook integration from working with their 64 bit Access 2013 runtime installations when I deploy the 64 bit version.

Not the end of the world, but I was wondering if anyone can think of a clever way to make this into a single attachment browse effort by either:

1. Exposing the path of the OLE Object after Inserting it into the text field that …
I have an Access application compiled in 2010.  In one part of the code it opens another database also compiled in 2010 and saved as an accde file.  to open the second application I am using  this approach.
Dim accapp as Access.Application
Set accapp = New Access.Application
accapp.OpenCurrentDatabase PathToAccdeFile

This works well on most workstations on our network, but we have a few where the accde file is attempting to load in Office 365 and we are getting all kinds of errors because 365 isn't fully loaded on that machine and so of  course the user isn't signed into an Office 365 account.
How do I remove all references to 365 so that  Access will load the application in 2010?  The accde file type is already associated with Office 2010.
I want to insert the following data with SQL into an Access table, but I have problems with the German date format:

 If Not IsNull(txtDateTo) Then
    sql = "INSERT INTO tblKeyEmployees (kem_KeyID_FK, kem_EmployeeID_FK, kem_DateFrom, kem_DateTo) " _
       & "VALUES (" & Me.lstKeys & "," & Me.lstEmployees & ", #" & Format(txtDateFrom, "yyyy/mm/dd") & "#, #" & Format(txtDateTo, "yyyy/mm/dd") & "#)"

        sql = "INSERT INTO tblKeyEmployees (kem_KeyID_FK, kem_EmployeeID_FK, kem_DateFrom) " _
        & "VALUES (" & Me.lstKeys & "," & Me.lstEmployees & ", #" & Format(txtDateFrom, "yyyy/mm/dd") & "#)"
 End If
        Dim dbs As Database
        Set dbs = CurrentDb
        Debug.Print sql
        ' Select all records in the New Customers table
        ' and add them to the Customers table.
        dbs.Execute sql

Open in new window

This is the output of the SQL debug line

INSERT INTO tblKeyEmployees (kem_KeyID_FK, kem_EmployeeID_FK, kem_DateFrom) VALUES (1,3271, #2018.11.16#)

Open in new window

I have tried so many options now. What shall I do in order to properly format the German date version ( to a SQL compatible date?
Running Access 2013.  Creating custom Ribbons.  Trying to streamline one of my forms that has a tab control on the form, by removing the actual  Tabs on the TabControl and setting focus to the correct tab as the user clicks the Tab on the ribbon. Not a button, but just the Tab above the buttons on the ribbon.
This is the XML for the ribbon and it works fine.  I just needs the proper way to insert an "onAction" to allow the function to run which sets focus to the correct tab.  FYI: The functions work fine too as I have already tested them with a button.

<customUI xmlns="">
<ribbon startFromScratch="true">
<tab idMso="TabCreate" visible="false" />
<tab idMso="TabExternalData" visible="false" />
<tab idMso="TabDatabaseTools" visible="false" />
<tab id="VendorsTab" label="Vendors" visible="true">

@@@@  NEED A STATEMENT HERE THAT CALLS "Set Tab1 Focus"   @@@@@@

<group id="VendorGroup" label="Purchase Orders"  visible="true">
         <button id="CreatePO" label="New Purchase Order"  imageMso="QueryShowTable" size="large" onAction="=CreateNewPO()" />
         <button id="FindPO" label="Find Purchase Order"   imageMso="FindDialog" size="large" onAction="=FindPurchaseOrder()" />
<group id="POGroup" label="Vendor"  visible="true">
         <button id="EditVendor" label="Edit Vendor"  imageMso="FileCloseDatabase" size="large" onAction="=EditVendor()" />
         <button id="NewVendor" …
I have an Access application that can export fields to a PDF file.  However, for this work it needs the full version of Adobe installed.  Not just the reader.  So I was wondering if there was a way to check programmatically to see if Adobe Acrobat was loaded and not just the reader.

Thanks John
I have attachment files, of various types, which have been converted to binary format and stored within my SQL Server database table.

I need to convert the binary file data to Base 64. Does anyone have a coding module written in VBA which can accomplish this?
Hi, I am creating an Access database and want to export my data to MS Excel Templates.  I can export the data to excel and in the code format the spreadsheet however, I have 30+ templates I want to utilize and formatting all of them through code would be extremely time consuming.  Is there a way I can utilize my existing templates and export the data to them.  Or is there a way I can capture the formatting of each MS Excel file and copy it into the code?  Thank you, Jeff
I would like to add a a condition to this,

While the user is creating a new record, there is a field on the form called "ServiceType"  if they select 'Legal' I would like to append a "L" at the end of the FileNo, else
leave everything the same as below

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.FileNo = Year(Date) & "1" & Format(Me.InvoiceID, "00000")
End If
End Sub
I am getting "Error 3017 Cannot update. Database or object is read only" when trying to export a table to Excel. I use the same method that I use in other places which always works as expected.
DoCmd.TransferSpreadsheet _
            TransferType:=acExport, _
            tableName:="table1", _

The only difference in my current Sub is that I am opening a record set to get a name for the file. This routine is in a loop because I will be creating multiple Excel files, each with a different name. This is what that looks like:

Dim sCompany As String
Dim sMyPath As String
Dim sReportName As String
sCompany = rsCompany(0)
sMyPath = "T:\MyFolder\"
sFileName = sCompany & "Filename"

DoCmd.TransferSpreadsheet _
            TransferType:=acExport, _
            tableName:="Table1", _
            FileName:= sMyPath & sFileName, _

If I hard code FileName:= then it works without getting Error 3027. but written as above (sMyPath & sFileName) or any variation of that and it fails.

What am I doing wrong?
What can be reason to the below error, while it only happens when I deployed the same project to https (everything is fine if I deploy it to http)?

Server Error in '/RegRec2' Application.
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 

[InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.]
   System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) +258
   System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +497
   System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +100

Open in new window

I have a Microsoft access application that i am trying to port to use the sqlserver express database in the back end instead of tables within the application.  I changed all of the tables in the access database to attached tables and for the most part everything works tickity boo.

I do have an issue on some of my code where i am trying to update a field.

set db = currentdb
set rst = db.openrecordset("select * from  inventory where customerid = 1 and productid = 10",dbopendynaset, dbseechanges)
with rst
     .fields("itemamt") = 33
end with
set rst = nothing

Open in new window

this code works fine except when it does the update, sql server adds a key lock on the database and doesn't release it

later in my code i go to update something else and whey i go to open the recordset (querying on the inventory table again) it locks and eventually returns an error

Has anyone else seen this behavour with linked sql server tables?  is there a way to force the lock to disappear when i close the recordset?
access 2016 show same fields but different rows/data on same page report
I have a table in Access with 3 columns: first is transactionID, the second is textline, 3rd is msg.  check example below pls            
transactionID      textline      msg
564231      3      hello
564231      4      hi
564231      5      hello
564231      9      hi
564202      1      hello
564202      2      hi
564202      8      hello
564202      22      hi
513830      7      hello
513830      10      hi
513830      13      hello
I would like the result to be as follows            
Return the transactionID, textline, msg for  those with consecutive textlines per transactionID            
transactionID      textline      msg
564231      3      hello
564231      4      hi
564231      5      hello
564202      1      hello
564202      2      hi

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.