Hi,
I have been using the following query, literally for years, without any changes. I run it from code using db.execute, and I do use the dbfailonerror option.
One of the things it does is to add a '3' to the end of the [serial]. [serial] is the primary key in the TST3 table. You might think that there would be a problem if, say, I have a list of serials containing
35
56
1
13
and I'm trying to update them to
353
563
13
133
But this has worked OK in the past. NOW I'm getting a KV Error when it tries to update the 1 to 13, because there's already a 13 in the table.
Even stranger, when the query fails, all the rows BEFORE the offending record DO get updated. So the query fails, and I end up with:
353
563
1
13
(and yes, I DO have dbfailonerror set)
So, it looks to me as if update queries are no longer running as transactions.
I am pretty certain that action queries have always been run transaction-wise in the past... if the query fails, the whole thing should fail. WHY is the transaction processing no longer working for this update query? Has anyone else noticed this?
I recently ran microsoft update and am now running
Access 2002 (10.6771.6830) SP3.
I have a lot of tables that need updating often, so I delete the old ones and re-import them then run the queries again. From time to time, I add / remove columns but in the case I removed columns that are NOT needed in the queries.
So, I ran the queries again and they run fine but it says "Enter Parameter Value" for a column that is no longer there. It's not in the SQL code either.
So where is it trying to get these columns from and how do I fix this?
We are using MS Access as the backend to our application which has been written in delphi and have run into a problem that we have not been able to solve. Hoping someone has run into this before or any suggestions are much appreciated.
The problem:
MS Access runs slowly for client PC's after a update or insert.
- I am using ADO to connect to the Access database, which is using the OLEDB for ODBC Provider. - The application I have sends queries (both select and update) direct to the database (ie client datasets are used). - When only select queries are sent to the DB the response time is fine. - When an update or insert query is sent to the DB the response time of the PC it is run on is fine. - When an update or insert query is sent to the DB the response time of any other client PCs running the application take about 5 to 6 times longer to run queries than before the updateinsert query was done. This is the issue that I am having. - Any client PC's that display this slower response time, can have their response time returned to normal by closing down the application and restarting it. - No more than 3 PC's connected at one time to the DB. - Maximum database size of 150MB. - Problem occurs on various network setups, including domain and workgroup. - Problem only surfaces for users at times well after any application updates have been applied (ie several weeks after, and then once the problem starts it continues). - It does not occur for all user sites.
I have tried and thoroughly tested the following to no avail... - Applied all the latest microsoft updates - Closing and re-opening the ADO connection after updatesinserts - Changed the ADO provider to Jet 4 - Saving the DB in Access 2000 or 2002 format - Set the Default record locking to 'No Locks' and 'All records' and 'Edited record' - Used 'Open databases using record-level locking' selected and unselected - Many application techniques (using delphi) to work around the issue. Many of which have indeed improved general response times, but have not resolved this particular issue.
The only thing I have tried that has resolved the issue is... - Upsizing the database to SQL Server (Unfortunately this option is not a viable one for us at this stage, so I need to find a resolution to it while still using the Access DB).
I have a userform that pops up when I am implementing a VBA subroutine. The nature of the form is simply to update the user what progress through the operation the code is using a label called lblProgressText.
So, I have a form called frmProgress and in my loop I use:
The lblProgressText control just wont update (but earlier today it was so maybe I have broken something).Btw, all this code is run from a Module, not in the form object.
Dear pro, I am having difficulties running a simple query that it runs all day and I have to kill it at the end of my date because it does not finish. Here is the statement that I would like some suggestions on how to improve it for faster results: SELECT code, acct nbr, date, user, sum(amt) FROM Tbl a WHERE code IN ('AB', 'BC', 'CD', 'AA', 'BB', 'CC', 'ZZ' ) AND date BETWEEN TO_DATE ('20070101', 'YYYYMMDD') AND TO_DATE ('20070131', 'YYYYMMDD') group by code,acc nbr,date,user
I have a table with a rotating work order number (from 1 to 9999 then starts over) and because there may be more than one record with the same work order number I need to find the most recent one. Here's the query I'm using the find the most recent record for each work order number: SELECT * FROM [Work Orders] AS wk1 WHERE NOT EXISTS( SELECT * FROM [Work Orders] AS wk2 WHERE wk1.[Work Order #] = wk2.[Work Order #] AND wk1.[Call In Date] < wk2.[Call In Date] );
Right now the table has a litttle over 10000 records and it takes anywhere from 10 seconds to 60 seconds to return the results. Is there a way I could possibly speed up this query?
I have been looking around the forums for a little while now... and cannot seem to find anything which will help me.... heres my query..
I have a stock control database.. which allows for sales transactions via a barcode reader...
my first query is.... when the user scans a product.. how can this be 'looked up' in the (UPC data table)... returning the correct information.. ie.. description.. but then taking the price from the product table.. the query doesnt seem to let me be able to join the two tables.. no idea why!?
secondly.. I want to create a form.. for the 'sales transaction'.. when the user clicks 'pay' i want the form to clear and store all these items in the sales table.. (deducting the units in stock at the same time..)
I hope anyone has any suggestions.. or pointers! Many Thanks :)
I apologize up front for how long this is, but the queries involved are a bit complex.
I developed a database in Access 2003 running on XP sp2 to score events for our dog club. Everything works. I recently had to migrate to Vista. I installed Access without a problem. No changes to the database were made. Almost everything works except for a few queries. I continually get the error "Object invalid or no longer set." This is not running on a network - it is local on the hard drive. Setting XP compatibility mode and runing as administrator do not help.
The query that is actually causing the problem is the third in a chain, meaning it queries a dataset produced by another query, which in turn queries a dataset produced by a third query. This all works under XP.
I have narrowed the problem down to the ORDER BY statement in the query (I prefer to work in SQL). If I remove one of the sort criteria, everything works, except the results are displayed in the wrong order. It does not matter if I click the "run query" button on my form, or simply execute the query from the list of queries, the same error occurs. This is where I'm stuck.
Here is the chain. This query pulls data out of multiple tables and performs some point total calculations. This one is rather complex, but works fine. It produces a blob dataset of all entries for all events and sets points for each entry.
SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class, tblDogs.Dog_Name, tblDogs.ID, tblDogs.Owner, tblDogs.Sex, tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt, tblResults.Time, tblResults.Final_Wt, tblResults.Dist, tblResults.Place, IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus, IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus])))))) AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN tblResults ON tblDogs.ID=tblResults.Dog_ID) ON tblEvents.Event_ID=tblResults.Event_ID) ON tblClass.Weight=tblResults.Dog_Wt;
That resulting dataset feeds the next query, which also works fine. This one sums the points for each entrant by class for the season, only selecting entrants that have been in 3 or more events.
SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.Dog_Name, qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points FROM qryAll_Results INNER JOIN qryPulls_Entered ON qryAll_Results.ID=qryPulls_Entered.ID WHERE qryPulls_Entered.CountOfID>3 GROUP BY qryAll_Results.Class, qryAll_Results.Dog_Name, qryAll_Results.Owner, qryAll_Results.ID;
This last query is the one causing the headache. This one pulls the 10 entrants with the most points for a class, sorts them by points, then assigns 1st-10th place.
SELECT TOP 10 qryOverall_Totals_All_Classes.Dog_Name, qryOverall_Totals_All_Classes.Owner, qryOverall_Totals_All_Classes.Points, ((SELECT COUNT(*) FROM qryOverall_Totals_All_Classes AS T WHERE T.Class = "A" AND T.Points > qryOverall_Totals_All_Classes.Points)+1) AS Place FROM qryOverall_Totals_All_Classes WHERE (((qryOverall_Totals_All_Classes.Class)="A")) ORDER BY qryOverall_Totals_All_Classes.Class, qryOverall_Totals_All_Classes.Points DESC;
If you remove 'qryOverall_Totals_All_Classes.Points DESC' from the ORDER BY statement, the query runs. If you leave it in, it generates the error "Object invalid or no longer set". I've tried using the alias, but that didn't work either.
Any thoughts? I can post the database somewhere if someone needs to see the whole thing.
I have a query that when I run it normally (just click on it) then it runs fine. (It is a union query, getting it's data from 8 other queries (who has their dependancies)
But when I want to run a report from it, Access gives me an error saying "query is too complex".
I am flattered, but I would prefer access to work than say I write stuff that is too complex for it. :cool:
Any ideas?
I am confused by the fact that it runs when I double click the query, but the report bugs it out.
I have an append query that everytime it runs it will simply duplicate the records. ex.. sample table has 11 records if you run it again the tableB will now have 22 records. I need it to only update the records, so if someone makes in change / new record in tableA it will only update the records in TableB. I have the Primary Key set to Yes no duplicates. TableA Primary Key is ID TableB Primary Key is CustomerID
I have a form set up and would like to have field update to a table when a policy number is input into the form. The fields are extracted from a linked table and are not viewed on the form but need to be written to a table to create a report. I'm confused on the sets to take to handle this. I think i need to use the "onchange" property and set up a macro that runs a query but how does the query write to the table?
I have a query which I have connected to an excel worksheet. The query displays as a table in excel. Recently I've decided to reorganise the data that goes into the query, however the query itself hasn't changed, there's a chain of queries all feeding data into each other to arrive at this final query, and it's further up the chain that I did the reorganising.
Now when I try to open the workbook, I get this error (the workbook is set to refresh data whenever it is opened)The query did not run, or the database table could not be opened.Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganised, then try the operation again.
If I try to edit the data connection or create a new connection, the query I've been using also no longer appears in the list of queries and tables I can get data from.The problem is, this wasn't the sort of reorganisation that I could do incrementally and then see what specifically caused the connection to fail. The query was out of action altogether until I'd finished the reorganising. So it's a bit complicated and I don't know what kind of things could cause a query not to be available to make a connection with Excel.
I am able to see from the list of queries I can connect to where the first query in the chain comes that I can't connect to. The only thing about this particular query worth mentioning is that it uses the Nz() function (it adds together sales figures by month from a list of sales, and in months where there are no sales, there is no data, hence null, when logically I want it to be zero, hence the use of the function).
i'm trying to create a query of all employees doesn't have any transaction for a certain range of date and will also shows the last transaction date they have.i have two databases one is the transaction file and the other is the user file.
I'm receiving an error indicating there is a data type mismatch when running a query named qappInventoryTakeOn.
Data is entered into the Inventory Transaction Form. If the transaction type is "Take On", when the update button is clicked the record will be saved to tblInventoryMovements and then qappInventoryTakeOn should run to update tblInventory, but I keep running into the aforementioned error.
I want to run 10 big queries (append/update/delete) one after another. If error happens before they all finish (if user press escape button) - I want to discard all record changes made by any of those queryes. I suppose begin/end transaction method is the right way to do that. Can you give me an example of code?
I'm not sure what to search for to figure this one out. My searches, so far, haven't helped me much.
I'm tracking transactions in a clinic. The amount due for the visit is calculated fine, but sometimes people don't pay right away. (And sometimes pay ahead, which will just be a negative amount due.) I need my transaction to not only show what's due from today's visit, but also to look at previous transactions of the same client and calculate what may still be due from before. Additionally, if I pull up an OLD record, I need it to show what WAS due on that date, not what's due today.
So, I know I need to refer to dates prior to the date on the form, regardless whether it's today's date (for a new visit) or a previous date (for a previous visit). That's what I'm confused about.
How do I refer to [txtDate] on THIS form and not confuse Access with [txtDate] on earlier forms? Code like this: SUM[blahblahblah] WHERE [txtDate] < [txtDate] surely won't work. Does: SUM[blahblahblah] WHERE [txtDate] < [Me].[txtDate] work?
Should this kind of calculation be in a query or in the Control Source itself, or somewhere else?
And do I need the square brackets or not? Do I use '.' or '!'?
Hi, I don't want to abuse anybody's patience but I have yet a couple of other quick question about transaction table. I am using a summation query for the stock on hand where by keeping it simple: stock = sum of movement in the table. What hapens if a simple function is used to calculate the stock. ie: Stock=[Qty Declared-(Qty Declared-Qty Received)-Rejects]-Qty Allocated
All those Qty belong to the same field then I can't just run a sum.
My other concern is that: Qty Declared Qty Received Qty Rejected
are three values entered at the same time but in the same (transaction type) field so data like the date would need to be entered 3 times for each item received?
Thank you very much for your time and patience again,
I have a 16Mb DB. The intention is for users to access this over the network without copying onto their local machines.
At present, many of the forms are located on 1 master form and they are all subforms on tabs. There are a lot of calculated control boxes on the forms, and these seem to be very slow to bring back the data over the network whereas on my local machine, the data is displayed in a couple of seconds.
COuld this be sped up somehow? Maybe using queries or other methods?
I'm developing a database to handle various aspects of quality control reporting. I've got a working structure however, i think, i have gone off the track slightly... First I'll show you part of the structure:
(please see attached .jpg)
(hope that makes sence)... so effictively I have a many to many relationship.
I have forms for each of these entities (frm_parts, frm_Suppliers). The problem i have noticed is you can assign the part a supplier in frm_Parts but it does not appear in the transaction table as a record. Is this because i have based the field "Part Supplier" (a combo box) in frm_Parts on a query? If this is not the right way to go about it. how do i go about it?! I have an idea... on this.. If i create a new combo box on the form I can use the wizard to select the supplier ID from the Supplier table (i believe) and "store" it in the transaction table... i think! But I want the user to select the Supplier by drop down list showing Supplier Number and Supplier Name.. not the ID. Any help is much appriciated!
Next job...
I have a form where I would like the user to be able to enter, for example, a Supplier number and see if a record of the Supplier exists yet. Next I want the user to be able, assuming the Supplier record exists, to search for a record of a Part number from that supplier. Next I want them to be able to "select" that part and be able to open a form with a new record using that part number (this form holds details of a report rasied against that part). See what I'm getting at? I've had a go at this but it's been a couple of years since i did any detailed work on an Access database so somethings are a bit hazy! I'm not asking you to do this for me, but I would much appriciate some pointers on how to create this sort of thing!
I have these fields ID,InvoiceID,DateEntered,Description
What I need now is to show the most recent transaction per invoice and what was the description for it.
Doing this
SELECT InvoiceID, Max(DateEntered) AS MaxOfDate FROM TableName GROUP BY InvoiceID;
Works Perfect. The problem I have is when I display the Description field. As soon as I do that, I see all invoices and not just the most recent. Anyone know how to write this sql statement that will return the invoiceID, dateentered, and Description only for the most recent invoice in the system?
I want to create a simple data base, in which I have to record Payments received from each customer on daily basis. and on daily basis i want to generate a report for the transaction I have made. example of data table is as follow. payment mode is cash or bank transfer.how to generate reports for day to day transaction.
I have create my first program. When you click on the program it runs (I am an expert now!) But when I want it to run, I want it to hide the toolbars on the top so no one can alter it. Any suggestion?
I have a report with several subreports which run agains a very large table. If I decide to print the report after examining it, all of the underlying queries must run again. Is there any way around this to avoid this delay, other than exporting as rtf or snp?
I have 6 combo boxes on a form, which call a report based on a query. The idea is that the user can query data 6 different way. How do I/ or the user know which one is actually running first. Would the order left to right make a difference? (None have sort ascending or descending)
I own a kennel and was wondering if there is a way to put in 2 different dates and get totals of animals during that time and totals of kennel runs? Thanks.
Sorry, my reservation table includes Kind, New (y/n), Owner's Name, Pet's Name, Date In, Date out, Price, # of Units needed, and # of dogs. I need to put in dates ie: 5-10-06 to 5-25-06 and get a total of dogs and a total of units so we don't overbook. When I made this program I have little idea of how to do it and now years later I'm trying to tweak it. Thanks again
