We have a split database with the front end on 3 user pcs and the backend on the network
I have the only full version of access on my pc for editing the front end and the others have access runtime to use the database
Problem: Since I split the database this way it takes horrendously long to make any modifications to the front end design of queries, forms or reports. I mean when I go to save changes on a form it takes about 10 mins to do it. Likewise design modifications to a form are very slow. eg click a field to move it and theres a delay of 5 seconds before the field is selected then another 5 seconds to respond to the next mouse move. Frustrating
Can anyone shed any light on the reason for this and/or suggest a solution.
btw it doesnt seem to make a difference whether the other users have the database open or not.
I have a form that takes a long time to open – around a minute and sometimes longer. The form is based on a query, and this query on its own opens almost right away. Why would a form take so long to load up? What factors can increase a form’s load time?
I have 3 PC's on my Network accessing a back end which is sitting on the main PC.
i.e. Main PC has Front End and Back End PC 2 has Front End and is linked to Main PC's Back End PC 3 has Front End and is linked to Main PC's Back End
PC 2 and PC 3 take about 5 minutes to load the intial main form. Once it is loaded the performance is ok. I have a Gigabyte network so know it's not just a slow network.
I have had a look at many other threads and also read http://www.granite.ab.ca/access/performancefaq.htm but nothing has helped.
Any ideas about settings in say the options page that I may have set wrong.
FYI I have Default Open mode set to shared, Default record locking set to No Locks, and Open databases using record-level locking ticked. Not sure if these are correct or if there are other settings to consider here.
I have also found that once you open the initial form once, it then loads quickly if you close it and then open it again, but if you close the database front end and re-open it it then takes 5 minutes again.
The only answer I have at the moment is to never shut the darn thing...!
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type 12345, 67890, 1 12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345 12345, 54321 ...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
Amongst many other things i would like to be able to see which teams in our company have access to which database. We currently use up to seven!!! different Databases which makes for tremendous fun...just kidding.
Since every Team has access to more than one Database andevery Database obviously gets used by a multitude of Teams, i thought the best way to go would be a Junction Table. Whilst building this, however, it occured to me that i could have just created fields for each Database in the Team Table which then can be ticked.
So here's my question, is it more space consuming to create a junction table, it certainly appears to be more work right now...
I am truly stumped. I have a query that sometimes takes less than 1 second to run, and sometimes takes close to 5 minutes. I am making NO changes to the query. I first thought it was a network issue, so I moved the whole db, both front-end and back-end, offline. No change. So I thought maybe it was a linked table issue, so I moved all the tables used by the query to the front end. No change. Every once in a while, the query runs at lightning speed, less than 1 second. Most of the time, it takes 3-4 minutes, or longer.
Here's the SQL: SELECT PCAInternalPareto1a.row, PCAInternalPareto1a.column, PCAInternalPareto1a.Noun, PCAInternalPareto1a.Rev, PCAInternalPareto1a.InspPoint, PCAInternalPareto1a.cell, PCAInternalPareto1a.DefectType, CLng(nz([firstofTruncatedOpSeq],0)) AS ZOpseq, Sum(PCAInternalPareto1a.DefectQty) AS SumOfDefectQty, PCAInternalPareto1a.poo, PCAInternalPareto1a.repcode FROM PCAInternalPareto1a LEFT JOIN BOMOutRefDesOnly ON (PCAInternalPareto1a.RefDes = BOMOutRefDesOnly.RefDesOnly) AND (PCAInternalPareto1a.partno = BOMOutRefDesOnly.PCAItemNo) WHERE (((nz([FirstOfComponentItemNo],0)) Like "*")) GROUP BY PCAInternalPareto1a.row, PCAInternalPareto1a.column, PCAInternalPareto1a.Noun, PCAInternalPareto1a.Rev, PCAInternalPareto1a.InspPoint, PCAInternalPareto1a.cell, PCAInternalPareto1a.DefectType, CLng(nz([firstofTruncatedOpSeq],0)), PCAInternalPareto1a.poo, PCAInternalPareto1a.repcode;
PCAINternalPareto1a is itself a query, which always runs fast. BOMOutRefDesOnly is a table with about 84,000 records. I have indexes on the joined fields.
I know the where statement looks useless but I have my reasons for doing it this way. I tried deleting the where statement and it ran really fast. Then I re-added it, and it still ran really fast. THen slow again. I can't find any relationship between anything I've tried and the speed of the query!!!! What's going on??????
I know this is probably the dumbest thing to ask, but I am just not getting it. I'm tryin to run a Query, and I assume it's a SELECT query for Last Name, First Name, SSN. The tbl containing this information [tbl_Employees] automatically places the dashes in the SSN through an input mask. However for one type of report I will later need to generate, the SSN cannot contain the dashes. What is the criteria for this?!? I am so lost! I know it's probably something so simple that I'm just overlooking it, but I'm clueless. Can someone help me out? Thanks so much! :)
I'm running this query off a table of about 1.5mil records. If I leave the date parameter off It comes up under a minute. When I add it back, it takes 5 minutes before the query even starts running. All the fields used in filtering the query are indexed. Any ideas to speed this up?
SELECT CallsEntered.[Work Order Nbr], CallsEntered.[Date Entered], CallsEntered.[Time Entered], CallsEntered.[Primary Locator Code] AS [ASC], CallsEntered.Headend, CallsEntered.Node, CallsEntered.[Grid Id], CallsEntered.[Q Code], CallsEntered.[Problem Code 01], CallsEntered.[Primary Finding Code], CallsEntered.[Primary Solution Code], CallsEntered.[Cancel Code], CallsEntered.[Scheduled Date], CallsEntered.[Wo Status], CallsEntered.[Date CheckIn], CallsEntered.[Assigned Installer], Calendar.Week, Calendar.Year FROM Calendar INNER JOIN CallsEntered ON Calendar.Date=CallsEntered.[Date Entered] WHERE (((CallsEntered.[Date Entered]>=Forms!frmServiceCalls!txtStartDate Or Forms!frmServiceCalls!txtStartDate Is Null)=True) And ((CallsEntered.[Date Entered]<=Forms!frmServiceCalls!txtEndDate Or Forms!frmServiceCalls!txtEndDate Is Null)=True) And ((CallsEntered.Node=Forms!frmServiceCalls!txtnode Or Forms!frmServiceCalls!txtnode Is Null)=True) And ((CallsEntered.[Primary Locator Code]=Forms!frmServiceCalls!cboASC Or Forms!frmServiceCalls!cboASC Is Null)=True) And ((CallsEntered.[Q Code]=Forms!frmServiceCalls!cboQCode Or Forms!frmServiceCalls!cboQCode Is Null)=True));
were each number comes from a query field..Can I add say Migrated and Excluded within the report and possibly other calculations that I may wish to include.
I have a following problem: I have a form in continuous mode. Users are supposed to filter data in it to their liking using inbuild filters in Access. After that there is a button that should calculate total weights in filtered records (at this point, I add more calculations once I have solved this issue). Code of button is as follows:
Code: Private Sub btnCalculateWeight_Click()
Dim rst As Recordset Dim weight As Long Dim material As Long Dim subtype As Long Dim locus As Long
[Code] ....
Loop goes through recordset correct amount of times but for unknown reason takes value only from the first record and sums it N times where N is number of records in recordset. Recordset seems to be correctly assigned according to messageboxes I added to pinpoint problem. PotteryWeights is a custom function I made and it works properly.
I have a form that I created that has a tab control. When the form loads, it automatically sets the focus to a field in the subform. This wouldn't be a problem, except when it does it hides the tabs at the top of the form so I have to go and scroll back up every time I click a tab so that I can see the tabs. Is there a way to adjust this? I have tried hiding a field and setting the focus to it and that didn't work. I also tried setting the focus to the actual tab, but that also did not work.
I'm experimenting with unbound object frames to embed an excel sheet in Access.If I have an excel application already open and busy, and then open my form, access freeze completely and won't do anything. My users will probably be running excel vba programs when they attempt to view this form, if there is no work around I'll have to drop the idea of embedding excel in access.
whenever an "external" thing wants to use excel (opening a file in explorer, etc), it uses the very 1st excel application open on the computer at this moment, instead of creating its own instance, or at least scanning for how busy the open application is. Because of that, I think that even loading the preview in the access form will try to use the current excel process even though it's completely busy.
I am trying to create a query that takes values from a search form and provides records. I was having issues with getting results when some boxes on the form were left blank. I found a solution to that and it worked with a small number of fields. However when I make the full form query (about 8-10 fields) and run it says the query is too complex. I wrote the sql as I could not get designer to do what I wanted. Attached is the sql that works and that which is "too complex".
Here is my sql statement that I want to get the total number of "yes" in each field. Each field is a text field.
SELECT Sum(Homeless="yes")*-1 AS HomelessCnt, Sum(NFresident="yes")*-1 AS NFresidentCnt, Sum(Self="yes")*-1 AS SelfCnt, Sum(StaffBoard="yes")*-1 AS StaffBoardCnt, Sum(FamilyFriend="yes")*-1 AS FamilyFriendCnt, Sum(CommLarge="yes")*-1 AS CommLargeCnt, Sum(ServeProvide)*-1 AS ServeProvideCnt, Sum(Physical="yes")*-1 AS PhysicalCnt, Sum(MentalEmo="yes")*-1 AS MentalEmoCnt, Sum(Cognitive="yes")*-1 AS CognitiveCnt, Sum(Vision="yes")*-1 AS VisionCnt, Sum(Hearing="yes")*-1 AS HearingCnt, Sum(Multiple="yes")*-1 AS MultipleCnt, Sum(AdvocacyServe="yes")*-1 AS AdvocacyServeCnt, Sum(AssistDevServe="yes")*-1 AS AssistDevServeCnt, Sum(ChildServe="yes")*-1 AS ChildServeCnt, Sum(CommServ="yes")*-1 AS CommServCnt, Sum(FamilyServe="yes")*-1 AS FamilyServeCnt, Sum(HousingServe="yes")*-1 AS HousingServeCnt, Sum(ILSkillServe="yes")*-1 AS ILSkillServeCnt, Sum(InformationServe="yes")*-1 AS InformationServeCnt, Sum(MentalRest="yes")*-1 AS MentalRestCnt, Sum(MobilityServe="yes")*-1 AS MobilityServeCnt, Sum(PeerServe="yes")*-1 AS PeerServeCnt, Sum(PersonalServe="yes")*-1 AS PersonalServeCnt, Sum(PhysicalRest="yes")*-1 AS PhysicalRestCnt, Sum(PreventiveServe="yes")*-1 AS PreventiveServeCnt, Sum(Prostheses="yes")*-1 AS ProsthesesCnt, Sum(RecServe="yes")*-1 AS RecServeCnt, Sum(RehabTech="yes")*-1 AS RehabTechCnt, Sum(CounselServe="yes")*-1 AS CounselServeCnt, Sum(Therapeutic="yes")*-1 AS TherapeuticCnt, Sum(TransportServe="yes")*-1 AS TransportServeCnt, Sum(YouthServe="yes")*-1 AS YouthServeCnt, Sum(VocationServe="yes")*-1 AS VocationServeCnt, Sum(OtherServe="yes")*-1 AS OtherServeCnt, Sum(Newsletter="yes")*-1 AS NewsletterCnt, Sum(EventFlyer="yes")*-1 AS EventFlyerCnt, Sum(VFIBrochure="yes")*-1 AS VFIBrochureCnt, Sum(WaiverInfo="yes")*-1 AS WaiverInfoCnt, Sum(Survey="yes")*-1 AS SurveyCnt, Sum(Other="yes")*-1 AS OtherCnt, Sum(Advocacy="yes")*-1 AS AdvocacyCnt, Sum(SkillTraining="yes")*-1 AS SkillTrainingCnt, Sum(PeerSupport="yes")*-1 AS PeerSupportCnt, Sum(Deinstitutionalization="yes")*-1 AS DeinstitutionaliztionCnt, Sum(ReferralPAS="yes")*-1 AS ReferralPASCnt, Sum(ReferralVFI="yes")*-1 AS ReferralVFICnt, Avg(MinSpent) AS MinSpentAvg, Avg(CallerAge) AS CallerAgeAvg FROM PersonalInfo WHERE (((PersonalInfo.CallerCounty)=[Forms]![CityStateCountyForm]![CmbCallerCounty]) AND ((PersonalInfo.CallerCity)=[Forms]![CityStateCountyForm]![CmbCallerCity]) AND ((PersonalInfo.CallerState)=[Forms]![CityStateCountyForm]![CmbCallerState]));
It says: "This expression is type incorrectly, or it is too complex to be evalutated. Try Simplifying the expression by assigning parts of the expression to variables."
Does this mean I can not do it all in one query, or how do I assign variables. Sorry I am kinda new to access. Thanks for the help.
I have an autonumber field set up as long integer. The field just reached the value of 32670 and I get the overflow message. I thought a long integer could be much bigger than that before running into that problem.
I got around it by re-creating the field and starting from 1, but would rather know why it's doing it so I don't have users without their system.
I would like to have a back-end Database on a host server and link a front-end Database to the tables several hundred miles away. The problem is I have no experience whatsoever in doing this.
My questions are:
(1) Can tables be linked via the Internet?
(2) If they can, is this the best way to link the tables?
(3) If it is possible to link the tables by the internet or by some other way; how do I do it?
I am using Access 2007 and trying to set the BackColor property of a text box on a form using VBA Code in the Form_Open event with code like this:
Me.txtName.BackColor = <some global variable>
Here's the thing: In order to get the correct color numer, I first pick my desired color from the palette to see its value. In this case, a very bright red comes up as #ED1C24.
In Access 2003 and earlier, this value would be represented as a long integer, not a Hex. So, my question is how to get the HEX value converted to a long integer??
The BackColor property is supposed to be a long integer data type, but that is NOT what the design interface is presenting to me!
Why did Microsoft change the BackColor property of form and report objects from long integer representation to Hex?
I'm sorry if I'm asking relatively simple questions. I'm a break/fix guy, and our in house programmers told us it would be 6 months before they could help us out, so we're tackling this chore ourselves.
We have a database of company names along with corresponding contact information. Each company has at least one associated id number. One company may have several numbers, but each number is unique. A very simple creature. I'm using a combo down box to allow the users to search by company name. This box only pulls the distinct company names. There is also a second combo box that has every id number. These are the only two methods used to navigate through the form.
How can I set up these two boxes to "track" each other? Right now, if I select a company name, the id number box doesn't move to its corresponding entry, and vice versca. How can I set these two boxes up to follow one another as a user navigates?
Our second problem might be a little more challenging. Like I said, each company may have more than one id number. We would like to set up a query that would pull all of the company id numbers when a user selects a name using the above drop down box and insert them into a text box.
The scenerario is the following: I have a table that keeps information for weeks about some files (e.g: if the file has been sent). Then I have another table with information for days in a month (the data for every day).
What I should do is: Check wheter the week has been sent, if it has been sent, I have to show the data for every day of that week in a report. Only for one month... and I can't figure out how to build that query...
I guess I should do something like: select week from weekTable where week=week and sent=TRUE set rcset=db.openrecordset(sql) if(not rcset.eof)then 'build in here the query to select the data I need from the month table end if
but i can't figure out how the loop would be for 1 month...
I have an Access form linked to two SQL tables, I had to make a change to the field length/datatype of a field named notes. The form now tells me that the "text is too long to be edited" whenever I try to add data to that Notes field in my form. I went from char to nvarchar... how do I fix this?
Hi, i'm not sure if this should be posted under queries or forms... i have a series of combo boxes in a from based on a query. each combo box needs to display options for characteristics of each section of a motor (ie. combo box for 'Corroded' = 'slightly', 'very', 'not at all', combo box for 'dents' = 'none', 'top', etc) my table structure works like this: i have a parent defintion tbl that defines the list of combo boxes (i.e. 'corroded', 'dent', 'rub marks') and assigns then and ParentID number that links to the tbl:InfoObjectDefinitions that defines ALL the possible characteristics ('slightly', 'very', 'not at all','none', 'top') and assigns them an ObectID number. So in a nutshell, i can pull the possible numeric options (OID) and text values('slightly') for 'corroded' from the table InfoObjectDefinitions according to 'corroded's PID. I then store the OID in the column for 'corroded' in tbl:Repairs. so i need to create a form that lets people add/edit entries in the repair tbl using the text values and not the id numbers. i can create a query for the form, that joins the repairs table and the InfoObjectDefinitions table, and substitutes the numeric values from the repairs table with the text values in InfoObjectDefinitions but then the comboboxes arent editable???? (the combobox expands, and i can see all the text values but cant select a new value) The SQL that created my query 'repairFormTest' is: SELECT Repairs.FENumber, Repairs.Corroded, (select otext from InfoObjectDefinitions where pid = 17 and oid = Int(Corroded)) AS CorrodedTxt FROM Repairs; and the form places the field CorrodedTxt as the Control Source of the combo box with 'select otext,oid from infoobjectdefinitions where pid = 17' as the Row Source. And as i mentioned, the box lokks right, and expands correctly but i can select another value. Any help appreciated, i really stuck..... Thanks, Vauneen
I have written a bit of code in a Microsoft Access 97. The code basically creates a Microsoft outlook message. I am trying to add in the message that I want to display in Outlook by using the MyMessage ="" command. But the message I want to add is too long to fit on one line of code - I get to the end of the line and can no longer type any more! I have tried to add the second part of the message to the next line but all I get is an "Expected line number or label or statement or end of statement" error message. I think this might be because access keeps putting a " at the end of the first line - every time I remove it and add the " to the end of the second line it keeps adding it back to the end of the first line!
