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'm moving an application from a dedicated Windows Server / IIS to Azure with SQL Database and App Service.  The existing application runs on MS Access "server" with Classic ASP pages and it is pretty speedy.

The following query on existing server for example returns all records in about 3 seconds.  There are 36,720 records in the "loads" table.

Now that I've moved the DB to Azure SQL the records are returned in about 35 seconds to the Azure App.  My current Azure config is Serverless Gen5, 2-8 vCores, 6GB-24GB memory.  After increasing the compute power I noticed the results returned faster, so I suspected poor query design.

Question:  Can the following query be optimized further?  I expected much better performance on SQL Server vs. MS Access "Server"

SELECT        dbo.loads.ID, dbo.loads.CustomerID, dbo.loads.CustomerContactID, dbo.loads.RequestDate, dbo.loads.Equipment, dbo.loads.Commodity, dbo.loads.Stops, dbo.loads.Miles, dbo.loads.CommentsPublic, dbo.loads.GrossQuoted,
                         dbo.loads.Weight, dbo.loads.Length, dbo.loads.Width, dbo.loads.Height, dbo.loads.CargoValue, dbo.loads.CommentsPrivate, dbo.loads.UserID, dbo.loads.Created, dbo.loads.forfeitload, dbo.loads.voidload, dbo.loads.shipperid,
                         dbo.loads.shippercontactid, dbo.loads.shippercomments, dbo.loads.consigneeid, dbo.loads.consigneecontactid, dbo.loads.consigneecomments, dbo.loads.usforwarderid, dbo.loads.usforwardercontactid,
                         …
0
one of our existing forms will no longer let us input new data.   It definitely has not been modified in a long time.   The only difference is we went from Access 2003 to 2010.    
The backend is SQL.

When we try to enter a new name, it displays "the field cannot be updated"
0
A kings ransom to anyone who can help me get around the 18MB files size limitation when embedding documents as embedded OLE Objects within the MS Access / VBA user environment.
0
The http address specified in this code generates a new 10 digit number every time it is called in a browser, however, when calling through this code I only get a new number if I completely close the access program and re open it. I assume that the connection to this site is not closed and so each time it uses the same response as before but I cannot find anything about how to close a connection or to start a new connection with MSXML2.XMLHTTP. Is there someway to do this?

Private Sub Report_Load()
'Houma: http://10.10.1.29/unifi/voucher-houma.php

'Port Allen: http://10.10.1.29/unifi/voucher-pta.php

Dim objWeb As Object
Dim URL, wrq As String

' Instantiate an instance of the web object
Set objWeb = CreateObject("MSXML2.XMLHTTP")

'Set URL
If Environ("Username") = "VIMS-HMA" Then
    URL = "http://10.10.1.29/unifi/voucher-houma.php"
ElseIf Environ("Username") = "VIMS-PTA" Then
    URL = "http://10.10.1.29/unifi/voucher-pta.php"
Else
    URL = "http://10.10.1.29/unifi/voucher-houma.php"
End If

' Pass the URL to the web object, and send the request
With objWeb
    .Open "GET", URL, False
    .Send
    If .ReadyState = 4 And .Status = 200 Then
        wrq = .responsetext
    End If
End With

Me.WiFiKey = wrq

' Clean up
wrq = ""
URL = ""
Set objWeb = Nothing
    
End Sub

Open in new window

0
I have a form that has a sub-form.  The sub-forms record source is a query and is displayed as a datasheet.  But what I'm trying to do is populate a field on the main form with data from the query in the subform.  But I need to populate the information as, for example, "Steve, Bill, David".  In other words, the results of the query separated with a comma and a space.

So if the query looks like this:

Names Example
Populate the field on the main form with "Steve, Bill, David"
0
Hi

What Access VBA code would I use to apply some text to a text box that is rich text format.
I just want to do a bold header that says "Header Example" followed by some text on a new line for example "and some normal text goes here"
I want to use VBA to apply the text on the form load
0
Hi

What Access VBA code would I use to check if a linked table is in another Access database that is in the same folder as the current database?

Thanks
0
Hi
I have an Access database with linked tables in another Access file that is in the same folder called "Backend1.accdb".
When I distribute the Access database I want to automatically loop through and relink the tables to the new location of the backend
which will always be in the same folder as the current database. What VBA code would I use?
0
Hello Experts,
Finally getting around to splitting our company's Access dBase that functions as our ERP system (customers, products, orders, etc.).  The process seems pretty straightforward but I have a few questions.

It's my understanding that I will be able to create different front ends with this split database.  I will need to create a front end for sales (2 computers), one front end for production operators (2 computers), one front end for engineers (6 computers), and a front end for me (everything).  Some of the forms/queries will be needed at all stations, for example,  (f-WorkHours).  Other forms will be group specific (Sales: f-Customers, Engineering: f-PartSpecifications, or Production Operators: f-Throughput).  

We have ~ 45 current forms, 30 reports, and 80 queries in our current database.   What's the best way to manage this undertaking so that I support:
1) group changes (the ability to deliver timely and consistent f.e. changes to an entire group of users), and
2) revision flexibility (the ability to rev a single query or a form, say, f-customers) that might be used by one or all groups?

Thanks in advance.
0
I need to add the missing data part in the serial port VBA Ms Access  code below, I’m trying to convert the VB6.0 code to VBA so that the code can work properly, but unfortunately, I cannot go any further due to knowledge gap here:

Code VB6.0 for
Private Sub cmdWrite_Click()
Dim lBytesWritten As Long
Dim BytesToWrite As Long, wData As Integer
If setFlg = False Then
MsgBox "Please click the Setup Button to do the setup First"
Exit Sub
End If
wData = txtWdata.Text
BytesToWrite = comDCB.ByteSize
openSuccess = WriteFile(hPort, wData, BytesToWrite, lBytesWritten, comOverlap)
End Sub

Open in new window


VBA Code

' Write data to serial port.
    strData = JsonConverter.ConvertToJson(transaction, Whitespace:=3) & Chr$(13)
    lngSize = Len(strData)
    lngStatus = CommWrite(intPortID, strData)
    If lngStatus <> lngSize Then
    ' Handle error.
    End If

Open in new window



Missing data in VBA


Mandatory paramaters required in VBA code
Three local variables are declared at the beginning of this procedure. The first variable,
(1)      lBytesWritten, is the starting address that will store the data written to the serial port.
(2)      The BytesToWrite variable will store the number of bytes to be written to the port, and,
(3)       the wData variable will store the single byte of data to be written to the port.

The reading part of the code are as below:”
VB6.0 Reading Code


Private Sub cmdRead_Click()
Dim lBytesRead As Long
Dim BytesToRead As Long, rData As Integer
If setFlg = False Then
MsgBox "Please click the Setup Button to do the setup First"
Exit Sub
End If
BytesToRead = comDCB.ByteSize
openSuccess = ReadFile(hPort, rData, BytesToRead, lBytesRead, comOverlap)
txtRdata.Text = CStr(rData)
End Sub

Open in new window

Missing data in VBA

As in the Write event procedure, we must first declare three local variables.
(1)      The lBytesRead variable is the starting address that will store the data read from the port.
(2)      The BytesToRead variable will indicate the number of bytes to be read from the serial port.
(3)      The rData variable will store the value of the data to be read from the port.

VBA Code


Open in new window

0
Hi. I have to update the following Access VBA code to work on a 64 bit machine. What would I convert it to? Thanks

Public Declare Function ShellExecute Lib "SHELL32.DLL" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Open in new window

0
Hi
I am trying to run Access VBA code created on a 32 bit machine on a 64 bit machine and the following code comes up red on the 64 bit machine.
I did not create the original code so am not sure what this code does or why it won't work on a 64 bit machine.

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
    Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

The entire module of code is as follows. The rest of the code seems to be acceptable on the 64 bit machine

Option Compare Database

Type tagOPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    strFilter As String
    strCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    strFile As String
    nMaxFile As Long
    strFileTitle As String
    nMaxFileTitle As Long
    strInitialDir As String
    strTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    strDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
    Alias "GetSaveFileNameA" (OFN …
0
Access 2007 database has three related tables that show duplicates when query is ran.  I do not wish to delete these duplicates, but must report on unique records.  Tables are [centers], [enrollment], [date enrolled].  Each center [centers] has students[enrollment] and the students are enrolled with a date [date enrolled].  Students may withdraw from the center [date enrolled. withdrawn], which can cause duplicate  students in my query as students may return to the center with a new date enrolled.  

I would like to hide these duplicate students in a query and can accomplish without the [date enrolled] table, but once I add this table duplicates show and the students qualification is needed for my report which is a part of the [date enrolled] table.  Can you assist me?
0
Hi
I want to create an Access form that can be used on multiple forms to display help text
in neatly formatted paragraphs. I am looking for tips on the best controls to use and the best way to do this.

Thanks
0
In Access 13, I want to use bold text for a section of a control - but the bold codes seen below are simply ignored - italics works fine - very strange. The control is of course set to Rich text :)

="Part 1 <i><b> Bold and italics </b></i> Part 2"
0
I have an access database that will be used for issue tracking within my organization.  I had added a crude security (Log-in) feature consisting of choosing a Program Office from a combo box and an Access-Key entered into a text box; it was not the most elegant code but was working fine.  Then I made some edits/changes to other features of the database, nothing that should have effected the code I have for logging in but started receiving a Run-Time Error 3709, The search key was not found in any record.  The change I made was to a long text field in one of my tables changing it to track comment history by making it an append only field.  I then went back to earlier draft versions of the database to double check that I had not accidentally changed something in the code only to find all previous versions were experiencing the same error.  

It is really important that I keep a running history of comments added to a record but it is also important that I limit program office access to only those issues that apply to them; I am using the log-in features to set a temp variable used later to filter records.  My code is below but I am not certain the problem is in the code.  

First I test for Null values; then I test for User credentials, this is where I am getting the error; finally I test for admin credentials which work perfectly.  I am a novice at best and have ran out of ideas.  Any assistance you can provide is greatly appreciated.

Private Sub cmdLogin_Click()
Dim …
0
Hi Experts,

Which Windows 10 book would you recommend for someone coming from Win 7?

Prefer something that covers all changes but short and to the point, not much reading necessary...

... if you are experiencing Windows 10 for the first time, after many years of Windows 7. You need a translation-style book, that maps Windows 7 things to the Windows 10 equivalent

Thanks
0
Hi Experts,

This is in reference the the following Q

https://www.experts-exchange.com/questions/29170016/Split-Names-column-into-First-Last-name-columns.html#a43013755

How to perform a split of names column with mostly being First + Last Name while some are Last + First Name (Indicated by Comma).

Thanks
0
User is using EXCEL to clean up data from an absolutely horrible, irregular, unpredictable format.
After getting the data into a proper state, they then save that spreadsheet with a new name.  (they need to give it a new name in case they need to come back to it)

I'd like to build some code on an ACCESS FORM that presents the user with the ability to browse to their newly created EXCEL file, and import it into ACCESS.

I've already got the import routine built, but my VBA skills only have it far enough to import the same filename each time.

Thanks for any help.
0
Please excuse the lack of knowledge and possibly wrong verbiage used in this question. My customer has an old access database from 1997 and its been converted to a 2002-2003 format. Now my customer would like to convert this .mdb file to the .accdb format. I have tried everything on google and nothing is working. Is there something I am missing. Be advised, I have no CLUE about Access 2016 or any other version.
0
Hi

I have the two Access tables shown in the image. I want to update the LicenceNumberFK field using the correct values in the second table that I imported from Excel t_Arms2. I want to do this by looking up the LicenceNumberFK field in t_Arms2 using the ArmPK field

1
0
Hello Experts:
We have a departmental Access database in the .MDB format that's been hovering around 1.6-1.7gb in size. The database is exhibiting unusual behavior in that as of a few days ago, it began frequently crashing and creating an automatic backup. There are 1,200 objects in this database...would it help if we created a new shell and imported all of the objects into the new database?
0
Hi

I have a split database Access solution for which I have code that automatically links the front end to the backend. When I run this code I am getting the following error. I do not have a link for the table mentioned. I never created such a table

Thanks

1
0
Hi

I have a text box called LicenceNumber that is populated programmatically from another form using the following code. What is the best even behind the text box LicenceNumber that will be triggered when text is changed from the other form?

 Forms(oNewLicenseeFor).Controls("LicenceNumber") = Nz(DMax("LicenceNumberPK", "t_Licence"), 0)
0
I have two excel sheets come from two different sources ( each with about 15 fields  - say field 1, Field2.... Field15 ) .
There will be some  differences  in data , and I need to show  the differences ( probably with a different color)

It can be done with MSAccess or Sql Server or Oracle as long as I get the results
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.