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

Ok below is my current SQL statement for a query in AccessDB I am trying to find a way to add a find max date (or most recent date) from the TxnDate column and only return results with the most recent TxnDate. Any help is greatly appreciated. Thanka

SELECT Customer.FullName, Customer.Email, Customer.Phone, ReceivePayment.TxnDate, ReceivePayment.TotalAmount, Customer.TotalBalance, Customer.CustomFieldDepartment
FROM Customer INNER JOIN ReceivePayment ON Customer.ListID = ReceivePayment.CustomerRefListID
GROUP BY Customer.FullName, Customer.Email, Customer.Phone, ReceivePayment.TxnDate, ReceivePayment.TotalAmount, Customer.TotalBalance, Customer.CustomFieldDepartment, Customer.IsActive
HAVING (((Customer.TotalBalance)>30) AND ((Customer.IsActive)=Yes))
ORDER BY Customer.FullName;
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 am getting two errors.

First one is when I click the "New Tenant" button on the bottom of the "frmNewLease"

The second is when I select a Make in the Vehicle form. This seems to work fine when I bring up the form independently but not when I bring it up as a subform.

See the attached Screenshots and DB

I am also unsure that the Vehicle form is properly connected to the Tenant which is its parent form.

I am trying to limit the list of a combo box based on a query. The combo box should contain a list of apartments that do not have matching fields between tblApt.AptID and tblRental.AptID.
In other words the apartments listed should only contain the apartments that are not rented.

In the attached DB if the query works properly it should not list apartments 1000-A through 1000-D.

You would see this in the form Create new Rental Switchboard Item.

ms access 2007 automation error excel 20116 interface not registered
Access 2013 ".accdb" files dates do not change in Windows File Explorer consistently when the database file is closed. In the old ".mdb" days the file date/time changed without fail, every time the file was opened and then closed.

This change in behavior for Access 2013 files has caused me to load an older version for updating inadvertently multiple times. Needless to say this is very irritating.

Any solutions?

I have table of eMails (with MemberNo, and other data to modify existing data in Members table) and of course the Members table.

Basically, I want to loop through the eMails table and for each eMail record open the corresponding Members record (using MemberNo) and display data from both on a (sub)form.

I want to enable user to check the data from the eMails table and, if necessary, change some data and then click an Accept button to confirm the update.  On the odd occasion the user may wish to reject the update and so click a Reject button.

I’m happy using DAO recordsets to progress through the eMails table and to update the Members table as necessary but cannot work out how to display eMail + Members data record by record, enabling the end user to accept or reject the update (I’m am not clear what events I should be using)

I would be pleased if you could give some pointers in how to achieve this

I have reset customer registration to use AES encryption to comply with new regulations.  The MySql is recording the data correct with $salt being used to encrypt the email.
However, I use PHPMAKER to manage the database on my PC (or could use MS Access but I cannot figure how to decrypt and encrypt the email so that I an update data.
The email is the only field of 20 to be encrypted.

Anyone able to help with amending the various PHPMaker scripts or a VB function to import and export to Access 2003
My database is just over 200,000 emails.

State of play:

Databases ensure data integrity and relationship through both primary and foreign keys, and MS Access is no exception.

Well, that's not completely true ...

A primary key should not be null.  - No problem, the NOT NULL constraint enforces that behaviour.
A primary key should be unique. No problem again, the UNIQUE constraint enforces that behaviour.

A primary key should be used only once, and this is where MS Access fails as the following sample will demonstrate:

Create a table, add an ID column, set its data type to AutoNumber.
Now add a second column, the name and data type doesn't matter.

Add some data to the table (10 rows).
Delete some rows in the middle and then delete the last row.
According to the 3rd primary key rule, deleted IDs should be lost, forever.

Now close your table, and compact the database.
Open your table again and add a new row.
Surprise !! The Newly generated ID isn't 11 as we should expect, but 10 again (remember you deleted it?)

Why may you ask ?
Ms Access internally keeps track of the last ID used, but this ID is reset to the max ID + 1 when you compact the database. This effectively breaks the 3rd primary key rule.

What can we do about this ?

Data macros to the rescue

Since MS Access 2010, Microsoft introduced Data Macro, which can perform various actions before inserting, deleting, updating data and after inserting and after updating data.

These macros (also known as Triggers in other database engines) are executed automatically whenever you add, insert or update data, manually, trough forms, or programatically. They are mostly used to validate data (i.e: ensure data integrity), but we can use them to generate a true ID number that will never - ever - be re-used, even if the database is compacted.

To achieve this, we first need a table that will keep track of the max ID is used.

Also we will need data macros that will pull the max ID before we add a new row, increase the max ID after we save a new row, and prevent any ID update.

So, in this example, create a table, name it IDtrackers.
Add a column, name it TableName, data type Short Text, don't allow empty strings, set it as primary key.
Add a column, name it maxID, data type Numeric long, don't allow null values.

Create a second table named MyTable
Add a column, name it ID, data type Numeric long, no null value, set it as primary key.
Add a column, name it data, data type doesn't matter.
In the ribbon, on the Creation tab, choose the Create data macro button, and choose the 'Before update' event.
Copy and paste the following code (hopefully, it becomes self explanatory in the editor):

I am trying to create a way to add Vehicle Make and Model which makes sense. And am having trouble doing so.
You can see my preferred approach on things like "frmNewLeaseTerm" and  "frmNewState" Or on the form "frmNewLease" and clicking on the buttons for adding lease terms suffix or State.

I know this may not be the standard approach but it is the one I need as I need to make things extremely simple.

The problem I'm having is due to the fact that its using two tables "tblCarMake" and "tblCarModel"

My Goal would be to have it look like the attached picture but with an added button for "Add Model"
I also forgot how to open the form as a popup so if you could help with that as well I would be grateful.

Also I seem to be having a issue with the "New Tenant" button on the bottom of "frmNewLease" and also my dynamically updating combo boxes for make and model arent working right.

I'm having a brain cramp this afternoon.

I'm building a SQL Server connection string on the fly in an Access application.  If I use SQL Authentication, the string looks like:
Driver={SQL Server Native Client 10.0};Server=MyServerName;Database=MySchemaName;Trusted_Connection=Yes;

Open in new window

and works fine.  But if I uncheck Use SQL Authentication and enter a UID and PWD, the connection string looks like:
Driver={SQL Server Native Client 10.0};Server=MyServerName;Database=MySchemaName;User ID=myUserID;Password=myPassword;

Open in new window

and does not work, even though myUserID and myPassword are the same userid and password I used to log into my computer.  I have tried this with:

Open in new window

but that isn't working either.

I know this has to be something amazingly simple, please help.
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Access 2010 vba

I'm trying to get the Max record for a query with a specific id
and then ge information from 3 fields to fill in textboxes on a form.
jjj  is coming up with  records but i cant seem to see them in code  ?

t=  "18460"

' get the correct max id here.
    StrSqlc = "SELECT Max(dbo_t_redbook_pricing_escalation_master_edit.PRICING_ESCALATION_ID) AS MaxOfPrc_Id, LAST_MODIFIED_BY_RACFID, LAST_MODIFIED_BY_DATE " & _
    "FROM dbo_t_redbook_pricing_escalation_master_edit" & _
    " WHERE (((dbo_t_redbook_pricing_escalation_master_edit.PRICING_ESCALATION_ID) =  '" & t & "' ));"
    Set R = CurrentDb.OpenRecordset(StrSqlc, dbOpenDynaset, dbSeeChanges)
    tp = R.RecordCount
    jjj = R.Fields(0)


Open in new window

I have a user that needs to import data daily into Access from an Excel spreadsheet.
I've been using the importspreadsheet in my Access VBA, then dump that data into a temp table, and then import it using append queries.
The queries run from a form with buttons so the user doesn't have to do anything, everything is imported in the back with the VBA.
The problem I'm having is that there are about 50 fields that have relationships to other tables using a primary key. For example the spreadsheet has the Customer Name but in Access the customer has an ID number. Before I write a Dlookup for each field I was wondering if there is an easier way.
What's the best/fastest way to lookup the correct IDs from the other tables?
In Access, I am using a Form, "FORM1"  and Subform, "tbl_Customer_subform". I am using combo boxes and text boxes on my main form to filter and
search on the subform. The challenge I have yet to figure out is how to Refresh or requery the subform  that contains all the customer records back to
its original 5,000 records. Can I do this with a command button?  Below is the code I am now using for searching and filtering.  Is there a way to easily do this?
Thanks in advance for any help!

 Private Sub Combo4_AfterUpdate()
Dim MyWriter As String
MyWriter = "SELECT * From [tbl_Customer] Where ([Field17] ='" & Me.Combo4.Column(1) & "')"
Me.tbl_Customer_subform.Form.RecordSource = MyWriter
End Sub

Private Sub Combo23_AfterUpdate()
Dim MyCustomer As String
MyCustomer = "SELECT * From [tbl_Customer] Where ([Field3] ='" & Me.Combo23.Column(1) & "')"
Me.tbl_Customer_subform.Form.RecordSource = MyCustomer
End Sub

Private Sub Text28_Change()

Dim strSQL As String

strSQL = "Field3 LIKE " & Chr(34) & Me.Text28.Text & "*" & Chr(34)

Me.tbl_Customer_subform.Form.Filter = strSQL

Me.tbl_Customer_subform.Form.FilterOn = True
End Sub
I'm trying to print a form with a subform in it. Both have diferent backcolors for its records depending on their value. I control it with VBA in detail_paint using backcolor property.
It works till I send it to print. Preview sight is always in black and white.
I have verified the printer properties and it's set to color.
Any help?
Thanks so much
I have an Access 2010 database and a command button with the following code:

  Dim stDocName As String
  Dim stLinkCriteria As String
  Dim strFindEval As String
  Dim strFindStaff As String
  Dim Staff As String
  Dim Evaluator As String
Dim UserName As String
    UserName = Environ("USERNAME")
    If UserName = "ABC" Or UserName = "DEF" Then
    strFindEval = InputBox("Enter Evaluator's Initials", Evaluator)
    strFindStaff = InputBox("Enter Staff Member's Initials", "Staff Member")
        stLinkCriteria = "[Staff]=" & "'" & strFindStaff & "'" _
        & " AND " _
        & "[Evaluator]=" & "'" & strFindEval & "'"
            GoTo Select_Form
        End If
    strFindEval = UserName
    strFindStaff = InputBox("Enter Staff Member's Initials")
        stLinkCriteria = "[Staff]=" & "'" & strFindStaff & "'" _
        & " AND " _
        & "[Evaluator]=" & "'" & strFindEval & "'"

' Accounting
    If strFindStaff = "GAS" Or strFindStaff = "RJL" Then
        stDocName = "frmEvalStaffAcctDone"
        stDocName = "frmEvalStaffDone"
    End If

I need the code to use the data entered in the strFindStaff prompt and then see if this user is a member of the ACCOUNTING group.

I'm hoping to replace the following so that it looks to see if "GAS" OR "RJL" are members of a …
Hi. I am using the following code to fetch the exchange rate in Access VBA. This works fine for me in South Africa
but does not work for someone in Germany. For him it returns an exchange rate of zero no matter what the currency is.
Why would this be?

Sub oTest()
    MsgBox oExchangeRate("USD", "EUR")
End SubFunction oExchangeRate(ByVal oFrom As String, ByVal oTo As String) As Single

On Error GoTo EH

    Dim oXMLHTTP As Object
    Dim strHTML As String
    Dim oRE As Object
    Dim oMatches As Object

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "<span class=['""]uccResultAmount['""]>([^<]+)</span"

    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    'oXMLHTTP.Open "GET", "", False
    oXMLHTTP.Open "GET", "" & oFrom & "&To=EUR", False
    Do Until oXMLHTTP.readyState = 4
    If oXMLHTTP.Status = 200 Then
        strHTML = oXMLHTTP.responseText
        'parse the result
        If oRE.test(strHTML) Then
            Set oMatches = oRE.Execute(strHTML)
            oExchangeRate = oMatches(0).submatches(0)
            oExchangeRate = -1
        End If
    End If
    Exit Function
    oExchangeRate = -1

End Function

Open in new window

I need to connect an Ms Access Database to an Oracle database (I suppose via an ODBC connection).

When I connect to this Oracle database via SQL Developer (which works well), I use the following parameters:

  1. LDAP server:
  2. Select Context: cn=OracleContext,dc=YYY,dc=int
  3. DB Service: XXXPRD.

Now like to reproduce the same connection in the ODBC Data Source Administrator screen of WINDOWS 10

ODBC Screen in WINDOWS 10.

The back-end database is an Oracle database version Oracle Database 12c Enterprise Edition Release - 64bit Production.  For this purpose I have downloaded and installed the Oracle Database 12c Release 1 ( 64 bit, but do not know what to do next.

Can anybody put me on the right track?

Thank you.
I am creating a simple survey/questonnaire datbase.
Usersrequired a dunamic setup do they can add/remove  questions going forward.

I dedided to create most of the survey at row level of table. I created a table for initial  question types and then an attributes table separately but works on the same   split form.   I have now an issue with the row source coming as the rows source is very slow and is  not working consistently.  the screen values flicker and I thought of setting the attributes table as bound.  Is there an example ofhow to make this form work?  Would a function work more effectively to ensure correct recordsets are returnes and can be veiwed?

Access ErrorHi,
 I have an ACCESS database which has been used for years without a problem (other than occasional corruption whenever it exceeds 150MB). I archived some old data from it and the database size is < 100MB.
 In the past several days, it became inaccessible from the computers almost once a day. Then I connect to the server (where .MDB resides), open it and Access says that the database needs to be repaired. When I click OK, then it makes a copy and proceed to perform repair. After the repair, users can open it again. But it is happening every day. At the end of each repair, i see the message the message as seen in the screenshot.

 What are the areas that I can look into to find out the root cause of this issue?

Free Tool: Subnet Calculator
LVL 12
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I have setup  RemoteDesktop Services on a Windows 2012r2 server to allow remote users to run Microsoft Access Databases.   It was working pretty good with the occasional "Your network access was interrupted.  To continue close the database, and then open it again"  error.

Lately I have been getting lots of locked files mostly Microsoft Access and one unrelated application.  

I am tied into the corporate licensing so I have enough licenses but is there a limit to the number of users on the server who can have the same application open.  (Microsoft Access being the most common issue).

I have monitoring setup and there is no network connection issue, I am not close to maxing out bandwidth, CPU or Memory.

This is quickly becoming an important issue.    

I know the recommended solution is to split the Databases so that there is a front and back end but there are to many databases and I only need to keep this going for another 3-4 weeks.   Does anyone have any suggestions
I am getting a Run-Time error 3131. Syntax error in FROM Clause.
I am using the following code in Access on a form using a combo box to select "Writers" in a subform.
I am not a programmer and can not seem to figure out what is wrong. Any idea why I am getting this error?

Code -
Option Compare Database
Private Sub Combo4_AfterUpdate()
Dim MyWriter As String
MyWriter = "SELECT * From tbl-Writers Where ([Field17] ='" & Me.Combo4 & "')"
Me.tbl_Customer_subform.Form.RecordSource = MyWriter
End Sub

Thank you...
I have developed a MS Access database with forms and report, to organize payments and cheque printing. This is now shared in a cloud and it's usage is always at risk as it is opened locale, but if something goes wrong during the sync then of course the file becomes corrupted or incomplete and it is obviously a mess.

So, here's the question. Is it possible, from the existing MS Access database to develop an online interface which shares the table source on a Microsoft Cloud?

Any input is appreciated. Thank you.

Currently I'm trying to imitate on how to export data from access 2000 to a fixed width text file, and so far i have successfully exports data to text file only with fixed width.
So when I try to upload the file to a third party software that we use it says invalid format.
When this error occurs i suspect it is because i don't have a start position.

I would like to ask question on how create  and format a text file with fixed width and start position in .

This is the initial tutorial i've followed:

I am attempting to create a in-house questionnaire for our sales team to allow them to better quote for certain projects.  There are a number of questions with multiple choice responses.  Based on these responses i would like to generate a number which would relate to a specific multiplier which we use to add to the base figure for the job (hope this is making sense?)

Any suggestions for the best approach to this?  It's a bit like those online personality questionnaires which give you a figure at the end of the questions saying if you scored 1-50 you are "insert generic state of mind here".  If you scored 51-100, etc, etc.

I'm looking at building this initially in MS Access with the hope of upgrading to a web based system at some point).

Dear All;
We have one issue with one of our superior, he wants an interface which was done in window form application using C# programming language opposed to the MS access interface done with VBA, according to him, VBA is too junior compared to C#. Correct me if I’m wrong:
(1)      VBA & C# code are very much similar, the only major notable differences are :
•      In C# we use curly blackest ({}) while in VBA they are not applicable
•      VBA works only along a program for example Excel, Word or Ms Access while C# work in windows not necessary a program
•      VBA uses Subs & Functions while in C# uses Functions
•      Variables C# are declared in one standard way while in VBA you opt to use Dim function, for example in C# an integer will be declared like ( int firstNumber ) if we want to store a number we will just now write like ( firstNumber = 12) while in VBA we will use Dim ( intFirstNumber as integer ) and then say intFirstNumber  = 12)
Now my question is how we are going to deploy an interface written in C # connecting to MS Access Back End through Sql scripts to our clients.
Currently all our interface are based within Ms Access (FE), we simply tell our client to download Ms Access runtime for all the users and put the Backend on the server while all the users will have their FE installed on their machine.
Any idea how to deploy this C# interface based application to the clients??????????

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.