MS Access Runs Slowly For Client PC's After A Update Or Insert.
Jan 29, 2007
Hi,
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 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?
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 an asp.net application where the user can add a new client name and password to a database, but I want to do a check to make sure that client name is not already in there. I was working with and INSERT INTO statement this here:
INSERT INTO [clients] ([clientName], [passWord]) VALUES (@clientName, @passWord) WHERE NOT EXISTS (SELECT [clients].[clientName] FROM [clients] WHERE [clients].[clientName] = clientName)
But I guess you can't use a WHERE clause with an INSERT statement.. So I am trying to use an UPDATE statement. This here:
UPDATE clients SET clientName = @clientName, passWord = @passWord WHERE NOT EXISTS (SELECT [clients].[clientName] FROM [clients] WHERE [clients].[clientName] = @test)
When I run it in access, it doesnt add a new row if I try to add a client name that is already in there, but if I try to add one that isn't it tells me it's going to UPDATE 13 rows which would be all the rows in there. Anybody have any ideas how I can do this?
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.
My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.
I need to update a table at a client's site to change a couple of fields, and would like to be able to send them something that they can run on their end to perform an insert query.
I cant update the tables via RDP etc. because the database needs to be in Access '97 format, and they all run access 2003.
I also cannot get them to send me the file for updates since it needs to be online basically 24/7.
Brand new on here and desparate for some help and guidence.
So far with Access I have just used it as a store of addresses to mailshot prospective clients.
However, I now need a more complex database and this is where you might be able to help.
First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.
Does this mean I need to do three tables:
1/ "University Details" which gives the address details 2/ "Department" storing the departments of the unis we work for 3/ "Client" Name of the client(s) in that department.
My Combo Box is tied to a table with many entries. When I click on it to display the table values it takes several seconds to display. Is there any way I can "pre-load" the Combo Box (such as on form load, etc.) so when I click on it it displays more quickly? Or is there another way to speed it up?
I've made an adp (access project ) file. I want to permit some users to look in the data but i'm not certain they have access installed, can i give them read-only rights by placing a data-access page in a network map? Can they use the functionality of a data-access page without having access installed?
This post is really aimed at someone that has experience of network databases and the problems they can pose but if you can help at all that would be great!
I work for an insurance company and we have created a database for registering complaints on. The one we currently use is a single .mdb file which can be shared by up to 60 people at any one time. This is creating a lot of problems when the queries and some VBA code are run. I therefore made a server file which is just an mdb file with data tables in and NO forms or queries. This file is stored on a shared drive on the network server. We then created a client file which contains all of the forms and queries and code. This file contains linked tables to the server so when data is entered into the form, it "Sends" it to the server mdb file. The client is installed on each of the local machines c:. The problem with this was that if we made a change to the database, we would need to reinstall the client on every single user pc. This would of took ages. I therefore made an auto-update function that checked the version number on the server and if the server number was greater than the client number, a simple .bat fiile was run which copied the updated client file from the network to the relevant users local disk. The problem we have now is that our IT department are concered that if we make a change to the client and all the staff log in at 9am for example, it will start doing multiple copying of a file around 8mb in size to around 60 machines. They are only running on a 2meg pipe so this could cause some problems. We are not looking to change the client & server idea but does anyone know if this will have a big impact and infact if the updates for 60 machine is the equivalent or lesser of 60 people sharing the single file i mentioned earlier. If the IT dept are happy with 60 users accessing the same file at the same time, which they currently are, why are they unhappy with it updating these machine using the new method.
Would really apopreciate any thoughts anyone has..
I have completed a database for a company with 60 000 clients and over 100 000 job records.
The database works at very resonable speeds on the Server computer, or the computer on which I installed the back end of the database.
Each of the other 3 computers on the wireless network, have a local copy of the Front End on their machine, and reference the Back End (BE) on the server computer.
Each of the Client computers have varing speeds when accessing the BE, some as slow as 10 minutes for a simple search, filter or just loading a form.
What can I do to improve performance across the network?
Do I need to install additional components on the other computers to improve the db performance?
I have developed .net application ....at back end I am using ms access database. When I deploy my application at client end , I want only my ms access database should accessible to application only. Client should not be even to see which type of database i am using or its business logic .
I'm not sure if Access is the right program to use for this scenario as I only know the basics on creating the database.
I want to be able to create a client database and then be able to generate a number of standard template letters at any particular time for selected clients.
I don't want a mail merge!!
Can access be used for this?
Ideally I would like to create a form for administrators to use to input new client info and update existing clients info and then just be able to click a button (or the like) to generate the standardized letter for a particular scenario.
I have an acces form to input client orders. Each order has its own order number a different client. On the same form I have a button to generate a report based on the inpuuted data on the same form which works perfectly fine.
Now I need to use same report to save each order in a specific folder on my computer in pdf format. However I would like to have the file generated as follows ordernumber "-" clientname.pdf. This way each order is saved in the same folder without overwriting the previous one.
I need some help to figure this out.. What I am trying to do is, when I click the "Save" button on the form I also want to save some of the fields into another table.
I want to open the other table and search of the key field (command ?) If found then ----run update sql statement else ----run insert statement end if close the table
I am looking for the command statements... (i have the insert and update sql statements ready)
Humm, been going over this and can't figure where I am going wrong. Linked SQL server table. SQL permissions are correct (cause every thing works via QA using pass through security). Form with a CBO to select Item1, once selected list box populates with Items tied to it. Select item from list box click button to break the tie between the two. 3 tables, Item1 Table, Item2 table and cross reference table that ties them together. The Break Tie basically (should) remove the entry from the cross ref. table. Form allows me to insert a new tie (same premis as break only allows you to select items not tied currently) and will insert a row into the cross ref. table. But it will not allow me to delete, or as a backup plan I tried to zero out the FK values. Delete says not allowed (bad permissions, table read only, etc.) and Update says must use an updatable query. NOW the queries I have used are basically DELETE FROM CrossRef Where PK = nnnn OR UPDATE CrossRef SET FK1 = 0, FK2 = 0 WHERE PK = nnnn I mean it does not get any easier than that. So what am I missing on this? Why won't it let me delete/update that stupid table?
Im trying to do an insert an an update in the same function,but it only allow an upate only if a record exist. Here is my code: Sub insert(ByVal UserSelection, ByVal Grand_Prix_ID)
'The date function 'IF the 2 or more days left before the race then ' Do the insert CompareDates(User) Dim mysql As String Dim strConn As String Dim MUser_ID = Request.QueryString("UserID") Dim Nickname = Request.QueryString("name") Dim LastGP_ID As Integer = Grand_Prix_ID - 1 strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Server.MapPath("App_DataFantasyF1.mdb") & ";"
'else if already selected before do the update Else MsgBox("You have made the selection before and You are about to update", MsgBoxStyle.YesNo) mysql = "UPDATE TeamSelection SET Grand_Prix_ID = @Grand_Prix_ID, User_ID = @User_ID, Driver_ID1 = @DRIVER_ID1, Driver_ID2 = @DRIVER_ID2, Driver_ID3 = @DRIVER_ID3, Driver_ID4 = @DRIVER_ID4, Driver_ID5 = @DRIVER_ID5, Driver_ID6 = @DRIVER_ID6" & "" mysql = mysql & " WHERE User_ID =" & MUser_ID mysql = mysql & " AND Grand_Prix_ID =" & UserSelection(7) & ""
Dim Myconn As New OleDbConnection(strConn) Dim objComm As New OleDbCommand(mysql, Myconn) Myconn.Open()
Hey guys- I have a 'Master Table' that holds all my imported records. After a few queries and whatnot- I need to break it down into specialized tables. For example- my Sales Associate info goes into one table (all their contact info, employee code, etc)- while the product info goes into another table.
As I import daily orders and whatnot- it also brings in the sales associates info. So, I want to have Access check the existing SalesEmployee Table for any existing records (by their employee code)- and if it doesn't exist, append it into the table. HOWEVER- if they are already showing in the SalesEmployee Table, I want it to check to see if their contact info is the same- if not, update it with the new info I am importing.
How do I go about doing this? Is this an update query all by itself? Or, do I need a more complex if/then statements and whatnot? Thanks!
I am running a query which uppends the record to one table from another table. My requirement is that, it should check one field of the destination table table before inserting the record. If that field matches, it should run Update query or else it should run Insert Query. How Can I do that?
Is it possible to have an insert and update in the same sql statement using MS Access 2002? I could split it into seperate statements but for code purity I would like to have it in one :cool:
Queries are run on a webpage: The queries would be a little bit different – instead of just one operator, it would get all operators for each category and then either create a record in Access (if it didn’t already exist) or update a record (if it already existed). For example, the first query might get
JSMITH 22 KWALTON 33 Since these records don’t exist yet, we’d do an insert for each. If the next query (for a different entry/verify category) got
JSMITH 44 VJONES 50 we would update JSMITH (since already inserted after first query) and insert VJONES. This has to be done since not everyone works in every category. Not sure if you can import different spreadsheets into Access and have it determine automatically for each row whether to do an insert or update (of course, we can do this in code in the web page).
I was wondering if someone could help? I am using Access 2002 and I am struggling to find out out how you can insert/update/delete records through a form using the design view. Is this possible or do you need to do this another way?
Could some one point me in the direction of a comprehensive tutorial or outline some instructions for what I need to do?
I need to create a form that inserts people's details into a table
When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.
I need to create a form to Add/Update into SMaster and SI_map tables, which has one-to-many relationship, that's why I use a list box to show the values from SI_map.
in SMaster [ Sid, other fields ] in SI_map [ primary key, Sid, ILookupid ] in ILookup [ ILookupid, IName ]
questions: 1. How to get the multi-selected values from the list box? 2. insert these multiple records into SI_map table at the same time with SMaster (if I use the same form to populate all these fields) 3. how to populate the records into form for user to see and update the values?
Any suggestion is appreciated, it is very flexible to change any format such as SMaster and SI_map can be separate forms, as long as it works.
