Access locks up on pass thru queries

[pghquest]

here is the situation, I'll try to be as detailed as possible

I have two tables in SQL 2008 R2, on windows 64 bit, with 40 gigs of ram, and 4x quad core processors.

Table 1, has about 60,000 recods in it
Table 2 has about 25,000,000 record

Both of these tables are in SQL, (database size about 60 gigs).

Im accessing them using Access 2007 links.

Its an update query to update the cost and quantity available
which looks like

UPDATE InventorySuppliers
SET BoydQuantityAvailable = Boyd0004Books.SumOfQOH, Cost = Boyd0004Books.Cost, BoydOldCost = InventorySuppliers.Cost, BoydAvailDate = { fn NOW() }
FROM Boyd0004Books INNER JOIN
InventorySuppliers ON Boyd0004Books.ISBN = InventorySuppliers.LocalSKU
WHERE (InventorySuppliers.SupplierID = 4) AND (NOT (InventorySuppliers.BoydQuantityAvailable = Boyd0004Books.SumOfQOH)) or (InventorySuppliers.SupplierID = 4) AND (NOT (InventorySuppliers.Cost = Boyd0004Books.Cost))

When these changes take place, then a trigger takes over and does some calculations on the fields.

My problems are as follows. The queries either
1) Runs completely, and then the Access database doesnt close out the query telling me its done, (and I know it finished because when I run the same query in SQL, it tells me no changes to be made). I heard there is a bug that causes this, but I cant find a solution.
or
2) The query takes forever, hours even.

If I shut the trigger off, and run the query in SQL, takes maybe 30 seconds, but shutting the trigger off isnt an answer.

My question is this, is there a way to have SQL not run the triggers until after I've made all of the updates, or is there a better ay to run the query to makeit more efficient? I've tried everything I can think of, including only updating the cost, and then the quantity, but that just makes the trigger run twice.. I've even tried running in Access 2010 and no difference.

Appreciate the input, I've been trying things non stop for days.

 

[pghquest]

Re: Access locks up on pass tru queries

One more question, (Bear with me, just want to try to summarize everything at once)..

I'm using a macro to run the command in VB to run the query, is it possible to run a query that looks and sees how many changes need made and then run loops of 100 through,until its done? If I do a top 100, it runs really fast.(like a second). so if I did say loops of 1,000, and then post on my form the loop that its on, I'll be able to see that its still running and not locked up.

Here is the section of the VB code.
' A1947-05C Update inventorySuppliers Cost Books
Forms("UpdateStatusForm").Controls("status6").Value = "Processing" <----- can make processing 1 of x etc..
Forms("UpdateStatusForm").Controls("status6").BackColor = 8454143
Forms("UpdateStatusForm").Repaint
DoCmd.OpenQuery "A1947-06A Update inventorySuppliers Cost Quantity available", acViewNormal, acEdit
Forms("UpdateStatusForm").Controls("status6").Value = "Complete"
Forms("UpdateStatusForm").Controls("status6").BackColor = 65280
Forms("UpdateStatusForm").Repaint

 

[MSAccessRookie]

Re: Access locks up on pass tru queries

You said:

"If I shut the trigger off, and run the query in SQL, takes maybe 30 seconds"

Let it take 30 seconds, and once the Query is done, start a new series of event(s) to do the update(s) performed by the current trigger.

Would something like this be possible for you?

-- Rookie

 

[pghquest]

Re: Access locks up on pass tru queries

You said:

"If I shut the trigger off, and run the query in SQL, takes maybe 30 seconds"

Let it take 30 seconds, and once the Query is done, start a new series of event(s) to do the update(s) performed by the current trigger.

Would something like this be possible for you?

-- Rookie

Well I need the triggers to run on all of the rows updated.. I did get close, if I break it up into small queries it runs pretty quick, so I'm thinking of creating a loop but when I do

Dim total as Integer
SQL = ("SELECT COUNT(*) as total FROM Boyd1947All INNER JOIN InventorySuppliers ON Boyd1947All.LocalSKU = InventorySuppliers.LocalSKU WHERE (InventorySuppliers.SupplierID = 1947) AND (NOT (InventorySuppliers.Cost = Boyd1947All.Cost)) OR (InventorySuppliers.SupplierID = 1947) AND (NOT (InventorySuppliers.BoydQuantityAvailable = Boyd1947All.Qty))")
MsgBox (total)

I get a result of 0 items, but when I run the select count on the server it shows about 10,000 items.

I'm thinking of just creating a loop updating 100 at a time until select count = 0

Any idea why the select isnt working in the access vb?

 

[pghquest]

Sorry it took a few days to get back to the thread, wanted to update it. I ended up getting someone to create a loop for me, to update 100 items at a time, and then display on the screen how many items were left to update. That way i can tell if it locked up, or just taking some time to do the update, and with 100 items at a time, it runs pretty smooth.

Thanks for the input MSAccess.

 

[pghquest]

Well I'm back.. problem continues to bug me now that the database has gotten bigger. Hoping people will have ideas..

If I run the queries all by themself, they run just fine, in both access, and SQL, only when I tell the VBA code to run the queries do they lock up..

VBA code looks like

DoCmd.SetWarnings False
' A0315-01A Add 0315Books into Inventory
Forms("UpdateStatusForm").Controls("status1").Value = "Processing 1 of x"
Forms("UpdateStatusForm").Controls("status1").BackColor = 8454143
Forms("UpdateStatusForm").Repaint
DoCmd.OpenQuery "A0315-01A BooksP not in Inventory", acViewNormal, acEdit

It'll lock up when running the query, but only through VBA..

Any ideas?

 

[SQL_Hell]

How does it run in Management studio? how long?

Can I see the code of "A0315-01A BooksP not in Inventory"

Is that really it's name? spaces in a query name?

What is this query? access query or sql server view?

 

[pghquest]

How does it run in Management studio? how long?

Can I see the code of "A0315-01A BooksP not in Inventory"

Is that really it's name? spaces in a query name?

What is this query? access query or sql server view?

If I double click the query itself in access, it might take a second to run. It runs just fine, its a SQL pass thru query..

It doesnt lock up all the time, just on occasion. I've got a series of these that run, it might run the first one, then lock up on the second one, or the third.. Here is what they all look like, the only difference is the letter, and the / 100

INSERT INTO Inventory
(LocalSKU, Barcode, ItemName, Weight, ActualWeight, RetailPrice, QOH, NoFulfillment, ReorderQuantity, ReorderPoint, Backordered, Category, DateEdited, EditType,
OptionFlag, AlturaDontUpload, UseSerialNumbers, CountryOfOrigin, LotQuantity, DropShip, Discontinued, SoldNotReceived, IgnoreInventory, Taxable, Kit,
PackSeparately, IgnoreSingleUnitSKU, NoLayaway, DontSell, IsABox, NoBoxNeeded)
SELECT TOP (100) Boyd0315All.EAN AS LocalSKU, Boyd0315All.EAN AS Barcode, 'x' AS ItemName, Boyd0315All.Weight / 100 AS Weight,
Boyd0315All.Weight / 100 AS ActualWeight, Boyd0315All.Price / 100 AS RetailPrice, '0' AS QOH, '0' AS NoFulfillment, '1' AS ReorderQuantity,
'0' AS ReorderPoint, '0' AS Backordered, 'Books - New' AS Category, { fn NOW() } AS DateEdited, 'A' AS EditType, '0' AS OptionFlag, '0' AS AlturaDontUpload,
'0' AS UseSerialNumbers, 'US' AS CountryOfOrigin, '0' AS LotQuantity, '0' AS DropShip, '0' AS Discontinued, '0' AS SoldNotReceived, '0' AS IgnoreInventory,
'0' AS Taxable, '0' AS Kit, '0' AS PackSeparately, '0' AS IgnoreSingleUnitSku, '0' AS NoLayaway, '0' AS DontSell, '0' AS IsABox, '0' AS NoBoxNeeded
FROM Boyd0315All
WHERE (Boyd0315All.ProductType = 'P') AND NOT EXISTS (SELECT * FROM Inventory WITH(NOLOCK) WHERE Inventory.LocalSKU = Boyd0315All.EAN)

Total results each, might be about 100 records,

Some changes since I originally posted, the inventory table now has about 40,000,000 records, and the Boyd0315All has about 11,000,000 records, but the query will run so fast if I run it outside of VBA, that its not a query issue, it has something to do with me running it in the VBA.

btw, I also bumped the ram on the server to 72 gigs, and Friday I'm having another 32 gigs delivered, so I'll have 104 gigs. The SQL database is about 90 gigs in size.

 

[cjman]

How about writing the Insert in SQL Server and just passing
Execute Name of SP to server. This is how I handle all of my SPs. The SPs that need parameters I use QueryDef method.

 

[pghquest]

I was looking at doing just that cjman, but I cant really find a website online to tell me how. I can write the queries into SQL, but I cant figure out how to save them, so I can call them from Access.

btw, the other 32 gigs of ram arrived last night, so I'm up to 104.

Performance boost elsewhere, but didnt solve this problem like I was hoping it would..

 

[cjman]

What I do is create a pass thru query
execute nameofSQLServerSP @StartDate = '01/01/2013', @EndDate = '01/31/2013' In design go to Property Sheet, ODBC Connect Str and hit the ... on the right side of the row to help build the connection string. Give the query a name. (You will need this name later). You can also set to return records or not to return records.
My next step is to use a form with two fields StartDate and EndDate.
In a command button for example use the following VBA code.
Dim db As Database
Dim q As QueryDef
Set db = currentDB()
Set q = db.Querydefs("NameofAccessQuery")
Q.SQL = "execute nameofSQLServerSP" & " " & "@StartDate=" & "'" & Form!Nameofyourform!StartDate & "'" & "," & "@EndDate=" & "'" & Forms!Nameofyourform!EndDate & "'"

When this code is run it changes the query to the dates you requested.
The next step is to run the query with the DoCmd.OpenQuery etc.

Hope this helps.

 

[mdlueck]

I outline here my suggestions for working with Access / SQL BE DB:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

Here is a straight-up example of executing a SQL INSERT via Stored Procedure:

Using VBA ADO objects to execute a Stored Procedure (INSERT)
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120