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 understand MS Access vba code within MS Access but not familiar with interfacing with other applications such as internet explorer.  Trying to automate user tasks.  I have developed code based on internet research and my close but not there.

This is what the user currently has to do and what we are trying to accomplish:

1.  Fill out the Container field with a text string (need to automate the pass of this value); I have done this in code below

2.  Press RETURN on keyboard which then reloads the web form to show a drop down list for the Mfg Order field.  This list has checkboxes on the left side with corresponding text values on the right side (need to refresh this reload); don't know how to do this and this appear to be the crutch of solving the problem in order to get to 3. below.

3.  The user then clicks only one checkbox of interest (need to automate the pass of this value)

4. Click on the ViewReport button (need to automated this)

Eg of Container value is "07016216" (this value is captured from my MS Access Form; just hardcoded below)
Eg of Mfg Order value to select is "ZMCSVC177759" (remember, this drop down list can have many values which the user has to pick only one from the drop down list; this value is captured from my MS Access Form; just hardcoded below)

Many thanks!!!!!

Code below:

Public Function cam()
     'make sure you add references to Microsoft Internet Controls (shdocvw.dll) and
     'Microsoft HTML object …
I have this DSsum Code now because it is too slow I want to replace it with SQL code , but Im not sure how to do it:

Running Total : DSum("Taxable"," Qrytaxes" ," [EmpID] = &[ EmpID]& " AND [TTDate]<=#" &[TTDate]& " #" )

Now how do I write a valid SQL statement?


There are two tables, one for Student and one for Borrowed Books. In the Microsoft Access (2010, 2013), it is easy to display a form Student based on the table Student, and other form BorrowedBooks based on the table Borrowed Books with their Record Source pointed.

How to do Form BorrowedBooks showing records for the current student showing on the Form Student one at one time? I'm looking to learn both VBA script and using the built-in controls to achieve the result.

Relationship between has been built. And it's one student to many books relationship.
Using docmd.openform( argument ), with a non-null argument...when single step debugger to the open form it shows the argument as null. Anyone seen things like this? If so, please help!
I'm working on a Delphi program which I'd like to link to a online database which should be accessible on any computer with the Delphi program running.

is it possible to link, and where can the database be hosted ,also how should the code look ?
I have an access 2010 application distributed via the packaging wizard. It works well except that shortcut menus are nos available in forms.
For example, there is no way to copy or paste with shortcut menus.
In the source file they are available.
What should I do to make them available in the distributed package?
Thanks in advance.
I have an access 2017 database that is linked to SharePoint List.  I have a form that list individual records within the database.  Normal you can either page down, click in the scroll bar or click on the block in the scroll bar and drag it down.  However, now when you click on the block and drag it down it does nothing.   No records move.  If you click on the scroll bar itself it resets back to the previous point.  I have attached a pdf file for those who need more of a visual like me.  Your help is appreciated.  This function has previously worked.
I have just started to work with WPF using and have had trouble getting the DataGrid to work with the Access Database (Jet) that holds my data.  I learn best through examples and I would be grateful if someone can give me sample code (both XAML and VB) to solve the following situation.

I have a DataGrid as follows:
            <DataGrid Name="dgPayment" Margin="10,15,10,10" Grid.Column="0" AutoGenerateColumns="False"
                      IsReadOnly="False" CanUserAddRows="True" CanUserDeleteRows="True" >
                    <DataGridTextColumn Header="Seq" Binding="{Binding Path=CL_Seq}" />
                    <DataGridTextColumn Header="Name" Binding="{Binding Path=CL_Name}" Width="200" />
                    <DataGridTextColumn Header="Hdr1" Binding="{Binding Path=CL_Hdr1}" />
                    <DataGridTextColumn Header="Hdr2" Binding="{Binding Path=CL_Hdr2}" />
                    <DataGridTextColumn Header="Hdr3" Binding="{Binding Path=CL_Hdr3}" />

I have code behind as follows:
            Using conn = CashConnection
I am getting error access denied in a workflow by the system ID
I am getting a SP 2010 workflow to cancel with error access denied by the system ID. Users have correct permission, I am a site collection admin and it does not happen to me but to the users who submit a request by a custom InfoPath form to a list with 2 custom workflows attached. The first workflow is the one that is not being triggered but instead is cancelled. When I manually run the item, the 2 workflows complete.
Hello Experts!

I know a long time question has been how to lock or hide or password protect an Access table.  Since I use a table to hold permissions (username and password), I've been searching for a way to prevent someone from creating a blank access database, then importing tables so they can then see users' passwords.  

It looks like this dude found the answer:

One of Data-Man's responses: "none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects"  is exactly what I'm looking for.

Anyone know how he did it?  Encryption/decryption vba code perhaps?  If so, can anyone send me a sample database?

Thanks All!
I have an EventsName field , and start date , end date and items lists fields.
I have make a crosstab which display for me the EventsName in a columns and give me as a value a sum of the items  in each EventsName.
as i know there is NO way to edit these value in crosstab.
i make a subform and put the crosstab as a source object . (because the columns in the crosstab is changing dynamically)
is there a way i can connect the main form with the subform of the crosstab that can help me give information from the subform and send to the main form which help editing ?
I am having trouble adding rows to a pop up menu which the count reaches 8.

Adding menu like this:      

   If Not (m_objCommandBar Is Nothing) Then
            Set m_objCommandBar = Nothing
        End If
        'Add a menu item for each record returned, only if items are returned
        Dim objConnection As New ADODB.Connection
        Dim objRecordset As New ADODB.Recordset
        Dim intMenuId As Integer
        Dim mnuItem As Office.CommandBarButton
        Dim strMenuCaption As String
        Dim strMenuParameter As String
        Set objConnection = CurrentProject.Connection
        intMenuId = 1
        objRecordset.Open strSQL, objConnection, adOpenKeyset, adLockOptimistic
        If (objRecordset.RecordCount > 0) Then
            Set m_objCommandBar = Application.CommandBars.add("InventoryMenu" & Now(), msoBarPopup, , True)
            Do While Not objRecordset.EOF
                    strMenuCaption = "Job: " & objRecordset("Job_Num").Value & ", Qty: " & objRecordset("Quantity").Value
                    strMenuParameter = "J|" & objRecordset("Job_Num").Value
                    Set mnuItem = m_objCommandBar.Controls.add(msoControlButton,  intMenuId , strMenuParameter, , True)
                With mnuItem
                    .Caption = strMenuCaption
                    .enabled = True
I am new to Microsoft Access and I've created a form with four columns; one for reasons, other two are TimeIn and TimeOut and Fourth Column is the difference between TimeIn and TimeOut. Now I would like to create a Pivot chart which shows reasons on the x axis and Sum of the fourth column for every reason on the y axis. Please help me with this.
Hi All,

I have a Run-time Error '2105' (You can't go to the specified record) in the event builder.
This is my code

    Option Compare Database

Private Sub Command123_Click()

End Sub

    Private Sub Form_Load()
    Dim str As String
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
    sSQL = "SELECT distinct(ID)  FROM tblMembers"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL)
    MemberID.RowSource = ""
      Do While Not rs.EOF
       MemberID.AddItem (rs!ID)
    Set rs = Nothing
    Set db = Nothing
    End Sub

    Private Sub MemberID_Change()
    Dim strID As Integer
    Dim strName As String
    strID = CInt(MemberID.Text)
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
    sSQL = "SELECT *  FROM tblMembers s WHERE = " & strID
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL)

    If rs.RecordCount > 0 Then
     txtFullName.Value = rs!Title & " " & rs!Fname & " " & rs!Mname & " " & rs!SurName
       txtFullName.Value = "Record Not found"
    End If
    Set rs = Nothing
    Set db = Nothing

End Sub

Thank you
I have a client server Access application and recently we moved the SQL to Azure. Now the TransferDatabase command doesn't work due to datetime2 field in SQL.

I used the following VBA Code to export an access table to Azure SQL:

DoCmd.TransferDatabase acExport, "ODBC Database", "ODBC;Driver=ODBC Driver 13 for SQL server;Server=*******;Database=****;Uid=***;Pwd=***;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;", acTable, "MyAccessTestTable", "ServerTestTable", , False

Then I get the following error from Access:

"Microsoft Access was unable to append all the data to the table. The contents of fields in 1 records(s) were deleted, and 0 records(s) were lost due to key violations. *If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table...."

My access table has only one field [CreateDate] with value of (7/7/2017 11:31:34 AM). After checking with SQL, the table is created and it is empty. The date field is Datetime2 field type.

It seems that when I export the table, Azure SQL create Datetime2 type by default and it is causing the problem. I tested other tables without the datefield and export works fine. Is there a setting in Azure SQL to default the new datetime type to datetime instead of Datetime2 when I export a table? Or maybe a setting in Access?

Thanks for any suggestions.
Here is the scenario:
We mail catalogs to consumers and I am trying to make an access database to show projected sales by week based on a sales distribution curve. So in one table I have:
Catalog Table
Catalog Name: (Spring Sale Book)
Catalog Projected Sales per Catalog: ($3.0)
Circulation number: (300,000)
Date to be mailed: (2/20/2018)

Then I have a catalog curve table that houses the % of sales projected each week.
Catalog Curve
Week: (1,2,3...etc)
Percent: (3.5,15, 23...etc.)

So I am trying to create a query that lists each catalog and the amount of sales projected for each week on a calendar. The result looking similar to the simple attachment.

The columns: week of year
Rows: catalog name
Values: a formula= sales per book * circulation * curve percentage for that specific week

I'm struggling on how to even go about this. Ideas?

"system resources exceeded"
Does any one have an idea were this is coming from??
I have a Word mail merge program that runs through selected members of a certain table where the date sent is empty. It prints receipts for donors and in the body of the program it enters the date sent into the table so that receipts won't be printed for the same donation again.

It used to work fine and printed the data only once. Recently, it has printed the whole current set of receipts but once it completed and even after the program was closed for a short period, it suddenly starts printing the whole set again.

What can be causing this problem?

How can I determine the data set that is the source of the data for the print of the program which was developed a long time ago?
I am trying to implement cascading combo boxes using the method 2 described in this EE article;

Basically passing the first combo box value to the second using a parameter query and sorting combo 2 using this parameter.

I have my access tables linked using a DSN less connection with SQL Native Client 11 to the SQL Server.

I get an "ODBC Call error" when I use the parameter in queries based on these linked tables.
I tried using Clng(paramerter) but his makes no difference.

I have searched online extensively but cannot find a specific solution  for "odbc call error" when using parameters in queries based on linked tables.
I have a form with 2 (continuous) subforms.
Both subforms have 2 cascading (dependent) combo boxes which run independently very well by utilizing a parameter and union query for the dependent combo
SQL Below:
First Combo "CbReqZone"
SELECT tb_Zones.ZoneID, tb_Zones.ZoneCode, tb_Zones.Description
FROM tb_Zones
ORDER BY tb_Zones.[ZoneCode];

2nd Combo "CbReqLocation"
SELECT  LocationID, Location, ZoneID, CbReqZone=ZoneID As InZone  FROM tb_Locations  UNION SELECT Null, '————————————', CbReqZone, False  FROM tb_Locations
ORDER BY InZone, Location;

They are synchronized with code in theire After Update events as follows:

1st Combo:
Private Sub CbReqZone_AfterUpdate()
Me.CbReqLoc.Value = 0
End Sub

2nd Combo:
Private Sub CbReqLoc_AfterUpdate()
If IsNull(Me.CbReqLoc) Or Me.CbReqLoc = "" Then 'No action required
    Me.CbReqZone = Me.CbReqLoc.Column(2)
End If
End Sub

Everything works very well in the first subform but the 2nd subform won't allow an existing record to be edited by the combo boxes.
The error I get is Update or Cancel Update without addnew or edit.
Just acn't understand it happens in form and not the other, both are doing exectly the same but for different fields in different underlying tables.

Here is the 2nd set of combos and after update events for the 2nd subform

1st Combo "CbDestinZone"
SELECT tb_Zones.ZoneID, tb_Zones.ZoneCode, …
I want a text box and a combo box to be a repeatable grouped unit of fields in a form please.

How should I go about this?

With many thanks!
I developed a very simple Db for a client to replace an older app.  I used my PC, running Win 10 PRO and Access 2016 full version.  The same app runs OK on the clients Win 10 PRO machine with Access 2016 runtime EXCEPT when running any report, I get a fatal exception and the app has to close.  I tried 32b and 64b 2013, 2016 versions, all the same result on the client's machine.  Any ideas?  I don't want to load a full version on the client machine, set the code, try it then uninstall it - just yet.  Not if there is a "head slapping" DOH! I am missing.
I had this question after viewing How do I extend the length of a form in MS-Access.
I have a document with multiple tables.  The next to last table needs to have the width of the 3rd column set so that it is the width of the text in the column so that the single element in the column doesn't get cut across 2 lines.  For example, one row has something like
C2AJ_YZ-10-AB-002.  Currently, this column cuts the string after the first hyphen, but I'd like the column to fit the text in one row.
   My first issue is how to access the correct table.  (This table will always be the next to last one.)  Then, how do I set the width of the whole column to be the same width as the text in that column?

Chris Cote
Hi Experts. I have three textboxes which only show when a button is checked then subsequently the values in the text boxes are summed up. My existing solution is returning empty using   ![Bill] = Nz((Me.txtPharmAmt1 + Me.txtPharmAmt2+ Me.txtPharmAmt3), 0).

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.