Microsoft Access

221K

Solutions

52K

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

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
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
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
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
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
I want to create a search term generator using vba.  I have a table with several records each containing a different search phrase, [Search].[Phrases].  I want to randomly concatenate the records with a space between each to generate one long string that is close to but not exceeding 250 characters long.  Can anyone help on how I would do this with VBA in ACCESS 2016?
0
We've just had every user of our Access 2016/Office 365 runtime software package called Job Tracker suddenly see a bright yellow banner with the wording:

UPDATE AVAILABLE Updated for Office are ready to be installed, but first we need to close some apps Update Now (big button)

We've cloned our domain controller and our Remote Desktop Session host and gone in as a typical user and finally pressed the Update Now button.

Nothing visible happened, but the banner still comes back when they login again. Presumably the Office 365 runtime is beyond the scope of these updates??

Does anyone know how to remove this notification permanently?
Yellow UPDATES AVAILABLE banner Access 2016
0
Hi

Please note, I am looking for Access VBA code which can open outlook (if it is closed), send email to group of people and close outlook.

Greatly appreciate your help on this.

Thank you
A
0
MS Access unable to see files moved from 2008 SBS to Server 2016.  The MS Access app is 16bit, running on Windows 7 32bit with Office 2016, accessing data files on Server 2016.

The files are present, can manually open them in Notepad as Domain Admin or one of the Domain Users without any issue.
The File Path is correct in the MS Access App
       * I can create a new record entry, this new record is created in the correct folder on the new 2016 server
       * I can pull up “History” in MS Access, it will only show new files created, not any of the others that came from Server 2008, yet are in the same folder.
       * MS Access does not error with a security warning, just states there are no files to be read if no new records are available.

I have manually reviewed Security settings comparing to the 2008 Server – It all matches
       * Compared the Security against what the MS Access application creates, they match.
       * I have gone through using iCacls.exe to set permissions again including Synch
       * I reviewed SMB on the old 2008 server to confirm SMB1 was not enabled and being used by MS Access
       * I wiped out the files and attempted a normal copy – no change
       * I have confirmed Access Based Enumeration is not enabled on this folder

If I use the exact same syntax with RoboCopy from the 2008 SBS Server to a 32bit Windows 7 system, change the data path being referenced by MS Access, MS Access will see the old files correctly.

Thank …
0
I'm developing in MS Access (VBA) and trying to capture Smart Card (CAC/PKI) certificate information using VBA.  I simply need Friendly Name or Subject for each certificate, as I can see in ActivClient user console - the program I use to manage certificates.  I know MS Access uses the same certificate to add Digital Signature to a database, and in the certificate selection utility, the fields that have the information I need are Subject and Friendly Name (the same as in ActivClient user console).

I've looked around and keep finding X509 store information, but it's based on .NET and C# code, which I do not know how to use in MS Access.  I cannot make a dll from that code, as I am not running Visual Studio or any other programming applications.  Also, the X509 Store is not in any library reference, so I cannot create an object and use it in VBA.

Does anyone know how to get or read certificate information - using VBA and any readily available references?  BTW, the references I've tried loading were as follows, but I couldn't make any headway with them.

ac.activclient.olaaddin 1.0 Type Library
CertCli
CertEnc
CertEnroll
GrooveCryptoServices

Thanks for any help/guidance!
0
Pretty sure I've dealt with this before but alas, no memory!

Using Access 2003

I have a table with 2 fields, a date field and a memo field I need to insert into a 2nd table with the same field name and type. A date exists in every record but in several of those records the memo field is blank.

I only want records with both date and memo to transfer but the memo field is particular when it comes to criteria. Conventional syntax like Not Null or <>" " doesn't work.


What's the trick?
0
Anyone have some existing VBA code I could use in Microsoft Access 2016 to add a new calendar event to Google calendar?
I have looked all over the web already, and I have not found any.
Thanks for your help.
Kevin Rea
Lancaster, California
0
How do I let a user select an printer for an access report?
0
I had this question after viewing Microsoft Access Form With A SearchForRecord Macro In A Combo Box Appears To Be Spontaneously Deleting Records.

I get an error message:  Run-time Error '3622':
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

Here's my code:  

Private Sub cboFacilityID_AfterUpdate()

'Old way of not displaying warning messages: 
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "PowerlineF_PowerlineParticipationAppend"
'DoCmd.SetWarnings True

"""""""""""""""""""""""
'New way:
CurrentDb.QueryDefs("PowerlineF_PowerlineParticipationAppend").Execute dbFailOnError

FacilityPowerSF.Requery

End Sub

Open in new window



Can anyone provide details on that?
0
ScreenshotI'm trying to create what I expect would be a very simple query in Microsoft Access. I've added a screenshot that shows the query for reference. I have one criterion specified the references a date range on a form, which causes the query to select all matches records, which works fine. There are multiple other columns which are true\false that I would like to design the query to select if any one of those has a "true" value. I thought this would be very simple... basically if ANY of the records have a true value for ANY of the columns, then then that record would appear in the results, however all records where there wasn't AT LEAST one true value across all of the columns would be excluded.
0
I am posting form data to a URL using VBA from MS Access.  However, I need to end up on the URL that I'm posting the form data to and I can't seem to do this since the url ends in .php.  How do i land on the same URL that i just posted to?
       
URL = "https://websiteX.com/tastein/enterxx.php"
        Set IE = Nothing
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Navigate "about:blank"
        IE.Visible = True
       
        Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
       
        xmlhttp.Open "POST", URL, False
        xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        xmlhttp.send (sPayLoad)
        Response = xmlhttp.responseText
        While IE.busy
        DoEvents
        Wend
        IE.Document.Write Response
0

Microsoft Access

221K

Solutions

52K

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.