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

Hello,

I have a team of four who need to share a small amount of data via OneDrive. This is the only sharing platform our company gives us to work with and we don't have admin rights. We want to use Excel for the front end of the database. Currently we use Excel and VBA to upload data to a master workbook that is shared by everyone. The problem we are having is this workbook is often locked (read only) by another user when someone tries to append or edit a record (apparently this is a known issue with OneDrive but it’s our only option). My thought now is to maybe use Access as a backend database on a OneDrive folder that does not sync with anyone’s computer. The only way to do this is to connect to the Access file using the Https address (since there will be no local copy on our computers and no way to map to a OneDrive folder without using the OneDrive syncing app). This works for Excel using this code:

Set Wb1 = Workbooks.Open("https://*****/Pipeline_Report.xlsx")

I cannot get it to work connecting to an access file using the URL:

Path = "https://*****/Pipeline_Report.accdb"
MyConn = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " & Path
cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.Open MyConn

Is there a way to do this?... or is there a better option?

We are using Office 2016
OneDrive for business
Some have Windows 10 and some have Windows 7
Four users share one table
The table has about 500 records and each user makes a few dozen changes per…
0
Hi Experts,

I'm looking to modify the below to be an update instead of a select.
however not sure where do I put in the body param as specified below
https://howto.caspio.com/web-services-api/rest-api/older-rest-api-versions/table-operations/

Function GetDataFromCASPIO1()
    Dim objHTTP As New WinHttp.WinHttpRequest
    Dim docXML As MSXML2.DOMDocument
    Dim ResponseText As String
    Dim curNode As IXMLDOMNode
    Dim oNodeList As IXMLDOMSelection
    Dim s As String

    Set docXML = New MSXML2.DOMDocument

    Set objHTTP = New WinHttp.WinHttpRequest
    URL = "https://MyAccount.caspio.com/oauth/token"
    
    objHTTP.Open "POST", URL, False
    objHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"

    objHTTP.Send 
    ResponseText = Right(objHTTP.ResponseText, Len(objHTTP.ResponseText) - 17)
    access_token = Left(ResponseText, InStr(ResponseText, """") - 1)
    'Debug.Print access_token


    Set objHTTP = New WinHttp.WinHttpRequest

    URL = "https://MyAccount.caspio.com/rest/v1/tables/Skilled_Nursing_Visit_Note/rows?q={""limit"":10000,""where"":""visit_date>=GetDate()-7""}"

    '''objHTTP.Open "GET", URL, False
    objHTTP.Open "Put", URL, False
    objHTTP.SetRequestHeader "Accept", "application/xml"
    objHTTP.SetRequestHeader "Content-Type", "application/json"
    objHTTP.SetRequestHeader "Authorization", "Bearer " + access_token


    objHTTP.Send
    'Debug.Print objHTTP.Status
    'Debug.Print objHTTP.ResponseText
  

Open in new window

0
See attached file. I cannot figure out why all the filters on Form "CustomerSalesHistory_Detail" do not  work correctly. Once I select the date range, I should be able to
select any one of the filters to get results. For some reason once I select the date range, it defaults to a writer "Jaynleep" with a date range of May 1, 2018 and May 2, 2018.
which I did not select.
Any idea what happened?

Thanks in advance for any help!
Rick
CustomerSlsHistory.accdb
0
Hi Experts,

I have the following lines of code retrieving all records from a particular table.
Would like to build a SQL string that will insert them into a local Access table.

Can someone help me accomplish that?

PS. there are different datatypes, so perhaps its also possible to inspect the Access datatype for that field before adding it to the SQL string..

    strXPathQuery = "Caspio/Row"
    Set oNodeList = docXML.selectNodes(strXPathQuery)
    For n = 0 To (oNodeList.length - 1)
        Set curNode = oNodeList.Item(n)

        For m = 0 To (oNodeList.Item(n).childNodes.length - 1)
            Debug.Print oNodeList.Item(n).childNodes(m).nodeName + " => " + oNodeList.Item(n).childNodes(m).Text

        Next
    Next

Open in new window


Thanks in advance.
0
I have a DB in access, a table (table_auth) that has a user and an authorized role for an app, a table (table_audit) where the roles are found for each user so they are not authorized.

My auth table
APP MODULE      ROLE      USER
----       ------              ------              ------
APP1      VIEW      READ      AYEBRA              
APP1      VIEW      WRITE      DAVID                
APP1      PRINT      ADMIN      PEPI                
APP1      VIEW      READ      ANGELS              
APP1      VIEW      READ      ASOLER              
APP1      VIEW      READ      ALEX
APP1      PRINT      READ      ETHEL

My Audit table
APP      MODULE      ROLE      USER
-----      ----      ------      ------
APP1      VIEW      WRITE      ALEX
APP1      VIEW      ADMIN      NOAH
APP1      PRINT      MODIFY      ETHEL

I want to make a query where I can add another field and be able to identify if the unauthorized role is in the auth table or leave it empty if it does not exist in the table, something like this
APP      MODULE      ROLE      USER      REAL_ROLE
----       -----                      ------               ------      -------
APP1      VIEW      WRITE      ALEX      READ
APP1      PRINT      MODIFY      ETHEL      
APP1      VIEW      ADMIN      NOAH      ADMIN 

How could I do it?
0
I am working with a tabbed form that has fields that are part of the main forms recordsource but also has two datasheet sub-forms that have their own recordsource.  When I click on one of the tabs the first field in each of the sub-forms get the focus.  How can I stop this from happening?  I'm afraid someone will type over a previous entry if the fields get the focus.
0
Access 2010  vba routine needed

I have a linked excel worksheet

What I need:
I need to export "Sheet1" with VBA code
PIPE DELIMITED only  |

Starting with ColumnA to ColumnH
Row2(Row1 has column headers)
number of rows in sheet will vary.
Export the file to: Putting the file in "C:\Program Files\MyData"

Thanks
fordraiders
0
I have a main form with 2 datasheet sub-forms.  On the 1st sub-form, if the user leaves the first field in a new record empty, when they tab off of it I want the cursor to go to the 2nd sub-forms 1st field.


I have tried these and they don't work:


Private Sub cbo1stForm1stCombobox_LostFocus()

    If IsNull(Me.cbo1stForm1stCombobox) Then
        
'        Forms!frmMainForm!frm2ndSubForm.Form!cbo1stField.SetFocus
   
'        Me.Parent.Form!frm1stSubForm.Form.cbo1stField.SetFocus

'        Me.Parent!frm2ndSubForm.Form.cbo1stField.SetFocus
        
'        Forms!frm2ndSubForm.Form.cbo1stField.SetFocus
        
    End If

End Sub

Open in new window

0
Hi Experts,
I want the following to be in my control.tag property and upon clicking a button it should assign it to control source.
=dlookup("CountOfID","Progress_Note_GroupBy_VisitDate_Qry","VisitDate = #[visit_date]# and [Nurse_User_ID_num_pn] = '[Nurse_User_ID_num_snv]' And ShiftFromHour = '[ShiftFromHour]'")

Open in new window

Currently receiving an error, what is the right way to do it?
0
I have a client who recently made the jump from Office 2003 to Office 2016 (previous support was in house and neglected).  They are now having an issue with Access 2016/SQL.

They currently have a specialized accounting package that uses SQL on an in-house server.  They have two Access reports that they use that connect to the SQL database via OBDC.  Those Access files were created by the accounting support team.  Every user uses the same user name with no password to run the Access reports.

The issue is that since the update to Access 2016, the Access report/database requires the user to login in twice before it opens.  As soon as they enter the username (and no password) the dialog box pops right back up and they do it again, then it goes in.  Things appear to work fine, but then when they go to print a report they run, the username dialogue box pops back up.  Again, after a few times it will then print.

I am not a SQL/Access person, so it is beyond me.  I tried turning off most everything in Access (Macro Security, Trusted Areas, Trusted Files).

When I use a system that is not updated to 2016 Access, it works fine.

I have the same issue on:
Windows 7 Pro
Windows 10 Pro
Terminal Server 2016

All were working on Access 2003.

I have tried 64 bit ODBC, 32 ODBC, and both as connectors..  Also have tried checking the "use trusted connection" tick box when the login pops up.

I can provide more information if needed, just out of my scope and not sure what …
0
Is it possible to have a combobox dynamically display possible selections based on what the user is typing?  In other words if the record source for the c-box is:

Envelope
Finishing
Folder
Form
Mail

And the user types an "f", the c-box will display


Finishing
Folder
Form

And if he/she continues and types a "o" the c-box will display

Folder
Form

????
0
Hello - the Debug.print statements below get what you'd expect, but the "Set fldr..." statement errors out with a "type mismatch" error.
This code used to work!
What's wrong?
Thanks


Public Function LoopFilesInFolder() As Integer

    Dim fso As New FileSystemObject, fldr As Folder, fyle As File
   
    Debug.Print fso.GetFolder("C:\RPA\").Files.Count
    Debug.Print fso.GetFolder("C:\RPA\")
   
    Set fldr = fso.GetFolder("C:\RPA\")
   
End Function
0
Hi Experts,
We have setup a server farm technology where file is on the local server and users are accessing the file thru the farm.
https://www.techopedia.com/definition/4202/server-farm
Now we are experiencing FE file corruption very frequent, not sure if this started immediately after the farm setup but this is now whats happening, and lately it can happens a couple of times a day.
See attached error that users are getting.
As soon I connect to pc and open the file, it asks if want repair, and I say yes, and then everything is fine.
I told users they must close access prior to logging off the farm, and they say this is what they do.
Last night I had only one user on, fixed it and in the morning, still only that user was on and problem happen again..
What can be done?
Thanks in advance.
Untitled.png
0
I am pulling my hair out as cannot figure out how to fix this I am summing 12 text boxes which make up a financial year on an MS -Access Report.

I get a #Type Error on 2 of the textboxes and the Totals TextBox, If I add them up Jan to Dec the errors occur in the Jan and Feb Text Box and the Total, if I sum from Dec to Jan the error occurs in the Nov and Dec Text Boxes and the Total look like this:

=[R_OthSpnd_1_Dec]+[R_OthSpnd_1_Nov]+[R_OthSpnd_1_Oct]+[R_OthSpnd_1_Sep]+[R_OthS
pnd_1_Aug]+[R_OthSpnd_1_Jul]+[R_OthSpnd_1_Jun]+[R_OthSpnd_1_May]+[R_OthSpnd_1_Apr
]+[R_OthSpnd_1_Mar]+[R_OthSpnd_1_Feb]+[R_OthSpnd_1_Jan]

I have attached 2 pictures of what this looks like this
 Jan to Dec OrderingDec to Jan Ordering
which leads me to think it has something to do with the order that the TextBoxes process the function, I checked the function and the textboxes do seem to process from the highest months where there is data back to January?

If I wrap the functions in the textBoxes in an Nz function and or the Totals it seems to make no difference?

Any idea how I can get them to Total correctly?


VBA Function looks like this:

Function GetValue(ByRef Lookup_Value As String, ByRef Company_Name As String, ByRef Mth_Value As String, ByRef Year_Value As String)

	On Error GoTo Err_GetValue

	Dim mydb As DAO.Database
	Dim rs_1 As DAO.Recordset
	Dim rs_2 As DAO.Recordset
	Dim rs_3 As DAO.Recordset
	Dim Get_SQL_1 As String
	Dim Get_SQL_2 As String
	Dim 

Open in new window

0
I have a query where I am trying to get one line for each sales agent for a particular month, with total sales for that agent for that month. I can't get the syntax. I have tried everthing I know of and it's probably very simple. the date field is called paid date. Can anyone help me?
I only have three columns in my query, 1. Agent, 2. Sum of Price and 3.  Paid date. The Agent is group by,  the Sum of Price field  is summed and the Paid Date  field is group by
0
how can I can filter external data when I connect to ODBC data source
0
Hi Experts Exchange

I came across your forum chat:
https://www.experts-exchange.com/questions/26579905/scroll-multiple-forms-in-sync.html

It seems that this may solve the solution by creating a second frame for this issue:
Refer to the discussion with the progress bar ITEM being contained in the detail area
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_windows8-mso_2010/crosstab-query-to-display-all-records-according-to/b98b9372-1a83-402d-b54d-87ca5b7a7407?messageId=c8966d76-2bcd-4a49-a1cc-83cdcf71a483

I have also placed this on uservoice
https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/34459063-items-in-detail-section-with-crosstab-queries-and

You mention that there is a site that has a sample in German
Are you able to put in a few screen captures of the solution that I may replicate it.
At this stage I prefer not to download, and simply follow some steps to which I can reproduce the solution.

Thank you kindly for your help.

Simon
0
I have a Report the Prints an Access Form that has filters. I can get the access form to Print the Repor, but the Query is not returning the results correctly.
Below is the code I am using. What I need is to Select the fileds Where the Date Range on the Form is Bew=tween the Start Date and End Date
and any one of the filters have been selected on the form. What am I missing?

SELECT EmailHistoryFINAL.Field6, EmailHistoryFINAL.Field17, EmailHistoryFINAL.Field2, EmailHistoryFINAL.Field3, EmailHistoryFINAL.Field10, EmailHistoryFINAL.Field9, EmailHistoryFINAL.Field11, EmailHistoryFINAL.Field14, EmailHistoryFINAL.Field16
FROM EmailHistoryFINAL
WHERE (((EmailHistoryFINAL.Field6) Between [Forms]![CustomerSalesHistory_Detail]![StartDate] And [Forms]![CustomerSalesHistory_Detail]![EndDate]) AND ((EmailHistoryFINAL.Field17)=[Forms]![CustomerSalesHistory_Detail]![CmbWriterr])) OR (((EmailHistoryFINAL.Field3)=[Forms]![CustomerSalesHistory_Detail]![CmbCustomerSearch])) OR (((EmailHistoryFINAL.Field10)=[Forms]![CustomerSalesHistory_Detail]![CmbPriceLines2])) OR (((EmailHistoryFINAL.Field9)=[Forms]![CustomerSalesHistory_Detail]![TestPart])) OR (((EmailHistoryFINAL.Field11)=[Forms]![CustomerSalesHistory_Detail]![MfgPart]));
0
I usually code in VB.net.  I am working on an existing application that is in Access 2016.  I need to add a report to it.  I am struggling with the correct syntax to reference a field from the table that is binded to the report.

The Reports name is Workflow_prt and its binded Record Source is an Access table named rpt_wrk_flw_prt_out.  I am trying to get the value of fields in that table named "rec_tm" and "status".  I want to set the text boxes in the Page Header to formatted version of the field values (rec_tm) or a literal value based on the those values (status).

Here is the code and some of the things I have tried:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

' Record time
'txtRecTm = CStr(Format(rpt_wrk_flw_prt_out.Fields("rec_tm"), "hh:mm tt"))
txtRecTm = CStr(Format(Me!rec_tm, "hh:mm tt"))

' Status
If rpt_wrk_flw_prt_out.Fields("status") = "1" Then
   txtStatus = "OPEN"
ElseIf rpt_wrk_flw_prt_out.Fields("status") = "2" Then
   txtStatus = "CLOSED"
ElseIf rpt_wrk_flw_prt_out.Fields("status") = "3" Then
   txtStatus = "CANCELED"
Else
   txtStatus = " "
End If

End Sub

Open in new window


Thanks
0
Hi Experts,

I have 4 integer fields as follows
Shift_From_Hour
Shift_From_Minute
Shift_To_Hour
Shift_To_Minute

and use the following to calculate the time difference between both shifts.
Duration: Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))

Open in new window

However users claim its not always accurate, if Shift_From_Hour is after 8PM (20), it will yield wrong results..
What is the right way to calculate this?
0
I have a command button on a switchboard that opens a form for input.  The button worked fine for months.  Now if you click the button you get a message that Access has stopped responding and it will close the database.  All the other buttons work fine.  I had to re-create the button and now it works fine.  Any idea what causes this?
0
Hi

I ma using the following Access VBA code to print out a delivery not with a certain ID. I then want to save the same report as a PDF.
The PDF saves the report with all the IDs and not just the ID that was used to print it.
I have marked the problematic line with a sentence in capital letters
The report shown below should only have one line and not all

Private Sub btnPrintDeliveryNote_Click()

On Error GoTo EH

    Dim oInvoiceNo As String
    oInvoiceNo = Me.lblNewInvoiceNumber.Caption


    Dim oDocsPath As String
    'Set MyDocsPath to Current User's My Documents Folder:
    Dim WshShell As Object
    Set WshShell = CreateObject("Wscript.Shell")
    oDocsPath = WshShell.SpecialFolders("MyDocuments")  'Don't use Set
    Set WshShell = Nothing

   
   If IsNumeric(Me.lblInvoiceID.Caption) = True Then
   
       'DoCmd.OpenReport "Invoice Delivery Note", acViewNormal, , "[Invoices].[ID]=" & Me.lblInvoiceID.Caption 'Prints Delivery Note
       If Me.chkPrint.Value = True Then
           DoCmd.OpenReport "Invoice Delivery Note", acViewNormal, , "[Invoices].[ID]=" & Me.lblInvoiceID.Caption
       End If
      'Save active report to folder
      'FOLLOWING LINE SAVES A PDF WITH ALL THE INVOICES AND NOT JUST THE ID THAT THE REPORT PRINTED ABOVE WITH
      DoCmd.OutputTo acOutputReport, "Invoice Delivery Note", acFormatPDF, oDocsPath & "\Printed Delivery Notes\Delivery Note on " & oInvoiceNo & ".pdf"


      DoCmd.Close acReport, "Invoice Delivery Note", acSaveYes

Open in new window

0
I run several automated processes daily that involve back-and-forth between a data source and Access tables, with excel workbooks in the middle to manipulate the data. The processes run on VBScript executed from a batch file. My problem is that every couple of runs, my middle-man Excel workbooks get flagged as having "Caused a serious error last time" (which I imagine is the system reacting to the rapid and frequent opening and closing of the WBs) when it is opened via another workbook's VBA. Because the file is thus disabled, it should be in the disabled items list, but it isn't - ever. I need these files to stop getting flagged as having caused errors so that the processes can occur without human intervention. Is there a way to permanently trust these files as reliable or otherwise get around this?
0
MS Access 2016:  I've created a combo box to select a job title then button that runs the report for all with the same job title to open in the query print preview.  But this print preview will not scroll with the mouse wheel.  How can I add this functionality?  I'd like to remove the page navigation buttons at the bottom left, but there would be no other way to move to previous or subsequent pages of information.
0
I'm using Access 2010 attachments. My main form has a continuous form subform, showing the attachments. In the subform, there's a button that when double-clicked, opens the Attachment Manager window. I can add and remove attachments OK. To update the subform to show the updated attachments, I press F5, or go to the next record and back again on the main form. It seems to me that I should be able to just do a requery to update the subform. I haven't been able to figure out what event to use to requery what object, to achieve the update.

Thanks
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.