Slow queries

[walduxas]

Hi guys,

I'm using update query to update values in table. However it takes about an hour to finish updating. I found that there is possibility to use this code to increase the speed of the query:

Sub PMUpdate() 
Dim dbs As DAO.Database 
Dim rsWk as DAO.Recordset 
Dim strSQL,i As String 
[B]Dim wksp As DAO.Workspace[/B]
 
 
Set dbs = CurrentDB() 
[B]Set wksp = DBEngine.Workspaces(0) ' set up a transaction buffer[/B]
[B]wksp.BeginTrans ' all record set changes are buffered after this[/B]
[B]On Error GoTo roll0[/B]
 
strSQL = "SELECT * from tblWk" 
Set rsWk = dbs.OpenRecordset(strSQL, dbOpenSnapshot) 
i = rsWK(0) ' Explict Reference to the only field in the table (first) 
 
strSQL = "UPDATE tblPMSales SET tblPMSales.i = tblPMInput.fldPMImportSalse " 
strSQL = strSQL & "WHERE tblPMSales.fldContract = tblPMInput.fldContract" 
 
DoCmd.RunSQL strSQL 
 
[B]wksp.CommitTrans [/B]
GoTo finish_it
roll0:
If Err.Number = 3022 Then
 
On Error GoTo roll0
Resume check_next
End If
MsgBox Err.Number & " " & Err.Description
[B]wksp.Rollback ' cancel everything if unexpected error[/B]
finish_it:
Set rsWK = Nothing 
Set dbs = Nothing 
End Sub

However, I don't know where should I write this code. Should I write this into SQL statement, or as some VBA code? Currently my update query looks like this (See attachement).

 

[namliam]

You are updating your Primary Key??? WHY?
This should
1) Not be needed
2) Not be possible
3) Not be desirable

An hour to update? Thats a loooong time, is this an access DB?
If so
Where is it located?
Is it beeing used/shared at the time of update?

 

[walduxas]

It's not a primary key. I simply named this column "Primary key" because I'm going to use it in most cases.

This is an access DB, but it's located in a remote hard drive. It's not shared.. well, at least I think it's not shared. This file is almost 700 MB of size, so because of storage limit I can't copy it to my PC, update it here and then copy it back to where it was.

 

[namliam]

The remove hard drive thing is definatly the problem, the code you have here wont help you I think...

Why use a compisit field that you need to update instead of just using this composition in a query?? Should be MUCH faster, more consequent and more maintainable...

Doesnt remove the glaring problem of "you shouldnt do this at all" just use the 3 seperate fields instead of the composit...

 

[walduxas]

Ok, the thing is that in the table there is no column which would contain unique data (this time primary key is not a solution). If I combine the values of three columns (Account_no, Currency_name and Customer_ic), then I receive a new value (in the column called Primary key) which is unique among all records. Having this unique record I can match this table with other tables (using relationships join parameters all-to-all, where joined fields from both tables are equal) and perform some other actions for instance a comparison etc.

I'm not very experienced in MS Access so I don't know other possible way to do that.

 

[DCrake]

It sounds like there has never been an comapct and repair done on this mdb. Because of the size of it Access may fail if you attempt it on the network. And as you say it is too big to copy onto your hard drive.

If you have no room on you local drive stick in a removable drive such as a memory stick (2 gig minimum) and copy it to that then do a C&R. If you cannot copy it across the network due to network resourses then use this applet to split the file into smaller segments and re assemble in the destination drive.

David