Microsoft Access

220K

Solutions

51K

Contributors

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

Using MS Access.

Ideally this whole thing would run under a single query but that's too complicated for me now, so currently I've broken it up and am dealing with 2 separate queries. What I'm looking for is a solution to query2.

A simplified version of the results of query1 is as follows. The first row are field names followed by data

State                     Location       Amount
Alabama              County             100
Alabama              City                   150
Alabama              Town                150
Alabama              City                   200
Arkansas             City                    500
Arkansas             City                    400
California            County              100
California            Town                 200
California            County              400
California            City                    300
California            Town                 600
New York            County              100
New York            County              500
New York            County              300
New York            City                    200
New York            Town                100
etc

Expected results:

State                 Location              Amount
Alabama         City or Town           500
Alabama         County                    100
Arkansas         City or Town          900
California        City or Town        1100
California        County                    500
New York        City or Town          …
0
Get Blueprints for Increased Customer Retention
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

I have a client with an Access-based inventory system with very complicated pricing. The pricing is based on a query that performs a lot of calculations based on several tables. The pricing query is set up to work on all the records in inventory (about 50,000 items).

This query is used in pretty much every inventory report.

The problem I have is that even if a report has only a handful of items on it, the underlying pricing query seems to be calculating pricing for every item in the database meaning that every report is now becoming immensely slow.

As a test, I ran a report of all items for a vendor and I chose a vendor that only has 5 items. It was extremely slow. Then I deleted every item in the database except those 5 and the report was lightning fast.

In this example, the report is based on a parent query that pulls vendor and item data (including the pricing query) and the parent query has a record source that specifies just the one vendor. However, it appears that the pricing query is running on every item in the database and then subsequently being filtered based on the parent query.

How do I get Access to only run the child query (i.e. the pricing query) for the items that are on the report? I should add that the pricing query is a child query in dozens of other reports in the database so I'm hoping for a solution that isn't going to require a lot of coding dozens of times over.

Thanks in advance.
0
Hello Team

I have a MS Access database that links to multiple outlook folders.  

One of the folders that I would like to link to is a custom search folder (Emails Per Day)  that captures and counts how many emails are received into my mailbox each day.  

When attempting to link my MS Access database, I can't find the custom search folder I created.

I am thinking that it may not be possible to link to it as it is not visible on the Import\Exchange Outlook Wizard in MS Access.

Can you please advise if it is not possible to directly link via the wizard, is there anyway around this, e.g., VBA?


Thanks in advance to any responses received.

Sincerely

Dale
0
Hello experts,
I've read the 2018 book 'Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners' by Dmitri Korotkevitch, but still I can't tell what the best choice is.

There's a 18-year-old MS-Access huge ERP application, which is now in my responsibility to 'upsize' to SQL Server. Up till now it's been an Access FE working with an Access BE.
The app is to be used simultaneously by 5 to 50 users, depending on the client. The are small ones out there and there are big ones as well. The company has been selling this app as a commercial off-the-shelf software.

The most critical point we have to think about is concurrency: Dozens of users are going to 'shoot' dozens of inserts each minute, others will update existing records from the same tables at the same time. It's a beehive full of hectic activity all day long. They all have to see a true state of the data in every SELECT statement they might be doing. They occasionally run some ad-hoc reports that are needed for further decision making along the workday. The application won't be split into OLTP and DWH in the foreseeable future. So the reports are here to stay.

I have some experience with converting small Access applications to work with SQL Server. I've always been using the default Read Committed Isolation Level, and I admit to have been a sinner, using 'WITH (NOLOCK)' in some of my SELECT statements. Now in this big project I know I can't get away …
0
Using MSAcess VBA  to open Windows File Explorer in a specific folder with a specific filter. Online I see many instances of generating a file picker, that is not what I want. I Want the list of files to be displayed within Windows File Explorer as if I had manually filtered it.

The attached file shows the VBA code that I have so far...........basically I have the filter created and now I need to add the code to open Windows Explorer with the filter.
RO_Image_List.txt
0
Are there any ideal locations/best practices on where to store the backend database behind access DB databases?

Do you put them on a dedicated server, or is that overkill (thinking cost vs benefit).  Need to investigate the costs of a bare bones  windows based VM with access installed.

We have inherited an access based app, of which the backend of the database is only approx. 100 MB in size, so very trivial really. However, the backend DB is just located on a file server alongside about 1 million other documents. Permissions are restricted to only users of the application, however, but its still on the same drive as the other teams data.

We don't have local admin rights over the server so troubleshooting file locks and the like requires another team to investigate who prioritise such a task accordingly in line with other ICT incidents and it can take hours to resolve something that would take approx. 30 seconds!

Are there any other risks / issues / concerns you can foresee in storing the backend DB on a general file server? I am trying to build a case for moving it, but don't have too much in terms of issues on why to do so, or where too.
0
MS Access (Office365) performance question

I have a process which requires saving up to 1000 records in a MS SQL Server backend database.
Currently I collect the data for each record, and then use an insertinto command, once for each record.
This process takes a very long time... up to 7 minutes on very fast windows10 computers with a windows 2016 server, although the ms sqlserver is running on a 3GHz win7 workstation with 32MB.

Is there a faster way to save 1000 rows than using the InsertInto command once for each row?

Thanks
0
I need to append data to an existing Excel files worksheet

1:lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

I have the following code provided by Daniel Pineault

Function ExportRecordset2XLS(ByVal rs As DAO.Recordset, _
                             Optional ByVal sFile As String, _
                             Optional ByVal sWrkSht As String = "Hardware", _
                             Optional ByVal lStartCol As Long = 2, _
                             Optional ByVal lStartRow As Long = 7, _
                             Optional ByVal lLastRow As Long, _
                             Optional bFitCols As Boolean = True, _
                             Optional bFreezePanes As Boolean = True, _
                             Optional bAutoFilter As Boolean = False)
    '#Const EarlyBind = True    'Use Early Binding, Req. Reference Library
    #Const EarlyBind = False    'Use Late Binding
    #If EarlyBind = True Then
        'Early Binding Declarations
        Dim oExcel            As Excel.Application
        Dim oExcelWrkBk       As Excel.Workbook
        Dim oExcelWrkSht      As Excel.WorkSheet
    #Else
        'Late Binding Declaration/Constants
        Dim oWSHShell
        Dim oExcel            As Object
        Dim oExcelWrkBk       As Object
        Dim oExcelWrkSht      As Object
        Const xlCenter = -4108
    #End If
    Dim bExcelOpened          As Boolean
    Dim iCols                 As Integer
    Dim lWrkBk              

Open in new window

0
I need a function/macro that will randomly pick a video link from a database but have no clue how to get started, can anyone help please?
0
I'm using MSAccess with Office365 to run an application created with MSAccess 2K.

I have a routine that runs from a form until a stop button is pressed.  This routine worked fine in Access2K, however in Office 365 I get the following message:

The Macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing <dbname> from saving the data in the field.

In fact, I get this same message when I attempt to click any control on the form.  I'm pretty sure that none of the controls use the BeforeUpdate event, and I know I am not using any macros.  I do use a number of DoEvents throughout the code, which are intended to allow access to check a flag that indicates the stop action has been requested.

I need to solve this problem so I can stop the routine!  Please help.
0
Your Guide to Achieving IT Business Success
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

i have this line of code in some procedure,i would like to replace the line of code with the procedure name
Const PROC_NAME As String = "TEST_RUN" should be replace with Const PROC_NAME As String = "function or sub name"
0
Hi,

Is it possible to show description field on the same field on the form in Microsoft access 2016? I already show the text in the status bar in the form but I want to show the description in the same field. Lest say, I want to write in the description in the field " please enter your name here"

How I can do that?

Thanks
0
I have a need to assign employees to positions.  Lets say I have table1 with EmpID and PositionCat
Table1:
EmpID   PositionCat
1             A
2             A
3             A
4             B
5             C
6             C

Then we have Table2 with PositionID, PositionCat, Position
Table2:
PositionID   PositionCat   Position
1                   A                     A1
2                   A                     A2
3                   A                     A3
4                   B                     B1
5                   C                     C1
6                   C                     C2

What I want to do is programmatically assign the employee to the position in Table3.
Table 3:
EmpID   PositionID
1             1
2             2
3             3
4             4
5             5

Any ideas on the best way to handle this?
0
i used a sql command in Access vba code to add a record into a table ie INSERT INTO Results '  .  now i want to read another table that has 1 field in it and if it is a number add one to it and save it again .  if it is not a number then do not do anything with it.

your thoughts?
0
Access 2013 using combo box wizard with a linked table getting error  message 'no valid fields can be found'   See attachment
Linked-table-for-customer-combo-box.jpg
0
I am running a query in Access via VBA (actually a lot of them), they look more or less like this one:
CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET Status = ""Completed with rejects"" WHERE ID = " & RequestId
RequestId is basically the ID of the record.
I am able to make it work from the Immediate window  most of the times, sometimes only it does not work in VBA break mode.
I am not able to make it work from the module, at least in most of the times.
At first I thought it is something with quotes (", '), so I experimented with different settings and for a moment it worked in the form showed above, but after a few tries it stopped working.
Then I thought it is something with locked records, but it did not get me anywhere.
I tried using options like dbSeeChanges and dbFailOnError, but it did not help.
I never see any error message from VBA, the record is simply not being updated.
I would be grateful for some insight, since I am currently stuck with this issue.
Access ver. 1904, MS Office 365 ProPlus
0
I have a complex code question.
 
I did have the list box (LstProvNPI) as a single select only that worked flawlessly.  
Once a user selected a row, the data would populate fields within in my form.  
Once the record was saved a SQL insert statement would update the record back to tables in SQL.

The problem is now the user would like to multi-select, I do not know how to do that.  
I can no longer have the Dynamic list box populate fields.  What is the best way to do this?

Step 1:
When a member ID is searched from a form menu, the LoadMember Function is loaded when my form opens
Step 2:
The Load Member will display a list of choices in a unbound list box.
Step 3:
The on LstProvNPI_Click() property within the list box allows the user to select the entry and this will populate the fields on the form.
Step 4:
When a user clicks save, A call module will call a function to Insert date from all the fields on the form to SQL.
 
Step 1:
LoadMember
Function loadMember(HID As String)
Dim server      As New SQLServer
Dim rs          As ADODB.Recordset
Dim SSQL        As String
Dim n           As Long
n = 0

If Len(HID) = 9 Then
    HID = HID & "00"
End If

Call server.initializeToRSMS(False, False, True)
    SSQL = ""
    SSQL = SSQL & "SELECT DISTINCT HumanaID, " & vbCrLf
    SSQL = SSQL & "         [Last_Name] + ', ' + [First_Name]   AS [Member Name], " & vbCrLf
    SSQL = SSQL & "         Last_Name, " & vbCrLf
    SSQL = SSQL & "         First_Name, " & 

Open in new window

0
Dear EE,

Can you help to share info about any third party software to monitor any program exe, e.g. (Winword, or any software exe) is running or not and if not running then send email to a user.


Thanks
0
I have a problem that has been Happening for years. It certainly
not alarming but I just want to clean up our program.

In a part that deals with a/r and check amounts I am running many
functions and I believe that one or more of them is getting interrupted
and thus produces incorrect results. This program is networked and
approximately used by 10 people at a time. Because there are many data
bases that are modified by these functions I was wondering if someone else could
unknowingly  be interrupting the functions?

I know that Access has a function to deal with this but I can not remember is name.
I am hoping some one could give me some guidance on a solution.
0
Amazon Web Services
LVL 13
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

We are encountering an intermitent error upgrading a legacy Access 2003 application to Access 2016 for a client.  

Our office has done dozens of upgrades from 2003 to 2013 in recent years but this is the first one to 2016.  I don't know if the fact that this is going to 2016 is a factor, just thought I'd mention it.  When doing the upgrades we know what needs to be changed and all the 2003 to 2013 upgrades have gone smoothly.  Most of the functionality in this 2003 to 2016 is fine but one menu option is causing issues.  Issues I've never encountered before.

Sometimes the menu option will run fine but sometimes I get the following two messages when it executes.  Then the application shuts down.

Access Stopped Working
When I select the 'Check Online for a solution' I then get
Access not working 2
I have error handling in every procedure in the application but the error handling routines are not being invoked. When I get the Access has stopped working' message.

This error occurs on a client machine that is running Access 2007.

My first thought was to ask the IT support people to re-install Access but I wanted to see if the EE experts had any other ideas.

We have thoroughly tested this application in-house (and this menu option dozens of times) and never once had any issues or failures.  We use an exact copy of the clients data to do our testing.  

I realize I've not supplied specifics about the functionality of the menu option but I thought these …
0
I want to create a dashboard in an Access application which connects to data store in SQL Server. I have created a mock up of how I would like to display a table reporting actual to goal for employee hours.  The SQL for the actual and goal is no problem. I am looking for advice as to how you would build the query and then display it. Would you create a table-valued function or take some other approach?

 Employee hours - actual to goal.
0
MRO Report CodeI have a drug screen database that is using Number Types zeros and ones for substances that are tested and blank if not tested. I need to create a report that will return specific substances tested for each person and the result (i.e.: if 0 the substance was tested for, and it is negative, if 1 the substance was tested for, and it is positive, if blank the substance was not tested for.

Microsoft Access 16. I have report working for DOT results because they always test for the same substances, which I have each substance field in my report. I cannot get non-DOT to work because there are many possible substances that could potentially be selected for testing. I would like the report to pull for each person what substances they were tested for (only. i.e. each company tests for different substances) and the result.

A line for each substance that can be tested for DOT (i.e.: Marijuana, Cocaine, Amphetamines, PCP, Opioids, etc). Non-DOT has many more potential substances.
=IIf([Amphetamines]="1","Positive",IIf([Amphetamines]="0","Negative","Cancelled"))
Header:
=Switch([Negative]="1","NEGATIVE" & IIf([Dilute]="1","/DILUTE","") & "  RESULT",[Positive]="1","POSITIVE" & IIf([Dilute]="1","/DILUTE","") & "  RESULT",[Cancelled]="1","CANCELLED TEST")

I can't figure out how to have the report only list substances specific to a test. I only want the specific substances that are tested to return on the report. On the DOT all substances are set always (never …
0
We have a financial application with a SQL backend.  There is a users group for the application users that allows them to write/edit the data in the tables.

Now, ownership has come to me and wants me to allow a member of that application user group the ability to access the tables via ODBC and MS Access, but have read-only permissions on the tables.  Every thing I've tried so far does not work.  He still can edit the data in MS Access.

Any suggestions?

Thanks!
0
My database is divided into a front-end and back-end. I regularly make updates to the front-end and then copy the front-end from my computer to my client's computer. Today, I added an attachment field (i.e. data type = attachment) to one of the tables in the back-end. It works fine on my computer. But when I copy the front-end to the client's computer, I get this error whenever I attempt to open a form that references the table with the attachment field in it:

The Microsoft Office Access database engine cannot find the input table or query ''. Make sure that it exists and that its name is spelled correctly.

When I go into the Linked Table Manager, it says it is pointed to the right back-end file. But if I check the box beside that table and click OK, the above error goes away and I can use the database without any further problems.

Or, if I execute this command:

db.TableDefs("tblCustomers").RefreshLink

it also fixes the problem. But I have to do that every time I copy a new front-end to the client computer.

If I delete the attachment field from the table, the above problem goes away and I can copy front-ends without any errors or the need to refresh links. But as soon as I re-add an attachment field to the table, the problem comes up again.

Any suggestions?
0
Access 2016 application:  Suddenly (I certainly did something dumb) in the Left Objects Pane no more Macros appear.  They should be there, I created them.  Tables, Queries etc... are still there but no Macros!

Also in the older versions, in order to make a specific Form appear on startup, that was done from the "Startup" menu.  How do we do this in 2016.

Thanks

Biggles1
0

Microsoft Access

220K

Solutions

51K

Contributors

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.