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

In trying to create an Access column in a linked table I get the error "Operation is not supported in linked tables"
Is there a way to do this where the tables are linked

Sub oCheck_Add_Columns()

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim blnFieldExists As Boolean
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("Ammunition")
    For Each fld In tdf.Fields
        If fld.Name = "CreatedDTG" Then
            blnFieldExists = True
            Exit For
        End If
    Next
   
    If Not blnFieldExists Then
        Set fld = tdf.CreateField("CreatedDTG", dbDate)
        fld.DefaultValue = "=Now()"
        tdf.Fields.Append fld
    End If
   
End Sub
0
Exploring SQL Server 2016: Fundamentals
LVL 19
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hi

What Access VBA code would I use to check if a column called "CreatedAt" exists in a table called "GoodReceived" and  then create it as a Date/Time column if not. I want the new column to have a default value of Now()
1
I have a table which includes a "Yes/No" field.  See attached.
Some of the records have their "Yes/No" Set to Yes.  See attached
I created a query that includes only :"Yes/No" set to "Yes"
The result is a dataset where ALL the "Yes/No" Fields are set to "Yes"

Has anyone encountered this phenomenon?
Table-with-Checkmarks.JPGRecordsAttributeSelected.JPGQueryResults.JPG
0
Hi Experts,

Summary: Does anyone know why, when I make a table a hidden object using VBA code, and then turn on Show Hidden Objects from Navigation objects, the table don't show back up?

Situation:
I'm creating tighter security for a client's sensitive data with an MS Access front end and back end. The back end is password protected, which I know there are a lot of very easy to use utilities on the web where you can use to find out the Database password.  So I'm taking further steps to secure. One of them will be will be to remove the Navigation Pane, turn off the By Pass Key (with a secret way to turn it on) and use a custom ribbon that prevent a user from opening up Options. All that said, if a user can find the password for the database, they can Import or Link to the back end tables, UNLESS they are hidden. So I've written code to hide all the tables in the back end which is launched from an outside application.

tblDef.Attributes = dbHiddenObject

Open in new window


All this works fine. My question is... when hide tables in this manner, and I by pass the start up (using my secret way), and then go into File / Options and turn on Show Hidden Objects
in the Navigation Options, the hidden tables DO NOT SHOW. Does anyone know why?

I've added some code to turn the the hidden object off from the outside app, and when I do that, the tables reappear.

Any help on this would be greatly appreciated.

Thanks,
Eddie
0
Sorry people here;

 I have come again for clarifications on receiving the data from the machine RS 232 using Ms Access App. This week we have started testing the new machines we ordered last month to be used to account for Value added tax (VAT) and transmitting the invoices details one by one a time  to the taxman server. Sending is not an issue all is okay , but receiving certified data from the server , we are finding some challenges ,the received data is supposed to pasted in the table called tblInvoice , but its empty no data after processing.

I have gone through the code I cannot spot any error , neither are there any errors after processing, see below:


Receiving Code from RS 232


Private Sub CmdReadReceivedJson_Click()
Dim DataRead As String
  Dim DataObject As Object
  On Error GoTo Err_Handler

  If ReadDataFromSerialDevice(DataRead, 4) Then
    ' Call to JSON library to parse it.
    Set DataObject = ParseJson(DataRead)
    StoreData DataObject
    MsgBox "Data have written in the contact table", vbExclamation, "Please proceed"
    Set DataObject = Nothing
  End If
Exit_CmdReadReceivedJson_Click:
Exit Sub
Err_Handler:
Resume Exit_CmdReadReceivedJson_Click
End Sub

Private Function ReadDataFromSerialDevice(ByRef ADataRead As String, APortID As Long) As Boolean

  Dim lngStatus As Long
  Dim strData As String
  Dim PortID As Integer
  Dim intPortID As Integer

  ReadDataFromSerialDevice = False
  lngStatus = CommRead(PortID, strData, 14400)

Open in new window

0
Hi Experts,

I'm trying to populate a text box with a Dlookup based on a combobox value.
Informations:
Form containing the combobox: frmInspection
combobox: cboEquipment
table: tblEquipment
Unique ID in the table: [EquipementID]
Field wanted in the textbox: [HS]

This is the code I've tried but didn't work:

Set rst2 = CurrentDb.OpenRecordset("tblEquipment")
With rst
            Forms("frmInspection").Form.Controls("txtHS") = _
            DLookup(.Fields("HS"), "tblEquipment", Forms("frmInspection").Form.cboEquipement = _
            .Fields("HS"))
Wend
End With

Open in new window

0
I am moving to Access 2019 from Access 2010.  Initially I had a problem with hiding the ribbon on opening because it seems that the technique used in 2010 no longer works.  
I have now successfully hidden the ribbon when opening the application by using a commend in an AutoExec macro.
That is working fine.

But now testing in more detail I have a problem with my reports.  I currently have a custom tool defined for all my reports.  All this does is to show the Close button and the Print dialogue button at the top.

It seems that these are no longer supported because the buttons are no longer displayed when running the application in Access 2019.

I have done some research and it looks like I will need to create a custom ribbon and call that from each Report.  This requires coding in xml which I will have to mug up on because I have never done that before.

My question is - is this the way that this should be done?  Any tips to make this simple?

Or is it easier to just add two separate buttons to the footer of each report (although this would be more work because each report would have to be editted.

Advice will be very welcome.

Regards

Richard
0
Hi,
I'm a MS Access developper who try to put my database backend to the cloud and link it to the frontend on my desktop. My client is a business which want to do that in order to access its data from everywhere (or at the very least from the company's intranet network).
I see blogs and I think the better solution is to use a Sharepoint site but I know nothing about Sharepoint nor to install it, configure it, etc.

1. Do you know if Sharepoint is the solution to my problem?
2. Do you know how can I get it?
3. Is there exist a better solution to access my backend with my MS Access frontend from the cloud?
0
Hi

I want to loop through an Access table automatically to check if a field called "Registration Date" is 7 or fewer days away.
What VBA code would I use to do this?

Thanks
0
Hi

I am looking for Access VBA to send an Outlook email for any version of Office

Thanks
0
How to Generate Services Revenue the Easiest Way
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

I must be missing something here:

strSQL = _
"UPDATE ItemQuery SET ItemQuery.ItemDateCreated = Date()"
"WHERE ItemNumber=" & ItemNumber7
CurrentDb.Execute strSQL, dbFailOnError

The ItemNumber7 is a field name on the current form
The Where Clause is highlighted in Red in the code window.  I guess I have a comma missing somewhere
0
I have a Pop Up Form that has no control source. The form is opened from a command button on the main form. If in the Pop Up form I use:

If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
End If

Am I saving anything?  If not how do I save the main form when code from the Pop Up form Changes data in the main form?

What happens if I don't deliberately save data in either form?
1
Hi.  If the following code finds the start and end of last year, what similar code doI use to find the start and end of last month

txtStartDate = DateSerial(Year(Date) - 1, 1, 1)
txtEndDate = DateSerial(Year(Date) - 1, 12, 31)
0
Hi
In the following Access VBA code the only line that doesn't work is
Worksheet.Selection.Font.Bold = True


Public Function ExcelExportAndFormat(ByVal CRecordset As DAO.Recordset, ByVal CSheetName As String) As Object

  On Local Error Resume Next

  Dim Excel As Object ' Excel.Application
  Dim Workbook As Object ' Excel.Workbook
  Dim Worksheet As Object ' Excel.Worksheet
 
  Dim Count As Long
 
  Set ExcelExportAndFormat = Nothing
  Set Excel = CreateObject("Excel.Application")
  Set Workbook = Excel.Workbooks.Add
  Set Worksheet = Workbook.Sheets(1)
  Worksheet.Name = CSheetName
  For Count = 0 To CRecordset.Fields.Count - 1
    Worksheet.Range("A1").Offset(, Count).value = CStr(CRecordset.Fields(Count).Name)
  Next Count
 
  Worksheet.Range("A2").CopyFromRecordset CRecordset
  Worksheet.Cells.EntireColumn.AutoFit
  Worksheet.Rows("1:1").Select
  Worksheet.Selection.Font.Bold = True
 
  Set ExcelExportAndFormat = Worksheet
  Excel.Visible = True
  Set Worksheet = Nothing
  Set Workbook = Nothing
  Set Excel = Nothing

End Function
0
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
11/26 Forrester Webinar: Savings for Enterprise
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

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

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.