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 need help in deciding path forward for our MS Access database.

Recently my old company was bought out by another and the new company changed the network topography. They removed network drives and went completely cloud base. We are using OneDrive platform. OneDrive does not support MS Access functionality at all. I cannot share our MS Access database with multiple users, because OneDrive replicates back-end for every user that links to it. so I needed to place back-end on every user’s computer. As result, I have several versions of the back-end and we do repeat inspections as result.

I am looking for a way to solve this problem, but also a good path forward with MS Access database, and I need your advice.

I have considered the following things:

1.      Use a virtual server to host our Access database
2.      Install MS Access 2010, change database to web apps and then host it on an older SharePoint server.
3.      Use an SQL server, and migrate MS Access back-end to SQL or AZURE and somehow link it to the front end. I read about this online, but not sure how much work will be involved.
4.      Completely redesign the database to SQL version or other version
5.      Leave as it is and deal with it.

The company is willing to pay for the hosting, but I am also looking at path forward and future of this database. Its quite developed and very useful for many personnel.

I need your expertise advice since you all know ins and outs of other databases.

Thank you.
0
Hi Experts,
I have a Access DB in 2007 version (32bit) and I convert to 2016 access(64bit).  I have a login page, user enter the user name and password, then open a form with few buttons (one of these button is open an entry form name "Add/update Case"), this button was working fine in the old version, however, after I converted to 2016 version, when I click this button to open the form,  once the "add/update case" form opens, it circling for about three to four seconds, it close the whole database then make a backup of it.  I checked all the coding and nothing get change.  I can figure it out why.  any help will be very appreciate.

Thanks
0
Hi

What Access VBA code would I use to create an Excel object, push data to a sheet in it
and then format certain ranges?

Thanks
0
Hi

In Access VBA I want to push data to Excel using a SQL statement then open the Excel file and sort by one of the columns.
What code would I use?

Thanks
0
I am trying to set a variable to the value of Cell F1 on sheet %DepNum% But not figuring it out as of yet.

Cell F1 is a countif in Excel to count the number of fields that aren't blank.

SourceFile:="C:\Users\beatified\Desktop\AHK\Copy of Deposit Form.xlsx"
InputBox, DepNum, Deposit Number, Enter the Deposit Number you would like to process
NumberColumns:=9


Output:=""
ColumnLetters:=["A","B","C","D","E","F","G","H","I"]
oWorkbook:=ComObjGet(SourceFile)
oExcel.Sheets(DepNum).Select
ExcelCells:=Object()
NumberRows:=oWorkbook.Worksheets(DepNum).Range(F1).Value
Loop,%NumberRows%

Open in new window

0
exporting data from subform

I have used this code in 2010 and 2003  to export a subform and even if the subform had filtered records, it ONLY exported the filtered data ?

Now it does not in Office 365 ?

DoCmd.DeleteObject acQuery, "qryTemp"
     Set qdf = CurrentDb.CreateQueryDef("qryTemp", Me.All_ExtendedPaymentTerms_subform.Form.RecordSource)
     DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, "C:\Users\" & Racfid & "\Desktop\All_Extended.xls", True
0
Hi Experts,

In Access, is there a criteria that filters anything that doesn't start with a number?
0
Hi Experts,

I'd like to know if it's possible to take the value of a combobox, search the value in a table and then return values related to that value?

IE:
1. In frmInspection, we select the value "3035" and then we click on the button "Specification".
2. The same value is applied to the cbo in frmSpecification.
3. A box containing the column C1, C2, C3, C4, C5,C6 is populated, (3082, 3052, 3034, 3035, 3150,3157) (Vlalues from the same row as "3035")

If 3149 was in the combobox, we would have: 3069, 3149, 3114, 3159, 3061, 3031 instead.

Is there  a way to do this?
Equipment.xlsx
Planning.accdb
0
OFFICE 365 ACCESS
form
subform
comboxes  =  4
tabcontrol

i have 4 comboxes on a form they independent of each other(not cascading).

I have a tab(tabs1,2,3,4)  
For Tab1

What I need:
I need when a selection is made in any given combobox the subform will filter.
if 1 of the comboxes is selected then filter.
if 2 of the comboxes are selected, then filter the subform with those 2 values
....etc  

I have told them to simply use the filter by selection built into access but they want it this way.


suggestions or sample code ?  

Thanks
Fordraiders
0
Hi Experts,

I'd like to know if there is a way to populate a combo box in form2 with a combobox from form1?

IE: I have a cbo in frmInspection, if I made a selection and click on the button "Specification", it
goes to the frm frmSpecifcation and it populates the same value to the cbo in the form.
Planning.accdb
0
I have a form that when on open the following VBA code executes:

    If DLookup("[SecurityLevel]", "LOCALtblCurrentUser") > 1 Then
        Me.AllowAdditions = False
        Me.AllowDeletions = False
        Me.AllowEdits = False
    Else
        Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowEdits = True
    End If

Open in new window


However, if the users security level is >1 I DO want them to be able enter values in three checkboxes.  How can I allow that to happen?
0
Hi,

We're having an issue at the moment with an access database repeatedly going corrupt. We've been restoring it from shadow copies for now but this obviously isn't a feasible long term fix.

The database itself is on a file server (running Server 2008 R2), and is accessed one of two ways: via Access 2019 on some users desktop machines, and the rest of the users via Access 2010 on a virtual terminal server running 2008 R2.

The issues first arose after office was updated (including access) to 2019 for the desktop users, so I'm pretty sure it's the database being accessed by two different versions that's causing issues.

Does anyone know if there is a way around this? We'd have liked to upgrade the server to Access 2019, but it seems that it needs to be running server 2019 to do so, and we'd ideally like to avoid having to upgrade the Server OS.

Thanks
0
Hi Experts!

I have an error message while trying to open an Ms access 2016 on one of the machines see the error message below , I do not have a full version but just runtime 2016 on the target machine , but other machines all is okay:

the database you are trying to open requires a newer version of Microsoft access



Office-Error.pngOffice-Error.png
0
I'm trying to remove the 1st 3 characters of a text field if the 3rd character is a dash (-) and the 1st 2 characters are numbers in an Access query or VBA. See attached sample result.


Image
0
Hi Experts,

I have a table with data in which I'd like to have a "Clickable" list of Equipements.
When an equipment is selected, it'll populate all the information related to that specific equipment to a form.
IE:
I'd like
E1
E2
E3
...
E16
to be in a selectable rectangle and when an equipment is clicked, it selects the form Inspections and it fills all data related to it.
Is there a way to do it without drop box/list ?
Planning.accdb
0
How can I group the following by month and day. I can make it work by months but this groups month in all the years in one record.

SELECT SWD_SCADA_Daily.[Well Name], Sum(SWD_SCADA_Daily.[Water Injection (bbl/day)]) AS CumInjection, Avg(SWD_SCADA_Daily.[Injection Pressure (Psi)]) AS AverageDischargePressure, Avg(SWD_SCADA_Daily.[WHP (Psi)]) AS AverageWHP, MONTH(SWD_SCADA_Daily.[Time/Date]) INTO SWD_SCADA_MONTHLY
FROM SWD_SCADA_Daily
GROUP BY SWD_SCADA_Daily.[Well Name], MONTH(SWD_SCADA_Daily.[Time/Date]);

Thank you.
0
Folks,
I have attached a Word 2016 document that outlines my new database that takes Implementation through Step One. With so much introductory information needed I felt it would be difficult to describe my problem and objective here.
Thanks to all that participates. I wish I could distribute points like we us to do but it appears that is no longer an option.
Logic-for-Excel-Solutions.docx
0
I am trying to read a series of records in a Access DB table and export them to a csv file with "|" delimiters.  I search some sample scripts and it's mostly working but the issue I'm finding is that every time I execute the vba code, it reads and prints to the csv file up until the last two records.  The second to the last record is partially recorded to the csv file and then the subroutine pauses.  If I run the  sub routine again, I get a Run time error 55 (file is open)  but it also finishing up the writing of all the records.

Here is the coding I am currently using:

Note; the 1st part of the routine is to pint out the record headers.  The 2nd part prints out the record content.

Public Sub Print_2_CSV()


Dim MyDB As DAO.Database
 Dim rst As DAO.Recordset
 Dim intFldCtr As Integer
 
 Set MyDB = CurrentDb
 Set rst = MyDB.OpenRecordset("Case Information File", dbOpenSnapshot)
 
 Open "C:\Test\Case Information.txt" For Output As #1
 
 rst.MoveFirst
 
 With rst
   'Write Field names to Output File, delimiting by '|'
   For intFldCtr = 0 To .Fields.Count - 1
     strBuild = strBuild & .Fields(intFldCtr).Name & " | "
   Next
     Print #1, Left$(strBuild, Len(strBuild) - 3)    'Field Names, remove ending ' | '
       strBuild = ""     'Must RESET
 
   Do While Not .EOF     'Values in Fields delimited by '|'
     For intFldCtr = 0 To .Fields.Count - 1
       strBuild = strBuild & .Fields(intFldCtr).Value & " | "
     Next
       Print #1, Left$(strBuild, 

Open in new window

0
Hi

I am using the following code behind a button click to open a form whose code is shown further on. This loads a listbox on the form.
When the user makes a selection from the listbox it sends this back to my original ComboBox.
The listbox has one column. I now want to do this for a multi column combobox and listbox. How do I do that?
Thanks

Private Sub btnManufacturer_Click()

    oForm = Me.Name
    oCombo = Me.Manufacturer_Combo.Name
    DoCmd.OpenForm "f_List", , , , , acDialog

End Sub

Open in new window


Private Sub Form_Open(Cancel As Integer)


  Cancel = -1
  CopyComboBoxSettings Forms(oForm).Form.Controls(oCombo)

  Cancel = 0

End Sub


Private Sub CopyComboBoxSettings(ByVal CSourceComboBox As Access.ComboBox)

  List1.RowSourceType = CSourceComboBox.RowSourceType
  List1.RowSource = CSourceComboBox.RowSource
  List1.ColumnCount = CSourceComboBox.ColumnCount
  List1.ColumnWidths = CSourceComboBox.ColumnWidths

End Sub


Private Sub List1_Click()
    Forms(oForm).Form.Controls(oCombo).value = List1.value
    DoCmd.Close
End Sub

Open in new window

0
Hi
I have a client that say that they receive data from multiple sources. I want to advise them on why they should use Access as their analytical engine
What key points other than ease of use can I mention?
Thanks
0
How do I implement full join in access. I want to see data from both table joined by two fields.

SELECT SWD_NDIC.*, SWD_SCADA_Daily.*, SWD_NDIC.[Well Name]
FROM SWD_NDIC full JOIN SWD_SCADA_Daily ON (SWD_NDIC.[Well Name] = SWD_SCADA_Daily.[Well Name]) AND (SWD_NDIC.Date = SWD_SCADA_Daily.[Time/Date])
WHERE (((SWD_SCADA_Daily.[Well Name])="Test") AND ((SWD_NDIC.[Well Name])="Test"));
0
Hi Experts,

I have a list of required fields, and trying to construct a SQL statement out of it as follows.

ListOfFields.
[input#InsertRecordVisit_Date.cbFormTextField, input#InsertRecordClient_Last_Name.cbFormTextField, input#InsertRecordClient_First_Name.cbFormTextField, input#InsertRecordDate_Of_Birth.cbFormTextField, select#InsertRecordShift_From_Hour.cbFormSelect, select#InsertRecordShift_From_Minute.cbFormSelect, select#InsertRecordShift_From_AMPM.cbFormSelect, select#InsertRecordShift_To_Hour.cbFormSelect, select#InsertRecordShift_To_Minute.cbFormSelect, select#InsertRecordShift_To_AMPM.cbFormSelect, textarea#InsertRecordPurpose_Of_Visit_Goal.cbFormTextArea, input#InsertRecordTemp.cbFormTextField, input#InsertRecordPulse.cbFormTextField, input#InsertRecordRR.cbFormTextField, input#InsertRecordBP.cbFormTextField, input#InsertRecordO2_Saturation.cbFormTextField, input#InsertRecordO2_In_Use0, input#InsertRecordO2_In_Use1, input#InsertRecordRespiratory_Lung_Sounds.cbFormTextField, input#InsertRecordRespiratory_Breathing_Pattern.cbFormTextField, input#InsertRecordBipap0, input#InsertRecordBipap1, input#InsertRecordCpap0, input#InsertRecordCpap1, input#InsertRecordTrach0, input#InsertRecordTrach1, input#InsertRecordVent0, input#InsertRecordVent1, input#InsertRecordCardiovascular_Skin_Color.cbFormTextField, input#InsertRecordSkin_Temp.cbFormTextField, select#InsertRecordCardiovascular_Edema.cbFormSelect, input#InsertRecordNeuro_Alert0, 

Open in new window

0
Hi

Is it possible to email data from a small Access table through an email address that has not been set up
in Outlook, So directly through the net.

Thanks
0
I'm using the following code and receiving Access error code:

Error

SELECT
CASE 
WHEN [tblConsolidated].[Appeal Category L3] IN
('180 MEDICAL INC',
'180 MEDICAL INC',
'180 Medical Inc.',
'180 Medical, Inc',
'180 MEDICAL, INC.')
THEN '180 MEDICAL INC'
Else [tblConsolidated].[Appeal Category L3];

Open in new window

0
I have a single form layout subform that moves to any record by the record navigators. They are listed by the data and time they were created (Oldest First). One of my clients wants to be able to re-order entries. (typically when some record was missed out or something needs adding in between existing records). Because the current order is date and time I could simply add a pop up form and demand an old date and time that fitted between entries in the existing list. I just wandered if there was a more user friendly approach. Something like a pop up form with a list of the records with a drag and drop feature.

Strictly speaking there is no specific reason to have a date time created field in any record. it was originally the best way I knew to ensure that records were presented in the order that they were created

Am I asking too much for drag and drop?
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.