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 Experts,

I've got a DataGridView control that includes some columns that are bound to Double fields in my database.

When I edit any of these cells so that they are empty or blank I get this error:

The following exception occurred in the DataGridView:
System.FormatException: Input string was not in a correct format. —> System.FormatException: Input string was not in a correct format.
at System.Number.ParseDoubleString value, NumberStyles options, NumberFormatlnfo numfmt) at System.Double.Parse(String s, NumberStyles style, IFormatProvider provider)
— End of inner exception stack trace — at
System.Windows.Forms.Formatter.lnvokeStringParseMethod( Object value, Type targetType, IFormatProvider formatlnfo) at
System.Windows.Forms.Formatter.ParseObjectlnternal(Object value, Type targetType, Type sourceType, TypeConverter targetConverter, TypeConverter sourceConverter, IFormatProvider formatlnfo, Object formattedNullValue) at System.Windows.Forms.Formatter.ParseObject(Object value, Type targetType, Type sourceType, TypeConverter targetConverter, TypeConverter sourceConverter, IFormatProvider formatlnfo, Object formattedNullValue,
Object dataSourceNullValue) at
System.Windows.Forms.DataGridViewCell.ParseFormattedValu elnternalfType valueType, Object formattedValue, DataGridViewCellStyle cellStyle, TypeConverter formattedValueTypeConverter, TypeConverter valueTypeConverter) at
System.Windows.Forms.DataGridViewCell.ParseFormattedValu e(Object formattedValue, 

Open in new window

Easily Design & Build Your Next Website
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

I have an Access 2016 database which I use to scan barcodes to keep track of products that are already stored in the database. These products will always match the barcode number that is entered. Sometimes the wrong number gets entered from the scanner which comes up with the 3101 error.

The way that the data is scanned in is through a Subform with the field called barcode.
I would like a way to be able to show a message box to catch the error rather than the built in error on access.
So here is my newest dilemma.

We have a front end / back-end database application in Access 2013.

Since it went live three weeks ago we have been needing to do minor changes here and there to meet the needs of the users.
This has required us to email the users about 2-3 times a day to download the newest update.

There has to be an easier way of updating then relying on the users to update.

So I searched all over and found a couple third party apps that would make this possible but they are expensive.
I did find an article from 2003 that addresses my exact issue.

My front-end has a table, tblClientVersion and a linked table to the back-end, tblServerVersion
When they don't match the app pops up a window asking you to upgrade.  If the user hits yes it closes the front-end, opens up another Database (Update.accdb) and that database does the actual copying of files from the sever to the client.  Then, when complete, it will close itself and open the client backup.

I have no issues with the first part of checking and I have no issues with update.accdb opening up the client.  The issue I am running into is it won't copy the file from the server to the users workstation.

Here is what I am working with:

Option Compare Database
Option Explicit

Dim strSourcePath As String
Dim strDest As String
Dim strPath As String
Dim strBkup As String
Dim strMyDB As String
Dim strVer As String
Dim strSource As String
Dim strMsg As String
Dim strOpenClient As String

Open in new window

I have a table as a record source for a data entry form.  One of the fields in the table is OrderDate.  When the user enters a new record on the form and the new order date (today's date) is 5 years AFTER the last order date for that particular part number I want a popup message box to appear letting the user know that...

"Please review the order history for this part.  It was last produced (insert date last order date)”

How can I do this?  I know how to do a DMax for the order date but I don't know the syntax for 5 years prior to today's date.
MS Access query needs to show all data after the first 22 characters. First part of the field is a date/time combination. Need to just show data after that to be able to group.
   02.05.18 09:42:01  USMC Mock Rd 115  PROBE STATUS PHC     PPC ...                  Normal
I cannot figure out what is wrong with this Insert query. It keeps saying 0 rows updated but I know that the tblUpdatefiles has 6 more records than tblFiles. Can anyone offer a solution?

INSERT INTO tblFiles ( ID, [File Name], WebName, DatabaseName )
SELECT tblFiles.ID, tblFiles.[File Name], tblFiles.WebName, tblFiles.DatabaseName
FROM tblFiles LEFT JOIN tblUpdateFiles ON tblFiles.[File Name] = tblUpdateFiles.[File Name]
Where Not Exists (Select * from tblFiles where tblFiles.[File Name] = tblUpdateFiles.[File Name]);

Open in new window

I would like to to search a listbox that has been populated by a query (which I saved in Access as qyrKunde_Suchen):

SELECT tblkunden.kundenid,
       [nachnameoderfirma] & "" & [vorname]                       AS Kunde,
       [strasse] & "" & [strassennr] & "," & [plz] & "" & [ort] AS Adresse,
FROM   tblanreden
       INNER JOIN tblkunden
               ON = tblkunden.tblanreden_id
WHERE  (( ( [nachnameoderfirma] & "" & [vorname] ) LIKE
                  "*" & [formulare] ! [frmsuchen] ! [txtcustomer] & "*" ))
        OR (( ( [strasse] & "" & [strassennr] & "," & [plz] & "" & [ort] )
              LIKE "*"
                   [formulare] ! [frmsuchen] ! [txtcustomer] & "*" ))
        OR (( ( tblkunden.telefon ) LIKE "*" & [formulare] ! [frmsuchen] !
                                         [txtcustomer] &
                                             "*" ))
        OR (( ( tblkunden.handy ) LIKE "*" & [formulare] ! [frmsuchen] !
        OR (( ( ) LIKE "*" & [formulare] ! [frmsuchen] !
                                       [txtcustomer] &

Open in new window

I am using MS Access 2016. I have an unbound main form with a bound subform.
On the Subform I have a True/False control (Whether a customer is active or inactive).
I want the default view to NOT include Inactive customers but have a tick box that will then include inactive customers.
I have used me.filter before bust I think I am having a problem with the True/False field.
Any help greatly appreciated.
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?
Easily Design & Build Your Next Website
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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?
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!!!
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.
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.
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 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
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.
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.

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 amend the code below to accept the "F4" key so the Combo Box list drops down and I can use the arrow keys

Private Sub cboTask_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode

 Case vbKeyReturn, vbKeyTab, vbKeyUp, vbKeyDown
   KeyCode = KeyCode  'Accept these keys

  Case Else
   KeyCode = 0 'Block all other keys
 End Select

Open in new window

Is it possible to extract a list of all of the variables that appear in the "Locals" window - when in runtime mode?  Ideally, I could use this if an error occurs.
I have an Access form with several combo-boxes, all with "Limit To List" set to True.  When I first login and enter something that is not in the list, I have some code triggered by "On Not in List" that asks me if I wish to add it, I select yes and it adds it correctly, with no extra prompts.

However, my form/application allows users to switch to other projects/back-ends.  These back-ends are all very structurally similar but have different sets of data, dependant on the projects and need to remain completely separate.  As soon as the applications re-links to the chosen back-end, the "On Not in List" event that worked originally, now has an issue.  It comes up with the message, "The text you entered isn't in the list.", which is the problem.  If I look at the new entry, it is in the drop-down list and if I select it from the list - it all works fine (but obviously, I shouldn't need to reselect it from the list).  

Simply closing the form and re-opening it - doesn't work.  I actually need to exit the Access application completely, re-open it - and once again, it works.

The combo-boxes are pretty much all text fields (not just numbers / date), and the combo-box fields themselves are bound to their respective fields in the main table.  However, the "row source" of the combo-boxes is a separate table that is much smaller, grouped and indexed.
I have a Microsoft Access application that is running on Server "2012 R2".  Currently, users login and run the application via RDP.  Whilst this works well, I have come across the term "RemoteApp", where the user can run the application as if it's running locally, making it easy to switch between our application and those running locally. In some cases, this might be a nice option, but I do have a couple of questions about it.

1. Does it have any obvious downsides?
2. Does it help reduce the load on the server in any way?
3. I can currently logon to a users session and help them out (if they need me to).  Would this still work?

Thanks as always
I have a Microsoft Access application that is installed on a "2012 R2" server.  Users currently access this application via the Remote Desktop from their local PC.  To set them I, I provide the login credentials (IP address, username, password), an installation video - and off they go.   One thing that does complicate the issue is that the RDP also needs access to the user C: drive, which is easy enough to setup - but I just wanted you to be aware of this for the question.  

Whilst this seems to work, I'd like some input to find out if there a better way for me to help users setup each PC.  Maybe a single setup file that would allow them to enter the credentials, access the C drive and then automatically add the shortcut to the desktop.

Any help/suggestions would be greatly appreciated.

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.