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

creating View  in MYSQL SERVER getting to much  slow down for  more then  twenty thousand records.
The view have been created in mysql. But when i click on the browse then it getting  slow down . main issue is that how can i handle large records in mysql views. Aactually i am converting Ms Access Database into mysql . there is lot of queries in Aaccess DB that i have to need use as view in mysql . but for large getting queries when i use in mysql for view then it gett very slow .


here is the example of query that is used for creating view in mysql server.it is fetching twenty thousand records.

SELECT FormulaInfo.FormulaInfoID, (Sum(formuladetail.Qty*fragrance.cost*unittable.unitconverter/`unittable_1`.unitconverter)/totalqty) AS UnitCost,
 Format(Sum(formuladetail.Qty*fragrance.previouscost*unittable.unitconverter/`unittable_1`.unitconverter)/totalqty
, 'Currency') & ' / ' & `unittable_1`.unitcode AS UnitPCost
FROM (UnitTable INNER JOIN Fragrance ON UnitTable.UnitID = Fragrance.Unit) INNER JOIN
(((FormulaInfo INNER JOIN FormulaDetail ON (FormulaInfo.FormulaID = FormulaDetail.FormulaID) AND (FormulaInfo.Revision = FormulaDetail.Revision)) INNER JOIN UnitTable AS UnitTable_1 ON FormulaInfo.Unit = UnitTable_1.UnitID) INNER JOIN Qry_FormulaList_TotalQty ON FormulaInfo.FormulaInfoID = Qry_FormulaList_TotalQty.FormulaInfoID) ON Fragrance.FragranceID = FormulaDetail.FragranceID
GROUP BY FormulaInfo.FormulaInfoID, Qry_FormulaList_TotalQty.TotalQty, …
0
We have a MS access form which connects to a SQL Server 2012 table.  Occasionally the Access application times out when trying to update a specific field in the table.  The odd thing is that while the application freezes we are able to open sql server manager and update the record directly. This indicates the issue is not with a db lock.  

Any thoughts on what we can do to dig deeper?
0
Hello All,

Even though I'm new to access, things were progressing well, but I've hit the wall with a few issues.  I've inherited a split database with four pieces, backend version, frontend version and two user versions in accde form.  The users wanted some changes, so I needed to add a few forms, modify a few forms, add a table and come up with the queries for the changes.  That work is complete and working well, but I wasn't able to convert the new frontend accdb version to the user accde versions.  I'm getting an AutoExec, Runcode Error 2045.  As a temporary solution for the users I posted the accdb versions, but most are getting a AutoExec, Runcode Error 2001.  Some of the users are able to open the database, but all the team members need to be able to get in.  I'm wondering what the differences  would be between the users who are able to get in and those that aren't.  I've added the location to the trusted sites and I've changed there macro settings with no success.

Any thoughts would be appreciated, thanks, Bill
0
how can I print the report header in every page, I see the page header printing but not the report header
please help
0
Import an excel file into Access 10 using the Saved Imports in External data. However the table created is saved using the wrong name, is there a method to change the name of the created table. Its a very complicated import, where I have changed the formats, so I don't want to recreate the import from scratch, just want to know is there a way to modify without code. If not, is there a dummies way of doing it using code.

Thanks
0
how can get next records from access file in windows form using next button like in online exam form when we click next button next question appear i want this code.please help me to solve this problem
0
Hi, I am trying to import from excel to access. I have executed this process for over 10 years and now since I have upgraded to Access 2016 it is giving me issues. I have a Excel spreadsheet when all the information is imported however, the data that I have in my "AdminComments" field is not. Once I have imported there is a table within Access 2016 created that has Datatype Conversion Failire for this particular column.

I made sure the data in the excel spreadsheet is text but it still isnt working. I am using Longtext because the text can be lengthy.
0
Hi All,,

My Expression is made in 3 pieces in multi-lingual languages.

The expression is :
               ":تحصيل شيك رقم:" & [PNcmbx] & "من برسم التحصيل"
The first and the last texts are in right-t-left language. The middle is a field in English and shows correctly in its own box.

The result is on the Form shows like in the attached image:

When I tried to copy it from the text from Form or from the Table and past it here (or in NotePad or Word),  It becomes like:
                   تحصيل شيك رقم:00102-CHمن برسم التحصيل

What I want is to show is as in the Image but keeping the PNcmbx (The middle one) as 00102-CH since it is stored as one piece i.e. 00102-CH  and shows correctly on in its own field.

Help please
CaptureRemarks1.PNG
0
I have Database in MS Access of my application and Now i want to convert into MySQL server database . Actually problem is that i have successfully import all tables by MySQL ODBC Driver. But there is some queries in access database and i am trying to convert into MySQL as views. when i am creating views from access databases query after change the query syntax as sql query . in some case the it will take too much time.
My main Question is that what is the best way to handle these access database queries in MySQL. is as Views or Store procedures or as Functions in MySQL server .please quid me and give me the right suggestion .

Thanks in advance
2017-05-12_2310.png
0
I am making code in access

Dim db As Database
Dim rs As Recordset

Whin I click debud.. compile database..
Coming message

Compile error:
Expected user-defined type not project

What's problem that...? And what is wrong?  
Khalil98
0
Hi all,

I have a form that has two layers of nested subforms.  I'm trying to display the total of [sales], which is a column in the second nested subform (let's call it subform2) in the first nested subform (subform1) above it.  The data in subform2 is the detail for the record in subform1.  The totals display in subform2, I just cannot get them to display in the summary record above them.  The same calculated total for the first set of records displays in all the records in subform1

In the act_sales field for subform1, the control source is the following:
=DSum("[sales]","frmqry_select_results_detail","[control_number]=" & [subform_select_results_detail].[Form]![control_number])

I've attached a screenshot of the output to help visualize what I want.

TIA, Mike
nested_subtotals.JPG
0
Hi,  despite being a fairly confident Access developer, I have thus far never had the requirement to include charts or graphs.  I can accomplish exactly what I want in Excel (see pictures) but despite trying for days, I can't seem to accomplish the same thing in Access.

 Excel Data    Excel Chart
This is the query that holds the information in Access:
Access Query
Any advice would be appreciated.
0
access 2007  database running very slow
0
I am not able to see the statements present in an append query
0
I have a form in which I've added a couple attachments fields. Users can add all type of attachments to the DB. But, I do not want ANYONE to utilize the REMOVE button from the attachments field. How can I get access to the pop-up to prevent the clicking of the REMOVE button?
0
Ordinarily, when I get an error message and the error handling is disabled,
the system will show me where the error is, so that I can debug it.
However, the above error message does not show me where the error is, and I have
no idea where to debug it.
0
i have a four fields data in a table in ms access of 192 employess. i want to make a report type of letter in text format.The four fields are empno, empname, value, audit_no. i also want to put these fields in letter type format report. in parameter when i enter empno it show me report of one person data and then ready for printing. thanks in advance
0
I have 2 tables: Main and States
In my Main form I have a sub form for states. All states are listed and I can check or uncheck any state.
When I create a record in Main, sometime I only need 1 state checked and sometime I need 40 or 48 states checked.
Currently, all states are checked by default. When I create a record that requires only 1 state selected, I have to uncheck 49 of them and it is getting on my nerves.

Is there any way I can create a button that when pressed ALL states are selected or ALL states are un-selected?

In the States table, each state has its own field and the name is the abbreviation (CA=California, etc..)

Thanks

Mike
0
First time poster.
Would like a bound field in mainform, donorid_tx, to change its value when I update the subform field DONORID which goes through previously used donors using key DONORID. The donorid_tx on the main form has a its record source as a query with appropriate fields in place.
suggestions keep telling me to change the control source of donorid_tx to reference the subform- I cannot do that because I want the control source to remain tied to the table where this data will go. So confused. Is it a problem that this donorid_tx isn't in another subform with the table it belongs to as a recordsource? (this table is a list of events for which donorid_tx is a number id and not a primary key. DONORID in the subform is a primary key in my table list of donors....
0
I've written this "optimal" query.

There is a list of people, each person is assigned, position, value, and points.

The optimal has to select a row of 8 Names with the most total Points where the total Value is under 25,000. No name can be duplicated. There can only be one person from each group except group "FF", where there is 3 (6 groups total).

Here is the query (VBA):
    CurrentDb.Execute ("INSERT INTO OPT_FINAL SELECT TOP 25 " & _
    "FIRST(T1.P1) AS P1, " & _
    "FIRST(T2.P2) AS P2, " & _
    "FIRST(T3.P3) AS P3, " & _
    "FIRST(T4.P4) AS P4, " & _
    "FIRST(T5.P5) AS P5, " & _
    "FIRST(T6.FF) AS F1, " & _
    "FIRST(T7.FF) AS F2, " & _
    "FIRST(T8.FF) AS F3, " & _
    "(T1.Pts+T2.Pts+T3.Pts+T4.Pts+T5.Pts+T6.Pts+T7.Pts+T8.Pts) AS PTS, " & _
    "FIRST((T1.Value+T2.Value+T3.Value+T4.Value+T5.Value+T6.Value+T7.Value+T8.Value)) AS Val FROM " & _
    "(SELECT Name AS P1, Pts, Value FROM OPT WHERE POS='P1' ORDER BY Pts DESC) AS T1, " & _
    "(SELECT Name AS P2, Pts, Value FROM OPT WHERE POS='P2' ORDER BY Pts DESC) AS T2, " & _
    "(SELECT Name AS P3, Pts, Value FROM OPT WHERE POS='P3' ORDER BY Pts DESC) AS T3, " & _
    "(SELECT Name AS P4, Pts, Value FROM OPT WHERE POS='P4' ORDER BY Pts DESC) AS T4, " & _
    "(SELECT Name AS P5, Pts, Value FROM OPT WHERE POS='P5' ORDER BY Pts DESC) AS T5, " & _
    "(SELECT Name AS FF, Pts, Value FROM OPT WHERE POS='FF' ORDER BY Pts DESC) AS T6, " & _
    "(SELECT Name AS FF, Pts, Value FROM OPT WHERE POS='FF' 

Open in new window

0
Hi and thank you for the help.

Any ideas why I am getting an error?

Private Sub Company_Veteran_Employment_Standard_AfterUpdate()
     Me.TabCt38.Pages("Page39").Visible = False
     Me.TabCt38.Pages("Page40").Visible = False
     Me.TabCt38.Pages("Page41").Visible = False
     Select Case Me.Company_Veteran_Employment_Standard
         Case " At least fifty one percent of the business is owned by veterans or persons on active service"
             Me.TabCt38.Pages("Page39").Visible = True
         Case "At least ten percent of the employees of the business are veterans or on active service"
             Me.TabCt38.Pages("Page41").Visible = True
         Case "If the business is a corporation and is not owned by veterans or persons on active service, at least fifty one percent of the board of directors are veterans or people on active service"
             Me.TabCt38.Pages("Page41").Visible = True
             Me.TabCt38.Pages("Page39").Visible = True
         Case "Certified by the U.S. Dept. of Veterans Affairs as a Service-Disabled Veteran-Owned Small Business or a Veteran-Owned Small Business and the owner(s) of the business meets the definition of veteran (OAC 123-5-1-01 (II))"

    End Select

End Sub

 
____________________________________________________________________

Private Sub Form_Current()
     Me.TabCt38.Pages("Page39").Visible = False
     Me.TabCt38.Pages("Page40").Visible = False
     Me.TabCt38.Pages("Page41").Visible = …
0
Is it possible to save on the form a value selected in and unbound drop down box.  The unbound drop down box has only two values (Yes/No).  I do not want to save the value in the database because there are several of these YES/No drop down boxes on the form.  Theses drop down boxes are use to jsut answer questions posed.  I would like to be able to reopen the form and see the answer selected.  I will not need the answer to run and reports etc.  Any suggestions?

Thanks
0
I have this code:

    DoCmd.OpenReport "Statement", acViewNormal
    DoCmd.PrintOut A_PRINTALL, , , acHigh, 2, 0
    DoCmd.Close A_REPORT, "Statement"

I want the 2 identical statements to print one after the other, is not working
0
In search of the top Web Browsing Reporting solutions. Implementation types and the Pro's and Con's.
0
Hi all,

I'm looking for some help writing a rather complex query against my Access database. I have no dedicated SQL experience, only advanced Excel/Access experience.

I have a single database setup that has orders going back 6 years.

I'm trying to compare the Historical orders with the Current orders and flag the Current Orders that fall within a -120 & +90 date window around the expected contract End Dates from the Historical Orders. Problem is that for large customers, they can have hundreds of orders, all with different date ranges, and they all don't need to be flagged. Only the ones that fall within the window in which we expect the next order to be placed.

I've created unique customer ID's for all customers so that part is not the problem. Only the date window piece.

Any help would be much appreciated. I'm open to exploring all options as well, this doesn't necessarily need to be done via SQL if there is a better known way.
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.