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 have a SQL database where I need to cycle thru 25 queries to make and append to a table so I can pull the right information.     I have one table w/25 values spread across 25 fields and in MS Access I have a form button the user presses and it runs the mk table and append queries.   I tried to replicate this in SQL starting w/creating a view and then using the Instead trigger - but that approach didn't work.    I'm not sure how to approach this problem or what terms/key words I should be looking at.   Any assistance would be greatly appreciated.
0
We get data from a client established SSRS.  Is there a way to set up a connection to SSRS in Access to pull that data in?
0
I have an access 2002 runtime running 3 pcs accessing data from a server.  2 pcs are windows 7 and one windows 10. The Windows 10 machine has an access version 16 on it.

When two or more users are accessing data the ethernet speeds are 1-1000kbs when one user is accessing data the ethernet speeds are in the tens of megabits and usually running up to 10 times faster.

We have disabled Leasing on the server (a known Microsoft issue)

I have 15 other installations with exact same application and all data on servers all using various windows OSs including a good number of Windows 10. The server has been checked by two independent IT companies and checked Ping rates etc. etc.

The only thing I am not sure about is if Leasing has to be disabled at each pc.
0
When I call a Stored Procedure in VBA in MS Access that has a lot of output parameters, when i look in SQL Server Profiler there are lots of records like exec sp_cursorfetch 180150003,2,1,1

This happens after the connection is opened, when the first parameter is defined in VBA

It is not slow when the .Execute is called.

in 1 stored procedure with over 100 OUTPUT Parameters it can take 0.5 seconds.

if I return a RecordSet with the same data in side - there is no delay - but I prefer Output Paramters when i'm returning a single row

Why is this?

This is an example of my code:

 
   
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    ' Connect to the database
    cnn.Open Driver={ODBC Driver 17 for SQL Server};Server=sqlserver\Db;Database=DbName;Trusted_Connection=yes;
    cnn.CursorLocation = adUseClient
            
    ' Set up the command object
    With cmd
        
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "Stored_Proc_Name"
            
        .Parameters("@Para1") = Para1
        .Parameters("@2Para") = Para2
        .Parameters("@Para3") = Para3
        .Parameters("@Para4") = Para4
        
        Set rs = .Execute
        
        OutPara1= Nz(.Parameters("@OutPara1"), 0)
        OutPara2= Nz(.Parameters("@OutPara2"), 0)
        OutPara3= Nz(.Parameters("@OutPara3"), 0)
        OutPara4= Nz(.Parameters("@OutPara4"), 

Open in new window

0
I'm trying to populate a text box on a form by clicking an item in a listbox (simple multi-select).  The name of the textbox is txtSelectedDriveThruInspectionsMonths.  The name of the listbox is listMonths.  Here is my code...

Private Sub listMonths_Click()
On Error GoTo Err_listMonths_Click

    Dim SelectedValues As String
    Dim frm As Form
    Dim varItem As Variant
    Dim listMonths As Control
    Set listMonths = Me!txtSelectedDriveThruInspectionsMonths

    For Each varItem In listMonths.ItemsSelected
        If SelectedValues > "" Then
            SelectedValues = SelectedValues & ", " & listMonths.ItemData(varItem)
        Else
            SelectedValues = listMonths.ItemData(varItem)
        End If
    Next varItem
    Me!txtSelectedDriveThruInspectionsMonths = SelectedValues

Exit_listMonths_Click:
    Exit Sub

Err_listMonths_Click:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_listMonths_Click
    
End Sub

Open in new window


But the error I'm getting when I select an item is:

"You entered an expression that has an invalid reference to the property ItemsSelected."
0
Hi Experts
Please I want a code or a mask to enter phone numbers assigned to one of the two … first for mobile phone numbers, for example, 248-0072-0115 (202) and the second is a pattern for regular telephone numbers (049-39-260) 202)
- For entering phone numbers, regardless of whether the number is for a mobile phone or for a normal phone, I would like to distinguish between the two types by typing zero if it starts with zero taking the mobile pattern and not starting with zero taking the normal pattern
0
Hi all, I have main form called workorder(single view) with subform paygroup(datasheet form)
this subform paygroup is having subform called payitem(continous form)
there is unbound textbox called totalvalue in footer section of payitem
now i want to refer that control totalvalue in main form workorder
can someone tell me how can i refer that control total value using vba code or
 i want that totalvalue to be displayed in mainform so is there anyway i can write expression to get that totalvalue in mainform
0
All-invoices1.accdbms access print invoices on a preprinted form

I need assistance to make the report printed on preprinted (A4 SIZE- DOT MATRIX ‎PRINTER)  

I need assistance to make the following change : printed on preprinted form with the following :
‎1.‎      Form "invbase_fm"  need to change customer text box to ComboBox  that show ‎customer num and customer name ‎
        All-invoices1.accdbDetails will show above customer in the same form
‎2.‎      Add button to print only selected invoices .‎
‎3.‎      Need to printed only 8 row from the invoice in same page remaining row in second ‎page.‎
Invoice_SAMPLE1.jpg
0
Good morning,

I am having problems with a piece of vba

Set rs = CurrentDb.OpenRecordset(“SELECT * FROM qryPropertyMatching”, dbOpenDynaset, dbSeeChanges)

The error that is coming back is Too few parameters, expected 3

 

any ideas?
0
Hi Experts
Is there a way to do conditional formatting.
So if we write a number in any field and there is a similar number in another field, the conditional formatting will work ... as shown in the picture
Field.PNG
0
Hi all, in ms access form can we hide values of combo box row source based on condition using vba code?
i mean say combo box row source is getting 5 records in drop down but i want to display only 3 records based on condition?
so user can initially see only 3 records in drop down but in case if he needs to enter value which wasn''t there in those 3 records but still exists in other 2 records then they need to able to select that record which wasn't there in drop down initially
is this possible using vba code? to hide values depending on condition?
0
Private Sub GetEmployee()
        'Clear COMBOBOX...
        cmbEmployee.Items.Clear()
        OLEDBControls.ExecQuery("SELECT empId,empName FROM Employees;")
        'If Records are found, then add them to COMBOBOX....
        If OLEDBControls.RecordCount > 0 Then
            For Each r As DataRow In OLEDBControls.OledbDS.Tables(0).Rows
                cmbEmployee.Items.Add(r("empName"))
            Next
            cmbEmployee.SelectedIndex = 0
            cmbEmployee.MaxDropDownItems = 5
            cmbEmployee.ValueMember = "empId"
            cmbEmployee.DisplayMember = "Name"

        ElseIf OLEDBControls.Exception <> "" Then
            'Report Error..
            MsgBox(OLEDBControls.Exception)
        End If
    End Sub

    Private Sub GetEmpID()
        Dim id As Integer
        id = Me.cmbSite.SelectedValue
        MsgBox(id)
    End Sub

    Private Sub cmbEmployee_DropDown(sender As Object, e As EventArgs) Handles cmbEmployee.DropDown
        GetEmployee()
    End Sub

    Private Sub CmbEmployee_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbEmployee.SelectedIndexChanged
        GetEmpID()
    End Sub

Please I want to return the employee id for a selected Text but it returns zero...please need your help
0
I am using MS Access with o365 on a Win10 Pro system.  My application has grown to about 500MB.

Its taking a very long time to open a specific form into code view.
For example, if I want to edit the onclick code for a specific control, I normally start by opening the form in design view.
Next I right click on the control, selected the event procedure, and then click to open into code view.

As my application has increased in size, it gets slow and slower.  For example, the process of right clicking a form to select design view may take 1 or 2 minutes before I can see the code.  Is there a workaround for this? Opening into code view only takes seconds.
.
0
I have a VB 10 project that utilizes 5 MS Access databases.  I need to know what references I need to add to my project deployment so that end users do not have to have MS Office installed on their system.  I load the databases when the project opens. I think I need the MS Office PIA dll for MS Access, but I would like a DB expert to advise me about this.
0
I have an Access 2016.accdb database and when I tried to opened I got this message:
"The Microsoft Access database engine could not find the object . Make sure the object exists and that you spell its name and the path name correctly. (Error 3011)"
Last time I used it it was fine, I always compact and repair when closing database.
0
We have a software package called Job Tracker running on the Amazon AWS cloud. Each of our customers has a separate copy of the back end database called Job Tracker Data.accdb which they link to via an Environnment Variable called DBPath set up in their Active Directory. The code that links to each table is

tdf.Connect = "MS Access;PWD=#########;DATABASE=" & DBPath & "\" & strBackEnd

That's fine within the main front end database codebase called Job Tracker Front EndDBPath.accdb, but how do we get to the same data from external programs other than MS Access?

Say I want to write a PWA accessing this data, how do I link in to each individual back end database?  Is there some ODBC way to do this? Or some entirely different mechanism?

It seems I could do it if each back end database was a SQL Server instance, but I would prefer not to have to convert every one of our users to SQL Server back ends...

Sambodh
0
Hi all, I need some help on MS access SQL query for this situation
I have main form  WorkOrder with  workorderID, Regionid, contractid, Typeid, Roadid etc(workorder table with workorderid as primary key and other fields are primary keys of their own tables)
I have continuous sub form with PayItems, rate, quantity etc (workorderpayitem table with workorderpayitemid as PK,workorderid as foreign key, payitemid etc)
ATM relationship is 1xworkorder will have 1xregion, 1xcontract, 1xtype, 1xroad many payitems
but now we need to modify existing form in such a way that each workorder will have many type,road and each type and road will have many payitems
say workorder --1000
will have type-1, road-1 which in turn have payitems-5
and again if user wants to enter 1xmore type and road then he will type type and road and for them he will again choose payitems
-------------------------------
To implement this scenario i have mainform as it is which is having workorderid, region, contract and has removed type and road columns frm there
this main form will have subform datasheet view to select type,road from dropdowns
when user selects type and road they will again need to select payitems which are in sub-sub continuous form
----------------
what i did is i added type and roadid foreign keys to workorderpayitem table
and record source for my datasheet view subform will be sql query
SELECT  WorkOrderPayItem.WorkOrderID, WorkOrderPayItem.RoadID, WorkOrderPayItem.WorkOrderTypeID…
0
Hi, I have main form work order which contains  continuous subform workorderpayitem...this means each workorder will have many workorderpayitemsID
Now i added new field called rownum in workoderpayitem table
this row num should display row numbers in the subform but it should display rownumbers as per workorder not numbers as per workorderpayitem form
to be more precise,, say i have workorder1 which has 5xworkorderpayitems so now that row number should display 1,2,3,4,5 in that bound textbox rownum only for that workorderpayitems which are there in workorder
for this i have created function Public Function ShowRecordNumber() As Integer

Me.RecordsetClone.Bookmark = Me.Bookmark

ShowRecordNumber = (Me.RecordsetClone.AbsolutePosition + 1)

End Function
and i am calling that function in rownum before update event
me.rownum.value = ShowRecordNumber()
but it's not displaying any value in the text box
so i tried to call that function in sql query ---ShowRecordNumber() as rownum
but it's throwing up error  undefined function
FYI i place this function in subform vb code at the top

apart from this i also used DCount("*","WorkOrderPayItem","WorkOrderPayItemID<=" & [WorkOrderPayItem].[WorkOrderPayItemID]) as rownum in sql query but it's showing rownumber considering all values in subform ..not based on particular workorder
also in subform user may delete some records so that means it must auto change that value(say if record 3 is selected then rownum 3 must point to…
0
Hi, I have an existing continuous subform with some controls.
Now i added extra 2 control buttons in the form. I arranged controls like i need and modified in layout view as well(i want my new controls to be in last but 1 position, they are like the way i want in layout and design view)
but when i switch to form view, 2 new controls which i added now got locked in same position where they are in layout view(new controls are now in middle of row like overlapping on some other existing controls)
how do i auto sync them with existing controls so that they appear same way as my layout view
what option do i need to select? as these are new controls do  i need to do some settings
please help me as i am pretty new to access
regards,
Lisa
0
Is it possible to put an MS Access 2016 back end database on SharePoint and link the tables to local front end?  I have an access 2016 database tool that I split into a front and back end for use in a multi user environment.  In prior projects I've done this using a shared network drive, but performance took a bit of a hit.  I've been asked if we can find a solution that works with SharePoint.  Is this possible and if so, how do we do it.

Thanks
0
Hi I have an access accounting program that I was brought into to fix.

I am on the last item and really need help wit h this please.

The client wants to be able to do this:

1. Press a button that will open a save file to dialog box
2.  In the save as box they want to be able to have the invoice number as the file to save, but as a pdf.
3. Finally when they press save the report is saved as a pdf as the invoice number, in the folder the user selected.

Any help. The previous programmer used cutepdf but the code is a mess

I am using a .mdb file in an Access 2013.

Thank you

Joe
0
Hi,

I'm having an issue get the Base64 HMAC on Amazon MWS correctly, I can get the SHA256 one to match perfectly but when I then try to convert to Base64 the result is incorrect, the code I am using to do the SHA256 part is as below

Dim lngLoop As Long
Dim oUTF, oEnc
Dim HMAC() As Byte
Dim strTemp As String
Set oUTF = CreateObject("System.Text.UTF8Encoding")
Set oEnc = CreateObject("System.Security.Cryptography.HMACSHA256")
oEnc.Key = oUTF.GetBytes_4("--------------")
HMAC = oEnc.ComputeHash_2(oUTF.GetBytes_4(StringToSign))
For lngLoop = 0 To UBound(HMAC)
     strTemp = strTemp & LCase(Right("0" & Hex(HMAC(lngLoop)), 2))
Next

Open in new window


But I believe this has be passed to the Base64 encoder as binary or a byte, but i'm having trouble converting a string in Access to a Byte? Is there an easy way to do this?

Cheers
Karl
0
need to change firstdayofweek in an access report
0
We have some coding inside of Microsoft Access where we pull data out of a database, create a .pdf and then send to users using Outlook.  This works most of the time but probably once a week, the email is generated with the report and then it won't send (we get an undeliverable error):

Your message did not reach some or all of the intended recipients.

      Subject:      Daily Lab Reports for 6/24/2019
      Sent:      6/25/2019 9:12 AM

The following recipient(s) cannot be reached:

      QC Database Email Group on 6/25/2019 9:12 AM
            This message could not be sent. The client operation failed. Try sending the message again later, or contact your network administrator.

__________________________________________________

Diagnostic information for administrators:
__________________________________________________

Error is [0x80004005-0x000004b9-0x00000501].
Submit-Message failed: message id(1), failure enum(6), HResult(0x80004005), EC(0).

This doesn't tell me much so I have no idea why it works most of the time but fails sometimes...

I have no issues sending emails using Outlook (manually) but it just seems to be problematic when something else is kicking off opening Outlook and sending the email.  I did turn off the "never warn me about suspicious activity" in the trust center - otherwise, we get the popup every time the task would run to "allow" another application to send email.
0
I have a client with an Access-based inventory system with very complicated pricing. The pricing is based on a query that performs a lot of calculations based on several tables. The pricing query is set up to work on all the records in inventory (about 50,000 items).

This query is used in pretty much every inventory report.

The problem I have is that even if a report has only a handful of items on it, the underlying pricing query seems to be calculating pricing for every item in the database meaning that every report is now becoming immensely slow.

As a test, I ran a report of all items for a vendor and I chose a vendor that only has 5 items. It was extremely slow. Then I deleted every item in the database except those 5 and the report was lightning fast.

In this example, the report is based on a parent query that pulls vendor and item data (including the pricing query) and the parent query has a record source that specifies just the one vendor. However, it appears that the pricing query is running on every item in the database and then subsequently being filtered based on the parent query.

How do I get Access to only run the child query (i.e. the pricing query) for the items that are on the report? I should add that the pricing query is a child query in dozens of other reports in the database so I'm hoping for a solution that isn't going to require a lot of coding dozens of times over.

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