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

Hi there,

I made a small win32 db-applicatie for warehouse for education a couple of years ago (Delphi 2007), using ADO en msaccess db.
Since a couple of months two things happen with the application:

1 slow start up and slow shut down in win10. In DELPHI IDE it 'hangs' on olepro32.dll.
2 i can't reproduce but some win10 user get errors (external exception / access violations)

I read something about how MS after june 2017 updates dll's.
Could that have anything to do with this?

Every help appreciated.
Is there a possible way of counting only the credits C of John only and not C in the whole table/query? Please help. Thank you a lot in advance!

I am working on a project (MS ACCESS 2007 for the forms) that will allow a user to select a report, Fill out the necessary parameters have the data extracted from a Stored Procedure in MS SQL 2014 then placed in Excel.  I have this working for the most part; however, a user can only run one report and then must exit MS Access and re-open to run a second report.    I think this might be because the variables are not being closed out properly (At least I thought they were)

Here is the code I am using to close the connection to Excel from within a VBA module in MS ACCESS

Dim ADORec As New ADODB.Recordset

Dim mySQL

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim x, y, z
DoCmd.Echo True, "Creating GLOBAL connections to the database."
Call SetGlobalSettings

DoCmd.Echo True, "Connection complete."
    Set ADOCom.ActiveConnection = ADOConn
    ADOCom.CommandType = adCmdText
    ADOCom.CommandText = "EXEC BMH_DS_WIDGET '" & myWidget & "'"
    ADORec.CursorLocation = adUseClient
    DoCmd.Echo True, "Running Stored Procedure BMH_DS_WIDGET '" & myWidget & "'"
    Set ADORec = ADOCom.Execute

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    DoCmd.Echo True, "Creating Excel Worksheet"

................ code to format and get the data.

    xlApp.DisplayAlerts = False

Open in new window

Please can someone give me the VBA code to create an email using Groupwise, and then OPEN the newly created email and add the recipient by hand, and body text by hand.

I have trawled the internet for an answer to this, and everyone has the code to create an email, but I have not found anyway to simply open the newly created message and then send it manually once it has been edited and spell checked.
I am using Access in office 365 and sql server 2014

I have a unbound text box where I have written updating code based on field values. I also created a button to copy the code to a clip board which I then copy into a module. Is there a way to copy the code straight to module? Or copy the code to a sql stored procedure?? If it is possible. How?
I am on a Windows 10 machine with MS Access 2016 trying to connect to MS SQL Server 2017 on Centos 7 Linux.  I have an SSH connection via Putty.  I tried creating a DSN alone first, but don't have any luck.  I don't see any examples on how to setup a DSN for SQL 2017.  I have tried working on this for a week and am approaching a deadline.  Any help is appreciated
I have a Microsoft Access report set up to print labels. The desired font is: TImes New Roman, Size 6.5, Bold.
I am printing to a Zebra GX420t thermal printer which prints one label at a time.
As soon as I drop the font size below 8, it still prints, but it is no longer bold.

I have another Access report set to print the same labels to a sheet in a laser printer. It prints at 6.5 without a problem.

I can also print 6.5 - bold to the thermal printer from a Word document.

I've been tweaking printer settings all morning without making a difference. Anyone have an idea why this won't work?
I am running sql server 2016.  How can I create a linked server to a MS Access database?
Hi there,
- file copy manually from explorer on local PC and paste manually - in RDP session - in explorer on server works OK.
- file copy manually from explorer on local PC and paste with vba on local PC works OK. (Function SaveFilesFromClipboard(Dest...))
- file copy manually from explorer on local PC and paste with vba - in RDP session - says: Clipboard empty!

- Settings in RDP session clipboard are set to 'yes' and local drive is shared.
- Users may not explore the server. It has to be done programmatically, files must be checked on extension and size.
- MS Access 2016, server 2012R2 with MS Access runtime

What do I miss?
Thanks in advance.

Public Function SaveFilesFromClipboard(DestinationFolder As String) As Boolean
On Error GoTo Err_SaveFilesFromClipboard
    SaveFilesFromClipboard = False
    If Not CBool(IsClipboardFormatAvailable(CF_HDROP)) Then Exit Function
    CreateObject("Shell.Application").Namespace(CVar(DestinationFolder)).self.InvokeVerb "Paste"
    SaveFilesFromClipboard = True
    Exit Function
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_SaveFilesFromClipboard
End Function
So we were having problems with database corruption of an access database due to what I believe to be too many users accessing it simultaneously.  I decided to split the database to a SQL backend.  Since doing it everything is working perfectly except one search on the form.  This search is searching against 3 columns to find the entry that matches a specific entry.  The search works but if the result is over ~7,000 on the recordset then it will cause access to go Not Responding and hand for 20ish seconds.  

Based on some google searching I get the feeling I'm going to have to find a different way to perform this search due rs.FindFirst not playing nicely with sql backend DBs.

Any help would be appreciated.  Code for the search is included.

Private Sub FindByPlot_Click()
On Error GoTo Err_FindByPlot_Click

    FindSection = Me.FindSection1.Value
    FindLot = Me.FindLot1.Value
    FindSpace = Me.FindSpace1.Value

    Set rs = Me.Recordset.Clone
    If FindSection <> "" Then
        If FindLot <> "" Then
            If FindSpace1 <> "" Then
                rs.FindFirst "[Section] = '" & FindSection & "' AND [Lot] = '" & FindLot & "' AND [Space] = '" & FindSpace & "'"
                rs.FindFirst "[Section] = '" & FindSection & "' AND [Lot] = '" & FindLot & "'"
            End If
            rs.FindFirst "[Section] = '" & FindSection & "'"
        End If
        Exit Sub
    End If
    If Not rs.EOF Then Me.Bookmark 

Open in new window

I am using Access 2013, SharePoint 2013. I have an excel file that is auto-rewritten every morning. I then link that Excel file to Access and query the data since it is not in a very clear format. The Access query for this is "WISE_IMRQ_Filtered". Within the same database "WISE_IMRQ" I have another Access table "WISE-IMRQ" that is linked to SharePoint. Both Access tables are formatted the same and I am looking for VBA to copy the "WISE_IMRQ_Filtered" table and paste (overwrite the previous) it into the "WISE_IMRQ" table, BUT still maintain the link to SharePoint so that everything is updated daily. Thanks for any help.
Hello.  Names are made simple to follow.  I have a form (FormA) that is a continuous form.  Each record row has a button at the end called 'Review' that opens another continuous form (FormB).  FormB is a many to FormA's one.   So the PK of FormA is the FK to FormB.  I have tried ways in opening FormB as you can see from the 2 lines below.  

        DoCmd.OpenForm "frmFormB", , , "ID=" & Me.txtID  (the ID is on FormB while the txtID is from FormA)
'        DoCmd.OpenForm "frmFormB", , , , , , Me.txtID   (same here but in FormB form load is listing the FK field = me.openargs)

The FormB recordsource is the table (tblB).  I did invoke a query and had [Forms]![FormA]!txtID as the where for the FK.  That got me no where.

I am either getting mixed up or I'm missing the steps necessary to accomplish this.

What is the proper way to open FormB from FormA's button that will show in FormB continuous form only those records for the FormA record of choice?

Thanks.... John
Ex i have a column 1  qty column2 price how can i add all the qty in a textbox and also the price how can i total it and put in a txtbox

Total qty=
Total sales=

We are working on building a application for IOT (internet of things) using Arduino and SmartcardWifi.
Arduino constantly sends the data to the website

Using C# we want to access the stream of data from the  website and process it further.  Need C# code which can lets us capture the stream of data and allows us to store it in database.
I KEEP Getting a type conversion error when I run an Update query that calls a function. All fields are defined as string.

Public Function getFirstName(InName As String) As String
Dim ctr As Integer
Dim char As String
    For ctr = 1 To Len(InName)
        If Mid(InName, ctr, 1) = "," Then
           InName = Mid(InName, (ctr + 2), (Len(InName) - (ctr + 1)))
           getFirstName = InName
        End If
     Next ctr
End Function
Can't fix this query:
InstantsQuantity: Sum(IIf([eventstatusC]="future" And [Forms]![frmAddItems1]![txtDateFromTime]>([EventDateFrom]+[EventDateFromtime]) And [Forms]![frmAddItems1]![txtDateFromTime]<([EventDateUntil]+[EventDateuntilTime]) Or [Forms]![frmAddItems1]![txtDateUntilTime]>([EventDateFrom]+[EventDateFromtime]) And [Forms]![frmAddItems1]![txtDateUntilTime]<([EventDateUntil]+[EventDateuntilTime]),[RentalInnerProductQuantity],0))
Shift are of four types.: A,B,C,G
After 6 days Off.
How to make this database so that at the end of the month we check there attendance. Shift changes aftet every 2 days.
I am creating an inventory database to keep track of hosts in the company building. If a host is scrapped, I can tick the tick box in the 'Scrapped?' field for the host in question and it will appear in the scrapped hosts query. How would I go about automatically populating the field 'Date Scrapped' with today's date, so for future reference I don't need to input the date myself.

Thanks in advance if you can help

I have inherited an old database which uses a MS Access Frontend to Import data into a SQL Backend via SSIS package .
It appears the connections to SSIS in the following Access DB Vb script fails as it only complies with TLS1.0 and not TLS1.2 as desired when using web based access.
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=MySQLData;Initial Catalog= MyProject;User ID=MySSISImport; Password= XXXXX;"

To resolve this to be TLS1.2 compliant should I be looking for
1- A security patch for SSIS
2- Re-write connection string an alternative way

I am posting this question as a follow up to the question at this link:

I have a form with two unbound combo boxes, one for the course and one for the preferred session of that course.  Each are located in the header of a continuous form which displays each request submission that has not been assigned to a session.  The user is able to filter the list based upon both the class and the preferred session.  

I need to add the cascading functionality so that if the user filters the list by course only the preferred sessions for the requests for that particular course show up in the preferred sessions filter combo.

Here is the SQL I have for each combo box:


SELECT qryReqPendingAssignment.Course, tblCourses.CourseName FROM tblCourses INNER JOIN qryReqPendingAssignment ON tblCourses.CourseID = qryReqPendingAssignment.Course UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblCourses.CourseName;

Preferred Session

SELECT qryReqPendingAssignment.PreferredSession, tblEvents.EventDesc FROM tblEvents INNER JOIN qryReqPendingAssignment ON tblEvents.EventID = qryReqPendingAssignment.PreferredSession UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblEvents.EventDesc;
Hi all, I have set out some data, however I am not sure if it is possible to list it in a specific way

For each product (prod_ID) there is events that happen, no matter what date the event is (like lines 20-24 & 32 & 33)
product is the Formula 1 event

For each departure (dep_ID) there would be different things happening (like lines 25 & 31)
departure is the specific package (where as there could be different-longer packages with different itinerary)

TABLE: prod_itin
itin_ID	brand_ID	cons_ID	date_added		itin_live	prod_ID	dep_ID	itin_day	itin_IATA_start	itin_start	itin_finish	itin_IATA_finish	itin_item
20		GPT		1	9/08/2017 1:00:00 PM	Yes		3		1		AEGPA		1:00:00 PM	2:30:00 PM				Free Practice 1
21		GPT		1	9/08/2017 1:00:00 PM	Yes		3		1		AEGPA		5:00:00 PM	6:30:00 PM				Free Practice 2
22		GPT		1	9/08/2017 1:00:00 PM	Yes		3		2		AEGPA		2:00:00 PM	3:00:00 PM				Free Practice 3
23		GPT		1	9/08/2017 1:00:00 PM	Yes		3		2		AEGPA		5:00:00 PM	6:00:00 PM				Qualifying
24		GPT		1	9/08/2017 1:00:00 PM	Yes		3		3		AEGPA		5:00:00 PM						Race
25		GPT		1	9/08/2017 1:00:00 PM	Yes		3	6	1		AUH		2:00:00 PM						Check-in
31		GPT		1	9/08/2017 1:00:00 PM	Yes		3	6	5						10:00:00 AM	AUH			Check-out
32		GPT		1	9/08/2017 1:00:00 PM	Yes		3	6	1		AEGPA		8:00:00 PM						Concert: Calvin Harris
33		GPT		1	9/08/2017 1:00:00 PM	Yes		3		4		AEGPA		8:00:00 PM						Concert: P!NK

TABLE: prod_deps
dep_ID	brand_ID	cons_ID	date_added		dep_live	prod_ID	dep_date	dep_type	dep_conf	dep_sold	dep_prod	

Open in new window

I've completed an Access app using Access365 on Windows10...this interface will be used by people also on Windows10, but using MS AccessRuntime2010.  All of my testing works fine on my machine.  I even copied & the file and renamed the ext from ACCDB to ACCDR to simulate the Runtime environment...again, all is well on my machine.  However, on the users' machines they are getting incorrect data & error messages.  

I know you'll want to know the exact errors & I will post those tomorrow after retesting.  The errors do not stop the users from utilizing the application, but what concerns me is that they are getting wrong information.  I have code that calculates the distance between two zip codes by using the latitude & longitude of both.  When calculating the distance between a zip in NY & one in MN i get over 1,000 miles (the correct value), but when the user is running the app, the result shows as only 8 miles (clearly incorrect).  We walked through the exact steps on both PCs to make sure we didn't do anything differently, but we get different results even though we use the same inputs.

Before I start supplying more specifics as far as code & screenshots of errors, I was wondering if there is anything inherent in the difference between Office365 & Office2010 that could lead to such problems.  Could there be something missing from the reference libraries that could be problematic?  Can MS AccessRuntime even add more references?  i'm guessing not.

I suspect i may need …
I wish to use the button "Edit List items" in a shortcut menu (right mouse button), for opening a lookup table, in Access, by using it in VBA.

I thougt i found it with ID 11229, but this ID is not meant for Access, but for Word. Do you know the ID for the button in Access ?

I have a picture of the icon on the button, but cannot send it, because there is no possibility to send an attachment.

Many thanks in advance for your help.
Is there a way to sort text as numbers in access without omitting the data that contains text values? I am currently trying to sort by operation which includes the values 1,2,2A,3,10,11,12,....ect. When I currently sort these values because the are stored as text they appear as 1,10,11,12,2,2A,3.......ect. I have tried using the expression Expr1: IIf([Operation] Is Null, 0, Val([Operation])) but it omits the operations that contain text values such as 2A. Is there a way to sort this information correctly?
I am having some issues in access using the DSum function to create a running total in access. I am trying to create a running total for both my Production standard and total conforming parts by using the date as the criteria. I am also doing this in a totals query which is set to pull data between [start date] and [end date]. The formula is working correctly for my production standard creating a running total between the specified dates but the total conforming formula (which is the exact same formula) is summing all of the data and then creating a running total. The are the two formulas I am using:

Production Standard - Running Total: DSum("[Production Standard]","M151-3 by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-3 by Month (with standards)","Date <= #" & [Date] & "#")

I should also note that I used these exact formulas for a different query (sorting by a different shift) and they worked perfectly.

Production Standard - Running Total: DSum("[Production Standard]","M151-1A by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-1A by Month (with standards)","Date <= #" & [Date] & "#")

Can anyone tell me what I am possibly doing wrong and why theses equations work for some of my data and not the others? This is all pulled from the same source I am just creating different reports sorted by shift.

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.