Microsoft Access

221K

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

We just set up Remote Desktop Services on this 2012R2 terminal server. The RDS is set up to use "Session-based" desktop deployment.
We published "File Explorer" where a "MS Access" short will take our teleworkers to get into a MS Access database file located on the terminal server. If the users use "mstsc /admin" they are able to open the Access database with full access (only one user access at a time.) But if they use "RD Web Access", they will get the Read-Only accessibility on this Access database file even there is no other user accessing at that time.)

Why does it become Read-Only while using "RD Web Access"? How to resolve? Please help.
0
Hi Experts,

I have a procedure that is supposed to be running all the time.
It checks for existence of some files, if it find them it starts executing one by one.
Have created a form which calls that procedure and keeps informing the status, which file file is it currently processing...
Now I have the following dilemma.
When I stop execution (Contrl+Break) and want to resume, I have no way to get focus back to the main form.
How can I accomplish that?

Thanks
0
in MSACCESS Report"

I need some phrases to be in different Formats.  Example:

This is plain text This is Italics this is plain text again and this is bold

Also I would like to embed a jpg or bmp in the text, for example a signature (that was captured as jpg or bmp) at the bottom of a letter,

Ideas anyone?  Thanks again.
0
ole/dde timeout in access.  I changed it to 0 but my query is still timing out.  How do I change it so the query does not timeout.  It is only running for a couple of minites when it timesout
0
I have a 2016 MS Access - Word application.  The database has the front-end code stored in an Access db on each user's c-drive, and that front-end db links to the actual data which is stored on a fileserver.  This application supports MS Word 2016 mail merges.  These mail merges all have one datasource which is driven by a single temp table that is created on the user's local db copy.  If that single user wants to open more than one merge letter, they receive a run-time 3009 stating that 'you tried to lock table 'blah blah' while opening it, but the table cannot be locked because it is currently in use.  Wait a moment, and then try the operation again."

From what I can tell, the first merge letter is using an exclusive lock on the MS access temp table datasource.  Is it possible that the merge letter cannot use an exclusive lock allowing the single user to open multiple merge letters that are all based on the same temp table?

Thank you in advance!  -Jason
0
Have an access app that has been running for more than a decade. Several times a year we compact & repair it. However, over time it continues to grow (Bloat). It is a split, front end/Backend structure. the frontend is currently 548mb. When I copy it to a new, blank db it reduces to 18mb as long as I limit the copy options to "relationships" and "Nav Pane Groups". It seems to run ok so far, but I'm trying to find out what data I am losing in this process, if anything, because trying to test all of the items in the app would take forever. so far have not been able to find out what is included in copying all options.
Currently running access 2016 on win7/10 pro
0
Hi Experts,
I have an application that was running fine on my pc, and whenever I wanted to stop code execution by pressing Contrl+Break it stopped.
Now I moved the application to the Server and tried a couple of times to stop it and could not get it to stop, had to kill the application.
What can this be?
PS. Use Access special keys = true.
0
Between autoexec macro and the ribbon, wich launch first within MS access, when a database is secured ? (prevent special hotkeys / do not display tables ect ...)
0
Hi,
I am new to MS Access and I need to edit a MS Access 365 report.

I need to add a condition that shows a different email address based on what choice someone makes from the radio buttons.

If the person chooses the last Radio button (AIM Consulting) then they will see a different email address within the report.

Form Radio Buttons
Label Address
0
those are the results of the query:
UserName                              YY              MM                      Status                            Approach              SC#
Peter Pan                             2018      11                      Status 1                           A                       123
Peter Pan                                2018      11                      Status 2                         A                        123
Peter Pan                                2018      11                      Status 3                         A                       123

=======================================================================================================
i need to make that (below)

UserName                              YY              MM                      CountStatus               Approach           Distinct SC# Count
Peter Pan                             2018         11                   3                                   A                          1

this is my final query:

SELECT Data.UserName, Count(Data.[SC#]) AS [SC#Count], Data.YY, Data.MM, Count(Data.Status) AS CountOfStatus, Data.Approach
FROM qryResultsDumpInitial as Data
GROUP BY Data.UserName, Data.YY, Data.MM, Data.Approach;

instead of I am getting:  The number of SC# should 1, not 3

UserName      YY              MM                      CountStatus               Approach           Distinct SC# Count
Peter Pan      2018        11                   3                                   A                          3



how to fix that
0
I have an option box with 3 options in a continuous form.  As I scroll down the page with either the scroll arrow keys or the mouse wheel some will show 2 boxes checked.  If you click on it, it will correct itself but it is very confusing for the user.  It does not happen if I scroll a page at a time.

I built a new database with a new form and new option box and it still has problems.  Any Ideas?
0
I have many queries were simply to come up with a number that is a count of records with a specific criteria, to be used later in a calculation. Because those criteria are so lengthy, and change with each variation of the dataI am  collecting, I’m not sure if an IIF structure would return the results you want. Crosstab queries can get quirky when fields are not available on different data runs. And to be honest, a crosstab query is really just a type of pivot table like in Excel, but not as flexible. I want to do most (if not all) of the count-collecting in VBA using recordsets or the DCOUNT() function. Then, dumping the values into a temporary table and base the report off of that. The queries I  already have built are doing pretty much what I described, but when I try to link them together for your final report, if anything had missing or null values, it causes problems. Doing the work in VBA givesme much more flexibility in handling these situations.
 But I do not know how to write up the function to do that how can u do what is above?
0
I have a Access DB that I working on which has forms and subforms. I have an area where I need to be able to add car makes models and colors. I am able to bring up a dialog to create a new color or Make but Need the Dropdown update with the new set of options after closing the dialog.

Thanks
0
i want to join two queries so i can take the count of one query and divided by another query to get the percentage of work orders that were completed.

the numerator query:

SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((MaximoReport.Status) Like "*COMP") AND ((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) AND ((MaximoReport.ActualLaborHours)<>"00:00") AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) And (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])));

Open in new window



the denominator query

SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start]
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((MaximoReport.Status)<>"CAN") AND ((MaximoReport.[Target Start])>=DateAdd("h",-11.8,[Enter the start date]) And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])));

Open in new window



What i want is combine two queries so i can have one query do it all instead of three.
MaximoReportVSgood.accdb
0
Hi please find below vba script, which helps me to copy range of excel cells and save it as a jpeg file. I want this code to run from ms access vba editor.

Basically

1. Open an excel workbook
2. Go to a given tab
3. Copy range of  cells.
4. Save as a jpec file in a given folder

Please have a look

Sub ExportRange()

   Const FName          As String = "C:\TEMP\export.jpg"

   Dim rng              As Range

   Dim shtTemp          As Worksheet

   Dim chtTemp          As Chart

 

   Application.ScreenUpdating = False

   '// Change range as needed

   Set rng = Worksheets("Sheet1").Range("A1:C10")

 

   '// Add a temp worksheet. Chart will be placed on this. It will be deleted after

   Set shtTemp = Worksheets.Add

   '// Add chart

   Charts.Add

   '// Move the chart to the new sheet and get a reference to it

   ActiveChart.Location Where:=xlLocationAsObject, Name:=shtTemp.Name

   Set chtTemp = ActiveChart

 

   '// Copy and paste the range

   rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture

   chtTemp.Paste

 

   '// Export

   chtTemp.Export Filename:=FName

 

   '// Tidy up...

   Application.DisplayAlerts = False

   shtTemp.Delete

   Application.DisplayAlerts = True

   Application.ScreenUpdating = True

 

End Sub

Open in new window


Thank you
A
0
We are moving from Windows 7 to Windows 10, and from Office 2010 32-bit to Office 2016 64-bit. We have recently had some issues with our testing of Access databases in the new environment. When opening a database, we are getting a 'First Things First' pop-up pop-up which gives us the option to install some updates OR proceed with caution. Now we have had a variety of results when continuing from here:
- click 'Ask Me Later', and things have worked fine
- click 'Ask Me Later', and things have imploded (forms not working initially then could not open the database)
- click 'Install Updates' and things have worked fine

On the database that imploded, as it was opening initially, the forms were coming up with an 'Error' pop-up and the only option we were given was to click Ok. Closing and then trying to re-open the database using Access 2016, we got a syntax error message. This message also came up when we tried to open the database in Access 2010.  We eventually figured out that some references were updated, was able to restore the database and get it working again in Access 2010.

When we clicked 'Install Updates', I was expecting quite a delay in opening the database, but that was not the case. It opened almost immediately, so I'm not sure what updates were installed.

So my questions are:

- What is the proper (correct) way of moving Access DBs from 2010 32-bit to 2016 64-bit? Conversion tool?
- What updates are done when you select 'Install' on the …
0
Hello, I have an Accounting access program I am upgrading to win 10. All the queries run except one that has a lot of Where parameter (5 of them).

I keep receiving the error "System resources exceeded".

I can not change the query due to the business requirements.

Any advice?
0
Hi,

I am looking for Access VBA script which can create link table to an excel spreadsheet out side the database.

If above is possible, I need an Access VBA script which can disconnect the linked table from the database.

Please let me know

Thank you
A
0
I am using a simple web based form to submit information to an Access DB. Here is a sample of the code:

<%
Dim myConnString
Dim myConnection
Dim mySQL

myConnString = Application("MyDBTable_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString
      
mySQL= "INSERT INTO Table "
mySQL= mySQL & "(status,pub_status,...
mySQL= mySQL & Request.Form("pub_status") & "','"
...

myConnection.Execute mySQL
myConnection.Close
Set myConnection = Nothing

If draft = "No" Then
...

The problem I am noticing is that every once in a while, random characters are appearing in the DB. For example the field called draft...I give the user to dropdown box to choose Yes as the box defaults to No. There's no way to specify anything besides Yes or No and as you can see from the code, I have condition statement based on which they choose. Well something happen three straight times to this users information and when I looked at the database the entry that was submitted was "Not". How did Not get into the field when the only two options were Yes or No?

I started looking around and another one of my fields which identifies which city our office was located, in this case Columbia, had entries like Columbial, ColumbiaR, Columbiae and my favorite Columbia&#10642%. This is a drop down as well. Any idea why or how these random character are getting added to the data entered.

BTW, this is a random occurrence, it does happen all the…
0
Hi

I am looking for an Access VBA script which can apply conditional formatting on Excel worksheet columns based on cell values.

Please let me know your thoughts.

Thank you
A
0
I am as clear as mud on which is the best (newest?) driver to use to connect Access to SQL Server. I've been using DRIVER=SQL Server. I recently tried DRIVER=ODBC Driver 13 for SQL Server which seems to work fine. I came across Microsoft OLE DB Driver 18 for SQL Server but I haven't been able to figure out how to use that. I get a Can't find installable ISAM error.

I am looking for advice on the best driver to use. I would like to know if I can use Microsoft OLE DB Driver 18 for SQL Server and how.

Thank you.
0
Is it possible to run an Access 2010 accde created with 32-bit access on a computer where Access 2016 64-bit is installed by using Access 2010 runtime in parallel?  Can I install and call the 32-bit access runtime without messing up the 64-bit access 2016 install?

Thanks in advance!
0
Hi Experts,
When I Preview a report in MS Access 2010 and want to save it as a PDF the File Name defaults to the Report Name.  e.g. Report 1

Is there a way to change the File Name to something Custom?  e.g. Report 1 - 2019-01-30.

Thanks,
Bob C.
0
I am using Access 2013 and an email DLL library called Redemption. They work together very well and allow me to send emails from within my Access program to all my clients without any Outlook Security warning or any intervention whatsoever. It's seamless. We send all types of emails to our clients, but up until this time most of the emails have been only text.

Now, I want to up the game and send HTML emails with embedded pictures and links.  Does anyone have any experience with this using Redemption or another method? I am open to different options.

Ideally, I want to have the email generated in code and be sent to any client with the click of one button. The access software gets the client's email address from their record automatically. I don't mind having to program the email contents each time for a particular email solicitation.

If there was third party template generator I could just copy that would be great, but I haven't found one to work. For example, I am unable to recreate a mail chimp template in code.  It's not worth moving to an external email engine as I want this to be 1 or 2 client emails that are specifically generated for that client on the fly. Like, you get off the phone with a client and you just finished discussing a particular product and you want to immediately send a product spec sheet with pictures and links. The next client would have a different product, etc.

Any thoughts or idea would be greatly appreciated.
0
I am trying to develop an API call to Jet.com to retrieve my "token" so I can begin uploading products.  I have referenced the developer docs below, but I keep getting error messages. Is it necessary to really add the spaces and line feeds, or is this just for readability. Trying to get past step one. Any help would be appreciated. Thank you.

Definition
https://merchant-api.jet.com/api/token
Examples
Token Request
{
  "user": "5AF8F18CC1F635F34015EA871D3482351282CE37",
  "pass": "TRMct3xCvJX3P+SIMH4/CyJH3M9giFPtvRqRr8uNgPJz"
}

Open in new window



The following headers are required when making any call through the merchant API unless otherwise specified. The {id_token} will be returned after completing the "retrieve token" step

Header                                    Value        
Content-Type               application/json
Authorization              bearer {id_token}

---> My code

Body = "{" & """User"": " & """" & JetAPIUserKeyTest & """" & ", ""pass"": " & """" & JetSecretKeyTest & """" & " }"

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    strURL = "https://merchant-api.jet.com/api/token?" & Chr(13) & Chr(10)
    objHTTP.Open "POST", strURL, False
    objHTTP.setRequestHeader "Content-Type", "application/json"
    objHTTP.setRequestHeader "Authorization", "bearer {id_token}"
    objHTTP.send (Body)

Open in new window

0

Microsoft Access

221K

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.