[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

1
Microsoft Access 2016
I want to define a parameter query to ask for a selection but return all records if left blank.
I am using Like [Enter Location] .... this works fine. but there are times I want to run the report for all locations.
I tried like [Enter Location] or Like * but this did not work.

Any suggestions would be greatly appreciated.
thanks
Wayne
0
Access:
Need my query to ask for input. I would like for this to pop open a box where I can input a variable for tbl_Tasks.New_Crafts.

TRANSFORM Count(tbl_Tasks.New_Crafts) AS CountOfNew_Crafts
SELECT tbl_Tasks.TASKSDescriptionCorrection, tbl_Tasks.TaskCombo, Count(tbl_Tasks.TaskCombo) AS CountOfTaskCombo
FROM tbl_Tasks
WHERE (((tbl_Tasks.New_Crafts)="e"))
GROUP BY tbl_Tasks.TASKSDescriptionCorrection, tbl_Tasks.TaskCombo
PIVOT tbl_Tasks.New_Crafts;
0
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
I seem to have gotten what I wanted from earlier question, I have created below a table where I want to store two strings:
Tbllinkstring, see attached table

Now I want to use DLookup to be pick the string from the table, how do I use the IF and THEN and ELSE from the code below so that it evaluate and use the correct string, do not worry about the rest, I will take care of the rest, see the shaded area in the attached table:

Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function
For me this will be the best solution because before deploying the application I will be required only to change one of the links above in the table called Tbllinkstring to the correct host name, this means that I will still maintain my existing laptop links which allow me to compile and make an ACCDE copy without an error. Once the application is deployed to a new host name again it will simply drop my existing links after evaluating and use the new one. The advantage is that the users will not be required to select the correct link at all, the system will handle that for them.
On the shaded areas I want to …
0
Trying to convert Access syntax to SQL (Update Query)

UPDATE dbo.tbl_DriveSalesSidhilMatrix
Set tbl_DriveSalesSidhilMatrix.EnteredBy = case when OperatorName is null then Operator else OperatorName
From dbo.view_Z_SalesOrderAmmendmentsSums INNER JOIN
                         dbo.tbl_DriveSalesSidhilMatrix ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_DriveSalesSidhilMatrix.EnteredBy LEFT OUTER JOIN
                         dbo.tbl_Syspro_Operators ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_Syspro_Operators.OperatorCode
WHERE (dbo.tbl_DriveSalesSidhilMatrix.RecordSource = N'Sidhil')

I'm getting "Incorrect syntax near the keyword 'From'."

Can anyone help?
0
Hello Experts!

I have create a macro called AutoExec to run the function below:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function

Well the function links the tables in SQl Server at runtime and if the ODBC 17 path is correct a message with Re link completed Successfully pop up. All is well as long as I continue using my laptop the problem comes up if I install the same database on a different server client stand alone network not connect with my laptop because the new path is supposed to be:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=PETER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Now how best I can be changing the path below to be reference the correct server name? For example if the same name changes to CHRIS is there a way to alter the path below:

constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Example where there is USER change to CHRIS,the challenge here is how to change the code manually because we always send an accde format to clients. Some are suggesting to  put this string below in …
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
Experts,

I routinely build Access Databases as desktop processes to analyze data. I'm looking to understand the best way to handle the following situation:
Every query I run joins tables on Cost Center. My Cost Centers are 10 Char Alphanumeric. I've already learned from previous questions that I should create a Cost Center master table and use the Autonumber (or other short numeric) field as a CostCenterID for use in joins because its more efficient.
So now let’s say I have a
1.      Cost Center Table with 2000 cost centers and a CostCenterID (Autonumber) field that I’ll use as my primary key, and a
2.      Transaction Table with 1mil records that I want to join on the Cost Center table, but the CostCenterID does not yet exist in the Transaction Table.

How is performance effected if I:
1. Use an Update Query to add the CostCenterID from the Master Cost Center table as a foreign key to any Transaction Tables, so Cost Center and Transaction tables can be joined directly, or
Use Interim Query/Tables
2. Use a Select query to create an interim relationship.
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber)
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window

3.      Create an interim table with a make table query
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber) INTO tbl_Bridge
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window


One opinion I was given is that option 1 is best. I just want to make sure as I will be basing many processes going forward off of this method

Thanks!!
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
Is it possible to run some VB.NET code from within MSAccess!   MSaccess is my main frontend and I have
a VB.net module I'd need to run!  This is a service to access a webservice and I suppose I could write equivalent code in VBA
but was hoping to start adding VB.net code gradually to later convert everything to VB.net!  I've never use VB.net
but want to start learning it  piece by piece!

This is a snippet of code to give you an idea!  I ask because there are visual studio .NET libraries available so I assume I can!

 
Dim manualWebClient As New System.Net.WebClient
            manualWebClient.Headers.Add("Content-Type", "application/soap+xml;  charset=utf-8")
            Dim bytArguments As Byte() = System.Text.Encoding.ASCII.GetBytes(
                "<soap12:Envelope xmlns:xsi='http: //www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap12='http://www.w3.org/2003/05/soap-envelope'>

Just looking for a few good suggestions to point me in the right direction!

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

sqlError.JPG
0
I have this SQL code that is the record source for a datasheet form:

SELECT tblEstimateProofs.Quantity, tblProofTypesLU.Description
FROM tblEstimateProofs LEFT JOIN tblProofTypesLU ON tblEstimateProofs.ProofType = tblProofTypesLU.ProofTypeID
WHERE (((tblEstimateProofs.EstimateID)=[Forms]![frmEstimates]![txtEstimateID]));

Open in new window


If I use the form on a report is ends up looking like this:

Datasheet Example
But on the report I want to display:

3 Baseball + 4 Football + 1 Soccer Ball + 2 Ping Pong Ball + 5 Some Other Kind of Ball

(Note the + marks between each item)

How can I do this?
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
I've been using Rick Fisher's Find & Replace for MS Access for many years. Another developer is trying to purchase the software, but is getting no response from  Rick Fisher. Does anyone know if he's still selling it? http://www.rickworld.com/index.html
0
Hi

I am trying to sum up columns and get the result using below formulae in access query. values need to sum up are in text format ("0.00")

Total: Nz(CDbl([Spent]),0)+Nz(CDbl([To Spend]),0)

I am getting #Error

Please have a look

Thank you
A
0
Hi
I have Oracle SQL Dev version v17.4 with Java Platform 1.8.

I would like to connect to an Ms Access table but i can't see the tab. How can I add it?
Oracle Missing Ms Access tab
In an older Oracle SQL Dev, (1.5), i do have that tab.
0
How to balance forwarded in access report closing balance of previous day to be an opening balance for the next day.
Here is my format
1st Day = Initial Balance  - Fill + Credit = Closing Balance
2nd Day = Closing Balance will be the Opening Balance for the next day.

Tnx
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
Is there any way to pass query parameter through vba in ms Access. Any attached example will be highly appreciable.
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

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.