[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Hi Experts,

I'm middle of converting my Access BE database to MS SQL Server and at the first glance users realized filtering forms are much slower.

A- Is there a magic/trick to have things run faster (or at least how they used to work) upon conversion?
B- Can someone help me convert this Access query to SQL to run it in the most efficient matter?

SELECT Skilled_Nursing_Visit_Note.ID, SNV_Printed_History.VendorsID, SNV_Printed_History.SNV_ID, Skilled_Nursing_Visit_Note.Client_Last_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, Skilled_Nursing_Visit_Note.Visit_Date, Skilled_Nursing_Visit_Note.Shift_From_Hour, Skilled_Nursing_Visit_Note.Shift_To_Hour, Skilled_Nursing_Visit_Note.Date_Signed, Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV, Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, SNV_Printed_History.ReviewedBy, SNV_Printed_History.ReviewedDate, SNV_Printed_History.PrintedDate, SNV_Printed_History.PrintedBy, SNV_Printed_History.PrintedDate, Skilled_Nursing_Visit_Note.Client_First_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, Skilled_Nursing_Visit_Note.Date_Of_Birth, Skilled_Nursing_Visit_Note.Shift_From_Minute, Skilled_Nursing_Visit_Note.Shift_To_Minute, SNV_Printed_History.ReviewedDate, Skilled_Nursing_Visit_Note.Treatments_Administered, CInt(IIf([duration]=0,1440,[duration])/60) AS DurationHr, SNV_Printed_History.NoPrint, Skilled_Nursing_Visit_Note.Status, Skilled_Nursing_Visit_Note.Client_Last_Name_Init, 

Open in new window

0
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

I have an extract of data which I've imported into access which represents represents login/logoff activity of users/pc's. We have some suspicions that officers may be sharing accounts & passwords, which is a breach of policy. I wanted a way in access to query the data , to list all rows of data, ordered by date, whereby on the same day, the same account is logged into more than 1 unique machines. At present the key columns are field1 (the date, in the format dd/mm/yyyy), field 6 (the PC the account is logged onto, which in the format PC12345 for example), and field3 (the users account, in the format of text, username, e.g. jbloggs). If the account is logged into more than one unique PC, on the same day, I would like to see those records for further investigation. What would be the best way to filter the data to list all rows which fit this criteria.
0
Previous Day Closing as Opening for the next day. A simple query or report with a cutoffdate to get current balance. Access Database

Below is my table and expected result for 3 day mix transaction:

TableInitial
Tdate       Trxn           Amt
-----------------------------------------
Day1        Initial           100

TblCStock
Tdate       Trxn         Amt
------------------------------------------
1Day        Issue          50
1Day        Issue          10
1Day        Issue           5
1Day        Recieved    40
1Day        Recieved    50
2Day        Issue           10
2Day        Issue           10
2Day        Received    80
3Day        Issue           20
3Day        Issue           20
3Day        Received    10
3Day        Received    10


 
Qry/RptTblCStock
Addl row to be added below each nextday 1st  transaction date.
Legend:

1Day =
Result = Initial - Issue + Receive

2Day = Result - Issue + Receive
and next day

Tdate       Trxn         Amt
1Day        Initial        100
1Day        Issue          50
1Day        Issue          10
1Day        Issue           5
1Day        Recieved    40
1Day        Recieved    50
2Day     Result            125
2Day        Issue           10
2Day        Issue           10
2Day        Received    80
3Day     Result           185
3Day        Issue           20
3Day        Issue           20
3Day        Received    10
3Day        Received    10
4Day     Result            165

Tnx in …
0
Getting an error message: 'Microsoft Outlook - Sorry, something went wrong.  You may want to try again'

This only appears when sending to one specific client from MS Access, all other client emails work fine.

Any ideas how I could fix this?
0
I am not sure where to place my coding. I have a text box is being used as a less or greater than statement to pull values from two fields to filter data on the next page or form.

I put the following statement in the new page where it suppose to filter the data, written as following and I have criteria set in query that is making the list box display blank.

Private Sub Form_LOad()
        If Len(Form_002_Criteria.TxtExp & vbNullString) > 0 Then
            Me.Filter = "ExpMin <= " & Form_002_Criteria.TxtExp & " AND ExpMax >= " & Form_002_Criteria.TxtExp
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
End Sub

Open in new window

0
Is there a way to change the background colour of the main database window in Access 2016? I'm referring to the overall window with the ribbon on top. In Access 2010, there were three options for its colour scheme: blue, silver, black.

Thanks in advance.
0
I have a continuous form where I have a bound checkbox which I want to use for allowing users to select records and press a button to open a form with the selected records. After the second form is open then I clear the checkboxes. The form works perfectly when one user is using it, but when several users are using it does not work properly- different users' selections are mix and matched. How can I solve this problem?
0
Hi Experts,

I have the following code which loops thru all fields from a text data file (csv), reads the value and constructs a string.
Would like to have the following modification.
For each field containing a date/time value, check if the time is midnight like "2018-08-01 00:00", in that case should change the time to either 24:00 or 00:00 AM

For Each ColumnName In columnsName

                    fieldName = Replace(ColumnName, " ", "")

                    fieldValue = Mid(columns(c), 2, Len(columns(c)) - 2)

                    patient(fieldName) = fieldValue

                    c = c + 1
Next

objHTTP.SetTimeouts 0, 0, -1, -1
objHTTP.Send JsonConverter.ConvertToJson(patient)

Open in new window

0
I get this error when i try to connect an access database on the network to WinSQL

sqlError.JPG
0
First time working with Access. I have a form that has 176k records. Basically is actually 9810 records 545x18. Then the data is being multiple again by another table is 176k. There 18 locations that have 545 records each. The selection form has 2 combo box and a list box. The first combo you choose a region, then it cascades to the second combo box to show locations, and last it cascades from the 2nd combo to the list more specific locations  to choose. When I click the submit button to go on to new form or page it filters from 176k to 9810, when it suppose to filter to 545. Is what I did so far https://www.google.com/url?sa=t&source=web&rct=j&url=https://m.youtube.com/watch%3Fv%3D3ljoKaS6M9Q&ved=0ahUKEwiEyNmNr9DeAhW5HTQIHZh2D0IQo7QBCCwwAw&usg=AOvVaw0KiyDpoFTnI5fnncgJUDBf

Is there any codes that someone can create as an example?
0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Using MS Access 2016 I am writing a VBA script to loop through an application window to retrieve the text/values. I was hoping someone could help me understand why a text box would have two classnames and id's (hwnd). Using AutoIt the classname that is the same as the main window also shows the name of the control (textbox). The other classname for the textbox has the word Edit in it but the control name is blank.

I can get the text that is in the textbox but I also need to get the control name so I can identify what its for.

Hope this makes sense.
0
I am using Access 2003 (yes I am no jokes).... I have a Combobox to look up information... (date of birth). Users types in the date and bingo we find a match and goes to the correct record.

The issue I'm having is that when a user starts to type and then deletes the initial entry Access still wants to look up the data in the table.  Currently I have the combo box set with a row source of: SELECT tMain.DOB, tMain.LName & ", " & tMain.FName, tMain.TrayNo FROM tMain ORDER BY tMain.DOB DESC

I think I need to somehow move this code out of row course and fire it after an update.  Does this sound right and if so how I can use VBA to execute the search when there are at least 8 characters in the combo box to start the search?  I was thinking in the event "after update" to check the len and if over 8 set the row source in the combo box to SELECT tMain.DOB, tMain.LName & ", " & tMain.FName, tMain.TrayNo FROM tMain ORDER BY tMain.DOB DES??? Doesn't seem like the best approach.

Thank you.
0
I'm looking to see if there is a way to connect Access 2016 to SQL Express database using an OLE DB connection.
What I need to do is
 - import data into the SQL Express tables
 - create a form to update the SQL data
 - create queries with calculated fields
 - write reports

As of now I have the database created on my local C drive (C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\mydb.mdf) but in the future it will reside on the network with multiple users who will have access to the database and I don't want to have to create and ODBC connection.
0
While writing a custom ribbon for Access, I'm having troubles removing 2 elementsin the info tab of the backstage. See attached image, I would like elements circled in red to be removed.

RibbonCreator did not help much as it hardly support the backstage view (or is that just the shareware ?), and isMso provided by Ribbon Wizard were frowned upon by Access (GroupFileProperties and GroupFileLocation).

Below, the XML I obtained:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <commands>
         <command idMso="Help" enabled="false"/>
    </commands>
    <ribbon startFromScratch="true"/>
    <backstage>
         <tab idMso="TabInfo" visible="true">
              <firstColumn>
                   <group idMso="GroupDatabaseCompact"/>
                   <group idMso="GroupDatabaseEncryption" visible="false"/>
                   <group idMso="GroupFileProperties" visible="false"/>
                   <group idMso="GroupFileLocation" visible="false"/>
              </firstColumn>
         </tab>
         <tab idMso="TabOfficeStart" visible="false"/>
         <tab idMso="TabRecent" visible="false"/>
         <button idMso="FileSave" visible="false"/>
         <tab idMso="TabSave" visible="false"/>
         <tab idMso="TabPrint" visible="false"/>
         <button idMso="FileCloseDatabase" visible="false"/>
         <tab idMso="TabHelp" visible="false"/>
         <button idMso="ApplicationOptionsDialog" visible="true"/>
    </backstage>
</customUI>

Open in new window

Access-Backstage.png
0
Hey,

I have a subform in my form. In this subform I have several columns. In first one I have a combo field filled with data from a table A. When I choose a particular value from this combo I'd like to display appropriate value in the next column in my subform as a text field. This value is also from table A. How I can obtain?
0
excel document every time i open asking to save changes even though i have not made changes? how to avoid this message prompt every time(even i made no changes) which i have to click no button
please advise
0
Using Microsoft Access (2003)
I have a form with a text box called PicFileName and an Image Control called PersonImage
The intent is that the following code will cause .jpg (picture) to appear in PersonImage, without the .jpg being embedded in the table behind this form (in order to save space)
The following code accomplishes this perfectly:

Dim strPicName As String
Dim strPicPath As String
Dim strSource As String

strPicPath = C:\Pix                 C:\Pix is the folder containing the jpg
strPicName = ABCD.jpg       ABCD.jpg is the filename of the picture

strSource = "" & strPicPath & strPicName & ""              in this case it will be C:\Pix\ABCD.jpg

If Not IsNull(Me.PicFileName) And Me.PicFileName <> "" Then
    Me.PersonImage.Picture = strSource
End If


As I said this works perfectly in all computers on my LAN, EXCEPT for ONE which returns an error 2114 saying that this code is not supported OR the jpg is too large!  I have reduced the size of the .jpg to a few K, to no avail.

ALL COMPUTERS ON THE LAN USE THE SAME OS (Win 10 Pro), AND THE SAME VERSION OF ACCESS, AND GET THEIR JPG FROM THE SAME FOLDER,  and the above process runs perfectly for the last six years!!

QUESTION: What needs to be adjusted in the one computer where this is not working?
0
Here is my data:

RAW_OEM_DATA
| OEMCurrentPartNumber | OEMDescription       | OEMSubNumber |
|----------------------|----------------------|--------------|
| 19M7796              | SCREW                |              |
| 19M8365              | USE PN 19M7796       | 19M7796      |
| AT256104             | USE PN 19M7796       | 19M7796      |
| CH12356              | USE PN 19M7796       | 19M7796      |
| M134162              | USE PN 19M7796       | 19M7796      |
| M74138               | USE PN 19M7796       | 19M7796      |
| AE42460              | BEARING WITH HOUSING |              |
| DA25307              | USE PN AE42460       | AE42460      |
| AH223030             | USE PN AH227807      | AH227807     |
| AH227807             | FRAME                |              |
| AH208565             | USE PN AH223030      | AH223030     |

Open in new window


FINAL_OEM_DATA
| OEMCurrentPartNumber | OEMDescription       | OEMSubNumber |
|----------------------|----------------------|--------------|
| 19M7796              | SCREW                | 19M7796      |
| 19M7796              | SCREW                | 19M8365      |
| 19M7796              | SCREW                | AT256104     |
| 19M7796              | SCREW                | AT257892     |
| 19M7796              | SCREW                | CH10599      |
| 19M7796              | SCREW                | CH12356      |
| 19M7796              | SCREW                | M134162      |
| AE42460              | BEARING WITH HOUSING | DA25307      |
| AE42460              | BEARING WITH HOUSING | AE42460      |
| AH227807             | FRAME                | AH223030     |
| AH227807             | FRAME                | AH208565     |
| AH227807             | FRAME                | AH227807     |

Open in new window


COMPANY_DATA
| Item       | OEMItem  | Description  |
|------------|----------|--------------|
| AM19M7796  | 19M7796  | Screw        |
| AMAE42460  | AE42460  | Bearing Assy |
| AMAH227807 | AH227807 | Frame        |

Open in new window



The purpose
I am cross referencing OEM part numbers with my companies part numbers.

In the RAW_OEM_DATA table, any rows without a number in OEMSubNumber are the "latest" numbers. All other numbers are sub numbers that reference the "latest" .

The [OEMItem] in COMPANY DATA is the "latest" OEM number. when a user searches a part number my query searches the OEMSubNumber column and brings back the OEMCurrentPartNumber to match with the [OEMItem] in my companies numbers.

To get the raw OEM data to the format in the FINAL_OEM_DATA is what i'm trying to achieve. But I don't need all 1.6 million records in that table. just the ones that match the [OEMItem] from COMPANY_DATA.

Final Result
Both the RAW_OEM_DATA and COMPANY_DATA tables reside on my web server so employees can access via the secure website. It is a aspx site with MS SQL server. Twice a year i have to run my companies new item numbers through this process and update my tables with the new data.


Couple of notes
Some numbers in RAW_OEM_DATA "piggy back" one another. example
AH208565 subs to AH223030 which subs to AH227807
That means not all [OEMSubNumber] will be the "latest" OEM number.

The description for the "latest" OEM number has to replace all the "USE PN ***" numbers


My process in the past
run quite a few queries to get the OEM raw data to the above desired result
0
Reset Auto Number field in Microsoft Access

Hello,

I have a split database with fontend and backend files. There is a table tblinvoice which has a DCID field as a primary key field. This field has relation with another field in a table which is the base table for a sub form. What I want to do is reset the auto number to start from 1 as I have completed the testing and now want to implement the access database. When I did the testing I generated few records which incremented the auto number Field.

Here is what I have already tried:

1) Unlinked the relations between the main form table and subform fields.
2) Remove the field as primary key
3) Create an Append Query which will append the value to 1.

I tried to run this query on the table opening the back end file and also tried to run it on the frontend file.

But its is not working.

Any ideas other then what I have tried above. ? On what file do I need to run the append query - Font End or the back End file ? Can anyone guide me step by step what needs to be done. I am not at all an expert but a starter.

Thank you for your help

Myinfo.
0
Microsoft Azure 2017
LVL 12
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.

Using Microsoft Access 2016 (wish I had another choice)  I am trying to write a script that will loop through an application window and get the values for each textbox, combobox, listbox, checkbox....

I found a script (currently for use on Excel) that goes through all windows but will not return the value/text for any "Edit" control. I intentionally left a "Save As" window open. It can see the Filename combobox and that it is an "Edit" control but didnt return the value. The application I am trying to read the data isnt popular so I figure this approach would be easier than trying to explain the application.

I have tried various sendmessage, FindWindowEx, SendMessageGETTEXT, GetWindowText configurations and am missing something (besides a few brain cells). In short, I could really use some help changing the script to pull the text/value from any control regardless its type. Once this is working I will then change it to work on Access to update a table.


After I am able to read all the controls I plan on being able to update fields and save the changes in the application if that makes a difference in your solution.


First time using this site (I'm sure by the question that's obvious).

Thank you in advance for your time and assistance.

Option Explicit
 
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare 

Open in new window

0
I'm getting a Run-time error 438 - object doesn't support this property or method.
this only happens like every third or fourth time I run the code
Prior to executing, I'm sure all word instances are shutdown
Environment:  Access 2016 - running VBA to manipulate MS Word 2016 document.
All the references are good I believe (included Word object module, etc)

see attached word file and simple database - see module 1 for sample
it generates a 438 on the line
owordapp.activedocument.content.selection.Goto what:=wdgotobookmark, Name:="photo"
Option Explicit
Sub photoproblem()

Dim owordapp As Object, i As Integer, a As String, v As Shape, rng As range, dir1 As String
Dim template As String, newphoto As String

dir1 = "U:\Data\photoproblem\"
template = dir1 & "SAMPlE FILE.docx"
newphoto = dir1 & "dolphin.png"


Set owordapp = CreateObject("Word.Application")

With owordapp

    .Documents.Open template
    .Visible = True
    .screenupdating = True
    
End With

owordapp.activedocument.content.selection.Goto what:=wdgotobookmark, Name:="photo"

Set rng = selection.range

Set v = activedocument.inlineshapes.addpicture(FileName:=newphoto, range:=rng, savewithdocument:=True).converttoshape

Set rng = Nothing
Set owordapp = Nothing

End Sub

Open in new window

Sample-file.docx
photoproblem1.accdb
dolphin.png
0
Hello,

I am running into quite an unusual problem and was wondering if anyone has experienced this issue before. I have an ActiveX Control that I have made that uses a Picture Box and displays an image from Azure blob storage by converting that image's memory stream into an image. I use this ActiveX Control for a Microsoft Access 2002 application. This ActiveX Control works great on forms, but on reports it doesn't display fully (Please see attached images). Anyone know why?
ActiveXFullyDisplayed.PNG
ActiveXNotFullyDisplayed.PNG
0
Dear All,

I have a few questions regarding Microsoft Access reports (I am using 2016, but the same issues exist in 2013).

i) let's say I have created a report using the report wizard and when prompted I selected stepped for its layout. I have created and saved it, but my boss doesn't like it and he wants to use outline instead; I could not find a way of changing the layout, it seems to be only possible only through the Report Wizard or do I need VBA (or is it just not possible)?
ii) I have created a report with groups and I want the report header to be with the first group and the report footer on the same page after the last group. I am using Force New Page equals none for the group header and after each session for the group footer. This does the trick for the header but not for the footer. is there any way this can be achieved?
iii) (form question) when someone selects Justified as the form layout, would anyone be able to tell me where is the default value for the form's width (there doesn't seem to be anything like Normal.dot for Access, would that be in the Registry)? Also, if you have Justified layout and you change the width of the textfields, it looks like Access is not picking up automatically the new dimensions, but you would have to close and redo the form - any idea why this is or am I doing something horribly wrong?

Regards,
George
0
Is there a way to NOT ALLOW a user to add to the dictionary when they run spell-check?  In other words disable the [Add] button?

Spell Check
0
I need help with updating my form coding on Microsoft Access to make sure everything works properly. I would prefer if someone remotely connects to my computer.
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.