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 have a table with birthdays in, the format is mm/dd/yyyy. I have a query that formats this date to mm/dd as a new field then i have a form with a command button that appends these fields, along with others into a new table.  After appending the records, I want a message box telling me if the count of mm/dd is greater than 6. Can someone give me some suggestions? I can't seem to get the DCount to count the mm/dd field.
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 and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Hi Experts,
Just wondering if I have an Access MDB file but my pc does not have Access installed, is there anywhere I can upload the file to view/modify the contents?
The charity I work for provides a delivery service for groceries, food, wine and flowers in the city and neighbourhoods. The data used to be recorded with Excel but as I am about to leave, I have successfully been allowed to transfer the data to Access - making it easier for future developers to work with the data.

I would like to offer the functionality of displaying in a report,the number of orders for a particular shop per year. For example, let's say that we have 5 different Aldis in the DB. They are stored as Aldi Southern Quarter, Aldi West, Aldi East etc.

I used the following SQL code to create a query:


SELECT Month([deliverydate])                 AS theMonth, 
       Year([deliverydate])                  AS Year, 
       Count(tblshoppingtaxi.shoppingtaxiid) AS Orders 
FROM   tblshops 
       INNER JOIN tblshoppingtaxi 
               ON tblshops.[shopid] = tblshoppingtaxi.[shopid_fk] 
GROUP  BY Month([deliverydate]), 
HAVING ( ( ( Year([deliverydate]) ) = [theyear] ) 
         AND ( ( tblshops.shortname ) LIKE "Aldi*" ) );  

Open in new window

What happens is, that it groups all Aldis  by month and displays the number of orders for that particular month.

I do not want this - I just want to have a total for all Aldis per month.

Can this be done with SQL or should I resort to something else?
Pl refer to the attached Access 2013 database file.

In the Form - FormDCEntry I have a sub form which captures the Invoicing Details.
How can I have a total Value of the sum of Amounts from multiple lines.
Say Amount of line 1 + Amount of Line 2 .... and so on getting automatically in the field "Value" which is below the Sub form.

My info.Delivery-Challan.accdb
On my MS Application application, I have several forms containing combo-boxes (as some of you know from your help last week :O).  Currently, most of these objects have the rowsource set via a SELECT statement in the properties.  My question is this.  Is there any real benefit in converting those SELECT statements, to saved queries?
Hi Experts,

First requirement (and more important): I'm developing a farming application in MS Access and would like to add farm maps to it.  All fams have several areas for different crops, I would like to be able to draw those areas, save them in a table with GPS/area infos. Something like the attached Pict.

Second: After having the latitude and logitude (GPS) info, I need to link pictures taken form those areas using exif data.

Any help would be graetly appreciated! :)


I have a VB6 application which uses MS Access as backend database. Initially we had set this application to run on 3 machines(all desktops) connected on LAN, with database in one of the machines. This works fine with little delay on the other two machines to access the DB. We are trying to move this application to Windows 2008 server. Now the exe and the database both reside on this server. When we try to run the exe from other machines, which are slave machines, connected to this server, it works fine sometimes and sometimes it just goes into processing mode and seems to be hung. I am unable to understand why it runs on the desktop setup, but has problems with the Server-Slave setup. Does it has anything to do with the application or would it be a LAN problem? Thanking you in anticipation for your help.

C.J. Acharya
I am working in Access VBA. I have two fields on a form. One is for Architect Firm and the other for Architect. The architect combobox needs to be filtered on Architect.FirmID so that only Architects from the selected firm are in the list. I am using [Screen].[ActiveControl].[Parent]![cmbArchitectFirm] as a criteria in the RowSource of cmbArchitect. It works great to filter it, but only after a record has been selected in the Architect Firm combobox, because the AfterUpdate event on cmbArchitectFirm has Me!cmbArchitect.Requery. Before I select the architect firm, the architect field is blank even if the firm and architect fields have values. I have tried putting Me!cmbArchitect.Requery in different opening events on the subform with no luck.
There are two forms where I am seeing this behavior. Both are datasheet subforms. I use the same controls in two form views where the form is primary. There I use [Screen].[ActiveForm]![cmbArchitectFirm] and it works great.
This is something I have been fighting off and on for several days, trying to figure out the best way to create this filter. If I have to use the actual form names to make this happen I will have to make several copies of my subforms so that every form will have it's own dedicated subform, which I would prefer not to do.
Any help would be greatly appreciated!!!
In Ms Access I am trying to make a API call to Paypal to try them out. But no matter what I get a Error "0". Does anyone have any idea what the problem is. I have put my code below.

Option Compare Database
Option Explicit
Public Function PayPalGetTransactionDetails()

'On Error GoTo Errorhandler

    Dim Http As New msxml2.XMLHTTP60
    Dim Doc As New msxml2.DOMDocument
    Dim fp1 As String
    Dim fp2 As String
    Dim XmlString As String
    Dim UrlPath As String
    Dim TransactionIdValue As String
    Dim APICALL As String
    Dim UserNameValue As String
    Dim PassWordValue As String
    Dim SignatureValue As String
    Dim SubjectValue As String
    UserNameValue = "" 'API Paypal User Name
    PassWordValue = "MyPassowrd" 'API Paypal Password
    SignatureValue = "MySigvalue" 'Supplied by Paypal.
    APICALL = "GetTransactionDetails"
    SubjectValue = ""
    fp1 = "C:\Database\ExportedOrders\PaypalApiTestRequests\"
    fp2 = "C:\Database\ExportedOrders\PaypalApiTestResponses\"
    UrlPath = ""
    TransactionIdValue = "57547564e67744757"
    Http.Open "POST", UrlPath, False
'    Http.setRequestHeader "CONTENT-TYPE", "text/html"
'    Http.setRequestHeader "Username", UserNameValue
'    Http.setRequestHeader "Password", PassWordValue
'    Http.setRequestHeader "Signature", SignatureValue

    XmlString = "<?xml version=""1.0"" encoding=""UTF-8""?>" & 

Open in new window

Access 2003 mdb with a SQL 2014 backend. We recently changed DNS servers on our LAN to a new box with a new name and IP address, and now the ODBC linked tables to SQL take forever to open. Is there something in an mdb that remembers it's LAN DNS server? I already tried relinking a few tables with the linked table manager, no difference. I do know, however, that the ODBC DSN is fine because I created a test mdb and added a few linked tables from the same SQL backend, and they open lightning fast. It's just something about this existing mdb that now won't open tables in a tolerable time.
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.

I have a listbox (List4) on a form (frmScholarship_Codes) that contains 2 values, -1 or 0 (true or false).  My form's recordsource is qryScholarship_Codes_RS.  When someone chooses a value from the listbox (List4), I want to change the recordsource to a parameter query, qryScholarship_Codes_PARAM, which filters the form data based on the selection from the List Box.  In qryScholarship_Codes_PARAM, the parameter being [forms]![frmScholarship_Codes]![List4]

Each time I run this query, I get an error.  

This is my code:
Private Sub List4_AfterUpdate()

  If List4 = "" Then
  Me.RecordSource = "qryScholarship_Codes_RS"
  Me.RecordSource = "qryScholarship_Codes_PARAM"
  End If

End Sub

Thank you!  
Microsoft Access file locking.
I have an Access (2016) database that used to work when I clicked on a button it would export the data to an Excel spreadsheet. I created the commands in Access's VBA (I am not a VBA programmer) however now when I try to run (click on the button), I get the following error "Compile error: Can't find project or library. Here is the VBA code that I am using:

strSQL = "SELECT * FROM " & "Table_ExpenseReport" & " WHERE " & "[ID] = " & ID & ";"

It is showing that the error is on the ID part. I want this to pull the data in the ID field (cell) in the Access database and keep it as the rest of the command sends that ID's data to the Excel spreadsheet and only that ID's data. Can someone help me? I am more than happy to attach screenshots, etc. to make this work again.
How to display corresponding values of combo box in text box?

i am using vb6.0 database ms.access

for sample.. when i click the value in combo box my id number it will appear in all text box my information

thanks.. btw i find similar prob

I'm mainly working with MS Access for my client, and I noticed a reccuring issue: Data import.

This is a major source of troubles, especially when data (usually excel or CSV files) comes from unknown (and unreliable) source, usually they hold bunch of incorrect data, preventing databases from working properly (crash, unhandled data, vanishing data, data replaced by incorrect ones ....... the list of potential bugs is infinite).

So, I wrote a generic library database, who's purpose is to handle data import, validation, convertion, computation, from virtually any data source, to virtually any data source.

Now, I would like to distribute (and why not, sell) this "Framework" (can I call it like this ?), for privacy purpose, I'm thinking about converting it into a COM componnent that VBA projects will be able to set a reference on.

Wich steps should I follow to achieve this goal ?
What about software licence ? And price ?
I am trying for format the textbox with values for the underlying data source for the report.  If I reference the control name or the date field name, it fails saying that it is null.

Private Sub Detail0_Format(Cancel As Integer, FormatCount As Integer)    

    Box0.BackColor = Me!labelColorStr   ((((value is null))))
    Box1.BackColor = Me.txtLabelColorStr  ((((value is null))))
    Box2.BackColor = Me.labelColorStr
    Box3.BackColor = Me.labelColorStr
Exit Sub

    Exit Sub

End Sub
I’m trying to find vba to transfer an Excel file of my choosing from Windows Explorer into an existing table in Access without opening the Excel file.

I would like this to be attached to a button's on click event in Access.

I've only found code to open a folder but the file is opened when clicked and also one that only shows the folders and not the files within the folder.  Any help would be appreciated.
I have a database that runs an import of one CSV file and two xls files every day. I had set up fail-safes within the code to make sure that no blank files were imported, but recently discovered that this had caused some problems.

If DCount("*", "Table", "[Field]=Format('" & Activity_Date & "', 'Short Date')") = 0 Then
    MsgBox "Import brought over Zero files for Date imported. Please confirm this is accurate", vbYesNo, "Zero Import"
    If LResponse = vbNo Then
        Exit Sub
    ElseIf LResponse = vbYes Then

Above is the code that I created. Even if there is data in the table that is being searched, the database instead jumps to the Message Box line and then takes me to the bottom of the code that ends the import prematurely. So far, the only way that I've found to allow the import to go through completely is to turn off this section of the code completely. However, that means that there is no fail-safe in place if the import tries to bring in an empty CSV file. If there is any way to set this up, or maybe an easier way to check for blank CSV files, that would be greatly appreciated.
I have a textbox control set to standard format with 2 decimal places. It looks fine when you open the form. But if you click on the textbox control itself the decimal places then jump to 8. When you move the focus to another control it resets back to 2 decimal places. Why does it do that when you click on the control?
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I had this question after viewing Environ("USERNAME").

I want to store username and date/Time stamp in each record, for 3 tables.

I am using below same codes for three forms ( BeforUpdate Event)

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!updateBy = Environ("username")
Me!updateTime = Now()
End Sub

However, in one form, the [updateBy] field and [updateTime] fields are saved, but in two forms its not saved.
Its shows error as Field updateby is not found..
What can be the cause ?
Would appreciate help.
Thanks in advance.
Kamlesh Jain
In Access 2013 I am using the following
Docmd.ShowToolbar "Ribbon", acToolbarNo         Which hides the ribbon

When I run in Access 2016 the Ribbon remains.

Amy help appreciated

Hello everyone

I work for a small charity and we provide a courier service in our city. In the past seven years, the data has been saved in an Excel workbook with lots (!) of VBA code.
I am about to leave the company and I have finally succeeded to have everything stored in an Access DB, as I could not guarantee for the data integrity in Excel once I have left. It took me a lot of effort to convince my boss to use Access in the first place. I have now transferred over 40.000 records and I need some advice on the table structure/relationships between them. Here is the model:

Let me explain in detail what we do and then my question:

A customer goes to a supermarket, buys his/her groceries and drops them off at the customer service desk. One of our couriers then goes to a shop and delivers the groceries. Shops are supermarkets, department stores and some smaller specialty shops.  So I record the name of the shop and the customer and save it to Access.  However, we also provide deliveries for flower shops and beverage/wine shops.  As these shops tend to have one-time customers (like flowers sent as a present, or the occasional wine order), we decided not to record these customers. Instead, we set the shop and the customer to be the same and added the delivery address to a memo field in Excel.  Apart from that, shops cannot have deliveries to themselves and rarely can end customers/consumers have deliveries from them them to another place. For …
I am using MS Access as the front end (Microsoft Office 365 ProPlus) and currently have a SQL2008 server. I am migrating to a newer SQL 2014 server and are experiencing issues with code written using SQLDMO while testing in the new environment. I have an SSIS job that is called using the now deprecated SQLDMO that is obviously failing in the new environment. I am having limited success finding how to use SQLSMO in a similar fashion. Can someone please give some general direction (or specific!) so that the application can call these jobs in the new SQL server 2014?

Here is the code snippet we use:

Private Sub Command45_Click()

   Set objSQL = CreateObject("SQLDMO.SQLServer")
   ' Leave as trusted connection
   objSQL.LoginSecure = True
   ' Change to match the name of your SQL server
   objSQL.Connect "Server2K14"
   Set objJob = objSQL.JobServer
   For Each job In objJob.Jobs
      If InStr(1, job.Name, "SSIS Bills") > 0 Then
         MsgBox job.Name
         MsgBox "Job Started"
      End If

End Sub

Thank you, and please let me know if I have failed to provide necessary information.
I am trying to create a cross tab query. I have a date range in the query I am using that pulls in two dates and retrieves all the information between those two dates. When I create a cross tab query I get an error message that says access does not recognize Between [Forms]![frmEmployeeATLX]![dfsBegDate] And [Forms]![frmEmployeeATLX]![dfsEndDate] as an expression? Is it possible to create a cross tab query that uses a date range from a form? I attached a couple photos. Any help would be much appreciated!!
I need to map network drives to copy a file !  I tried to use the microsoft suggested function getnextletter but am getting errors.
Seems the oDrives is not an object As I get object required at that line!

    Public Function GetNextLetter(DriveLetter)
          'Start x at the ascii value of the drive letter to start the search
       'unless something is passed in. This sample uses capital letters and
       'starts at F.
       If IsEmpty(DriveLetter) Then
        x = 70
        x = Asc(DriveLetter)
       End If
       Set oDrives = wshNet.EnumNetworkDrives

       'Step by two since the first item in the collection is the drive letter
       'and the second item is the network mapping
       For i = 0 To oDrives.Count - 1 Step 2
        If Chr(x) & ":" = oDrives.Item(i) Then
            x = x + 1
        End If
       GetNextLetter = Chr(x) & ":"
    End Function

The VBA that references this function is!

Private Sub Command0_Click()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wshNet = CreateObject("WScript.Network")

strSource = GetNextLetter("F")
wshNet.MapNetworkDrive strSource, "\\TQFPRK06\project management\"
strDestination = GetNextLetter("F")     '<-- You can change the letter.  It represents the starting letter to work from.
wshNet.MapNetworkDrive strDestination, "\\TQFPRK06\Tech_drive\"
strSource = strSource & "test.xlsx"

objFSO.CopyFile strSource, strDestination & "\"

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.