Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Performance of ADO compared to DAO

I recently want from an Access backend to a SQL Server so I rewrote a routine that was using DAO recordset methods to now use ADO recordset methods.  I was expecting a performance increase but did not get it.  The code took essentially the same time (2 Minutes) to manipulate thousands of records.  Below are some code segments for the ADO code.  How can I improve performance?
ADO Code------------------------------------------------------------------  
  Dim rstIBOM As New ADODB.Recordset    
  Dim rstIBOMDesignators As New ADODB.Recordset
  Dim dbBat As New Command
  Dim strQuery As String
&
  strQuery = "SELECT [Committed], [Asm_Serial_Num], [Lot_Num], [Inventory_ID], [Package_Num], Designator, ID FROM [Issued_BOM] INNER JOIN" & _
    " ([Issued_BOM_Parts] INNER JOIN [Issued_BOM_Designators] ON [Issued_BOM_Parts].[Issued_BOM_Parts_ID] =" & _
    " [Issued_BOM_Designators].[Issued_BOM_Parts_ID]) ON [Issued_BOM].[Issued_BOM_ID] = [Issued_BOM_Parts].[Issued_BOM_ID]" & _
    " WHERE [Issued_BOM].[Project_ID] =" & Forms![Project_Form]![txtProjectID] & _
    " AND [Issued_BOM].[Generic_BOM_ID] =" & Forms![Issue_Components_to_Asm]![txtBOMID] & _
    " AND [Component_Num] =" & "'" & Forms![Issue_Components_to_Asm]![Issue Components on Asm Subform].Form![txtComponentNum] & "'"
    dbBat.ActiveConnection = CurrentProject.Connection
    dbBat.CommandText = strQuery
    rstIBOM.Open dbBat, , adOpenForwardOnly, adLockOptimistic 
&
        strDesignators = "SELECT Lot_Num, Package_Num, Inventory_ID FROM Issued_BOM_Designators" & _
                         " WHERE Issued_BOM_Designators.ID =" & Me.txtID
        dbBat.ActiveConnection = CurrentProject.Connection
        dbBat.CommandText = strDesignators
        rstIBOMDesignators.Open dbBat, , adOpenForwardOnly, adLockReadOnly

Open in new window

0
schmir1
Asked:
schmir1
  • 16
  • 13
  • 4
  • +1
1 Solution
 
TheVeeeCommented:
The performance you will gain will be little if your not using the real ideal of indexes on your databases.  ADO is a mechanism for delivering records. Thats what cool about SQL Server and any other true database is now you can create indexes on each of the fields you commonly use and speed up your retrieval process via your SQL statements.

If I am you I would have indexes created on

Issued_BOM_Parts Table
Issued_BOM_Parts_ID

Issued_BOM_Designators
Issued_BOM_Parts_ID

Issued_BOM
Issued_BOM_ID

0
 
rockiroadsCommented:
DAO was optimised for Access database, ADO is probably better used for SQL Server. Now with ADO and SQL Server to maybe speed things up, have a look at using stored procedures and then specifying it to run on the server when it comes to updating and inserting data.

dbBat.ActiveConnection.CursorLocation = adUseServer will ensure it is run on the server. This should sometimes make it run faster.

With your select, indexing helps of course like TheVeee has said but also look at your filters, project_id, generic_bom_id and and component_num. Ensure indexes have been setup on them also.
0
 
TheVeeeCommented:
Rockiroads is definetly right on the use of stored procedures.  This will significantly speed stuff up.  Its kinda like when you go to the store to get a pizza, if you have to walk around the store, gather each item up, figure how much of the item,  it takes more time.  But if you buy a pre-made pizza, you just walk in grab it, check out and your gone.  Same principle with SQL and stored procedures.  With a stored procedure its like the pre-made pizza, the database already knows what you want and the fastest access points to get there.  With SQL its fast, but still need to find out what paths are available, and then make choices.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
rockiroadsCommented:
lol, nice analogy there TheVeee
0
 
Leigh PurvisDatabase DeveloperCommented:
I'd have to slightly take a different tack to those honourable online colleagues who have gone before me here.. :-)

Having gone from "Access backend to a SQL Server " you "rewrote a routine that was using DAO recordset methods to now use ADO recordset ".
However it is my contention that this is all you did.  A direct like for like conversion.
In other words - your question reads very much like you have an MDB (or ACCDB) application which you've made use of Linked Tables to a SQL BE?  (The direct conversion from a linked MDB backend).
That you were using DAO is further suggestion that this is the case (i.e. you're not using an ADP).
Your connection opening is an assignment of the current connection.
dbBat.ActiveConnection = CurrentProject.Connection
If this is still an Access MDB (or ACCDB) then that connection is one to a JET database engine.
i.e. you're not directly hitting the Server.  You're stil behaving exactly the same as you were through your DAO code.
ADO and DAO actually perform very analogously in many instances.  Against Jet data DAO ususally has the edge.  Jet engine linked through to a SQL BE isn't really any more capable of using ADO to take advantage of server optimisation.
Hence you would have to open a connection specifically to the SQL Server.
You could achieve this with just a connection string (ADO is very versatile in this respect)
dbBat.ActiveConnection = "Provider=SQLOLEDB;Source=YourServerName;Initial Catalog=YourDBName;Integrated Security=SSPI"
Though I prefer dedicated connection objects personally.  (Especially in your case when you open a subsequent command shortly thereafter - though you could perhaps just keep the same connection setting).
But the point is that you're then accessing the server directly without the round trip through Jet.
This should be faster.
However....
They won't necessarily be explosively faster.  You're still loading (however many) rows into memory.  It depends upon what you're doing with the data once loaded.  Navigating through recordset rows still takes the time it takes.
There are issues to consider - as pointed out.
Rocki's cursor location - however Server side is the default for Jet and SQL providers, you should be fine there already (there are times when a Client side is required - but generally it will be slower to load).  But other recordset considerations are that if your connection was to a Jet provider then your requested ForwardOnly cursor type will have been replaced with a Keyset (because the LockType wasn't ReadOnly).  A Direct SQL connection will support ForwardOnly and Optimistic options.

Veee's Indexes - absolutely  Always vital - regardless of the RDBMS you choose.
Rocki and Veee's Stored Procedure analogies.  Good and always worthwhile - but you shouldn't see blistingly different performance from a well prepared and formed SQL statement against a similar SP's results.  The optimisation and logic contained server-side in an SP is always worth having if it's there to be had.  But you can't expect it alone to make blinding differences.
Of course, if what you're doing with the recordset could be achieved by other means (nothing beats set based operations in a relational database ;-) then that would be more ideal and offer a genuine difference.  (Especially combined with an appropriately targetted Connection).
Anything not clear - just shout.  (Am in and out a bit - but generally about :-)
Cheers.
0
 
schmir1Author Commented:
Thanks.  I'm going to take so time to think about your comments.

Quick Question to LPurvis
Are you saying that if I change this:
  dbBat.ActiveConnection = CurrentProject.Connection
To this:
  dbBat.ActiveConnection = "Provider=SQLOLEDB;Source=YourServerName;Initial   catalog=YourDBName;Integrated Security=SSPI"
That I might see a performance increase.  I'm looking for a quick fix for now but will consider major changes for my next rev.

Note: Your are right.  I am using linked tables and I am using ODBC Data Source to define the connection.
0
 
Leigh PurvisDatabase DeveloperCommented:
Based on your last line there, I'd say that using the change as suggested will make your request more efficient.
But can't offer any guarantees about how much performance gain you'll see.
As I mentioned earlier though - I'd lean towards a dedicated connection object.
e.g.
    Dim cnn As New ADODB.Connection
    cnn.Open "Provider=SQLOLEDB;Source=YourServerName;Initial Catalog=YourDBName;Integrated Security=SSPI"
    '...and then using it whever required
    Set dbBat.ActiveConnection = cnn
    '... and closing and destroying it when finished if it makes sense to do so
    cnn.Close
    Set cnn = Nothing
0
 
rockiroadsCommented:
Howdo Leigh, I was wondering when you was going to turn up. This is of course your area of expertise. I always assume adp's are in use when using sql server. That should have the connection object already initialised? Its been a while since I last used an adp, in fact last used it in 2004!
Tried that krony in the tin, one from that bubble ad. Very smooth indeed!
0
 
Leigh PurvisDatabase DeveloperCommented:
(I'll be gutted if I ever have an area of expertise lol - Too greedy for that ;-)

Glad to hear the beer news.
I'm actually about to pop out for a bite (as it's my Birthday :-) and rest assured if they have Krony then I'll have at least one in your honour. ;-)
If not then might try the new system you've given the thumbs up.

Cheers!
0
 
TheVeeeCommented:
Ok Purvis your correct on the stored procedure world if your using Microsoft "Stuff".  I forgot all about the JET engine stuff since I been using real world stuff and migrated from DAO many, many moons ago. Cudos on that!

Now in the real world using real databases like Oracle and DB2, there is a significant increase in performance using stored procedures.  I could write a book on this but I have instead gave the following arcticle on Oracle which sums it up... http://www.dba-oracle.com/art_ioug_proc.htm.  For Microsoft "stuff" your are correct, Stored procedures do little or nothing for it connecting to SQL server, but hey.. its Microsoft... what did we expect?  

We actually used in my last job VB front end using ADO connecting to Db2 Mainframe.. yes Mainframe Cobol Stored procedure and the time was increased dramatically. Same with Oracle.  

The reason big companies use Oracle and DB2 pay big bucks for those products is for a reason... performance.  
0
 
rockiroadsCommented:
Happy Birthday fella, enjoy your evening :)

0
 
Leigh PurvisDatabase DeveloperCommented:
Cheers Rock.  It was canny (as we say up north).
Re SP's.  I wouldn't say that SP's in SQL Server are particularly dissappointing from most perspectives.  Perhaps compared to Oracle's persisted loading it's less dramatic, but ultimately an SP (clearly one which accepts parameters) is going to return a new result-set each time it's executed.  That data fetch and passing over the network can often be the lion's share of the time lag.  The loading of the SP code and the omtimised execution plan are where we can see performance gains - and these gains are present in SQL Server (to whatever extent each user feels they offer compared to other vendors) - I wouldn't want to seem to imply they aren't.  Just that there isn't a chasm between the two options.
In this case (2 minutes to iterate through a recordset) I don't perceive that the optimisation of the request is where the problem lies (most likely most of it lies in the simple overhead of navigation through the recordset struture in memory).
Hence the expectation that an SP isn't going to make a substantial difference.  But as I say, we can't be sure if anything will.
Cheers.
0
 
schmir1Author Commented:
Does it make any difference if I do:
Dim cnn As New ADODB.Connection
   cnn.Open "Provider=SQLOLEDB;Source=YourServerName;Initial Catalog=YourDBName;Integrated Security=SSPI"
   '...and then using it whever required
   Set dbBat.ActiveConnection = cnn

Or
dbBat.ActiveConnection = "Provider=SQLOLEDB;Source=YourServerName;Initial Catalog=YourDBName;Integrated Security=SSPI"

I'm just thinking that it would be easier to update the server name using the second way.  We will be changing servers when this db goes into production usage.
0
 
Leigh PurvisDatabase DeveloperCommented:
It doesn't matter in that the overall result will be the same (a connection will be created and used to execute a command against).
The difference is just that the former created the connection object explicitly and the latter implicitly when the command is executed.
The former is more controlled and re-usable (for example executing other commands throughout the procedure).
No performance difference should be perceivable if existing at all.

To my mind it's then more a question of modular coding and application maintenance.

Maintaining a persisted connection object is entirely possible too, indeed quite common and is something I've advocated on occasion (there will be posts with such examples around I imagine - if not here then on other forums).
You then don't have to worry about the connection at all.  It's always available to you.  Direct assignment to the command and go.

Related to that is your mentioning of switching servers.
I don't quite see why the latter would be any easier for updating the server name.  Indeed quite the contrary.  It's the same connection string.
The single persisted connection object would mean that changing server or database would be very simple indeed - equally maintaining a single constant connectionstring text that all such objects use would make switching very simple (names changed in a single location).  You can also build the string up and store the individual settings (such as server name, database name etc) in a local table and adapt those values at will.

All options avaialble to you.
Cheers.
0
 
schmir1Author Commented:
I'm getting  Invalid connection string attribute error on:
        dbBat.ActiveConnection = strActiveConnection
with strActiveConnection ="Provider=SQLOLEDB;Source=Mspm1bdbd54\a1;Initial Catalog=DTLData;Integrated Security=SSPI"

Any ideas?
0
 
Leigh PurvisDatabase DeveloperCommented:
Just a typo from early on.

strActiveConnection ="Provider=SQLOLEDB;Data Source=Mspm1bdbd54\a1;Initial Catalog=DTLData;Integrated Security=SSPI"
0
 
schmir1Author Commented:
Great, that works.  Now I'm going to try some benchmarks.
0
 
schmir1Author Commented:
Now I'm getting the following errors:
   Error Number -2147217864
   Optimistic concurrency check failed. The row was modified outside of this cursor.

Any ideas on what it means and how I can fix it?


0
 
Leigh PurvisDatabase DeveloperCommented:
With what code?  (And the error being raised upon which line?)
0
 
schmir1Author Commented:
Here is the code.  You can see where I commented out the recordset stuff to replace with the new code.
Private Sub cmdCompleteAll_Click()
  On Error GoTo Err_cmdCompleteAll_Click
  Dim rstIBOM As New ADODB.Recordset    'converted to ADO but didn't find any preformance improvement
  Dim rstIBOMDesignators As New ADODB.Recordset
  Dim dbBat As New Command
  Dim dbBat2 As New Command
  Dim strQuery As String
  Dim strComponentNum As String
  Dim lngFirstInvID As Long
  Dim strFirstLot As String
  Dim intFirstPack As Integer
  Dim bolQueryReady As Boolean
  Dim strQuery2 As String
  Dim intRowIndex As Integer
  Dim intdebug As Integer
  Dim bolFoundUncommitted As Boolean
  Dim strDesignators As String
  Dim strActiveConnection As String
 
  strSQLServer = "Mspm1bdbd54\a1"  'temp
  strDB = "DTLData"
  strActiveConnection = "Provider=SQLOLEDB;Data Source=" & strSQLServer & ";Initial Catalog=" & strDB & ";Integrated Security=SSPI"
  dbBat.ActiveConnection = strActiveConnection
  
  bolFoundUncommitted = False
  bolPreventMovingToAnotherRecord = False 'Assume save record works then lot is committed so OK to move to another record
  If Me.Dirty Then  'new way
    Me.Dirty = False    'saves record if needed
  End If
  
  strQuery = "SELECT [Committed], [Asm_Serial_Num], [Lot_Num], [Inventory_ID], [Package_Num], Designator, ID FROM [Issued_BOM] INNER JOIN" & _
    " ([Issued_BOM_Parts] INNER JOIN [Issued_BOM_Designators] ON [Issued_BOM_Parts].[Issued_BOM_Parts_ID] =" & _
    " [Issued_BOM_Designators].[Issued_BOM_Parts_ID]) ON [Issued_BOM].[Issued_BOM_ID] = [Issued_BOM_Parts].[Issued_BOM_ID]" & _
    " WHERE [Issued_BOM].[Project_ID] =" & Forms![Project_Form]![txtProjectID] & _
    " AND [Issued_BOM].[Generic_BOM_ID] =" & Forms![Issue_Components_to_Asm]![txtBOMID] & _
    " AND [Component_Num] =" & "'" & Forms![Issue_Components_to_Asm]![Issue Components on Asm Subform].Form![txtComponentNum] & "'"
  intRowIndex = 0
  strQuery2 = ""
  Do While intRowIndex < Forms![Issue_Components_to_Asm]![lstAssigned].ListCount
    If Not IsNull(Forms![Issue_Components_to_Asm]![lstAssigned].Column(0, intRowIndex)) Then
      intdebug = Forms![Issue_Components_to_Asm]![lstAssigned].Column(0, intRowIndex)
      strQuery2 = strQuery2 & " [Asm_Serial_Num] = " & Forms![Issue_Components_to_Asm]!lstAssigned.Column(0, intRowIndex)
      bolQueryReady = True
    Else
      bolQueryReady = False
    End If
    intRowIndex = intRowIndex + 1
    If intRowIndex < Forms![Issue_Components_to_Asm]![lstAssigned].ListCount Then
      strQuery2 = strQuery2 & " OR "
    End If
  Loop
  If bolQueryReady Then
'    dbBat.ActiveConnection = CurrentProject.Connection
  dbBat.ActiveConnection = strActiveConnection
    dbBat.CommandText = strQuery
    rstIBOM.Open dbBat, , adOpenForwardOnly, adLockOptimistic  'need adOpenStatic for recordcount to work
'    Set rstIBOM = CurrentDb.OpenRecordset(strQuery, dbOpenDynaset, dbSeeChanges)
    rstIBOM.Filter = strQuery2
'    dbBat.ActiveConnection = CurrentProject.Connection
  dbBat.ActiveConnection = strActiveConnection
    dbBat.CommandText = strQuery2
 
    With rstIBOM            'All the serial numbers in the list
      If Not .EOF Then
'        .MoveLast
'        .MoveFirst
        strDesignators = "SELECT Lot_Num, Package_Num, Inventory_ID FROM Issued_BOM_Designators" & _
                         " WHERE Issued_BOM_Designators.ID =" & Me.txtID
'        dbBat.ActiveConnection = CurrentProject.Connection
        dbBat2.ActiveConnection = strActiveConnection
        dbBat2.CommandText = strDesignators
        rstIBOMDesignators.Open dbBat2, , adOpenForwardOnly, adLockReadOnly  'need adOpenStatic for recordcount to work
'        Set rstIBOMDesignators = CurrentDb.OpenRecordset(strDesignators, dbOpenSnapshot)
        If rstIBOMDesignators.EOF Or IsNull(rstIBOMDesignators![Lot_Num]) Then    'was using rstIBOMSerialNums but didn't work
        ' Note: May see this message if sort gets messed up.  Tried to force sort but didn't work (Issue1) and (Issue2)
          MsgBox "You need to pick the lot for the first designator then press this button to complete all the rest" _
                 , vbInformation, "User Notice (BSS-23)"
          GoTo Exit_cmdCompleteAll_Click
        End If
        
        If EnoughComponents(.RecordCount + Nz(Me.txtQtyScrapped)) = False Then
          GoTo Exit_cmdCompleteAll_Click
        End If
        
        lngFirstInvID = rstIBOMDesignators![Inventory_ID]       'was using rstIBOMSerialNums 4/10/09
        strFirstLot = rstIBOMDesignators![Lot_Num]              'was using rstIBOMSerialNums
        
        If MsgBox("All designators will be committed to the lot number of the first designator (" & strFirstLot & ")" _
                  , vbOKCancel, "User Input (BSS-29)") = vbCancel Then
          GoTo Exit_cmdCompleteAll_Click
        End If
        
        Do Until .EOF  'Do for each component #
          If ![Committed] = False Then
            bolFoundUncommitted = True
            'Setup_Designators_All checks committed flag for each SN,checks then decrements inventory,generate components/scrap records
            If Setup_Designators_All(True, !Designator) = True Then
              Call Set_Committed(Me!Designator)
              Call SetEnables
            Else
              Call ListButtonEnables(True)
            End If
          End If
          If Not .EOF Then
            If ![Committed] = False Or IsNull(!Lot_Num) Then   'Added "Or IsNull(!LotNum)" just in case the lot_num didn't get saved 4/14/09
              ![Inventory_ID] = lngFirstInvID
              ![Lot_Num] = strFirstLot
              ![Package_Num] = rstIBOMDesignators![Package_Num]  'use null if pack is null (not 1) 4/14/09
              .Update
            End If
          End If
          .MoveNext
        Loop
      End If
    End With
  End If
  If bolFoundUncommitted = True Then  'set the all committed checkbox
    Parent.chkCommitted = True
  Else
    MsgBox "All Designators have already been committed", vbInformation, "User Notice (BSS-24)"
  End If
  Me.Refresh
Exit_cmdCompleteAll_Click:
  On Error Resume Next
  rstIBOM.Close
  rstIBOMDesignators.Close
  Exit Sub
Err_cmdCompleteAll_Click:
    LogEvt "Error Number " & Err.Number & vbNewLine & Err.Description, vbCritical, "cmdCompleteAll_Click Error (BSS-28)"
    Resume Exit_cmdCompleteAll_Click
End Sub

Open in new window

0
 
schmir1Author Commented:
I put a comment in where the error occurs:
            If ![Committed] = False Or IsNull(!Lot_Num) Then   
              ![Inventory_ID] = lngFirstInvID
              ![Lot_Num] = strFirstLot
              ![Package_Num] = rstIBOMDesignators![Package_Num] 
              .Update  'Error: a row was outside this cursor
            End If
          End If
          .MoveNext

Open in new window

0
 
Leigh PurvisDatabase DeveloperCommented:
Interesting.  (Although there's no screamingly obvious reason as to why - but then there's too much code for me to examine carefully).
The first port of call when having a cursor problem would be to change the recordset type.
adOpenForwardOnly is a limited cursor so move to adOpenKeyset.
(The other alternative is to increase the cache-size of the cursor.  But personally I'd go with Keyset first.)

See how you go.
Cheers.
0
 
schmir1Author Commented:
Changing to adOpenKeyset didn't fix the problem.  I did notice that I get the Optimistic concurrency check failed error then if I run the code on the data again, no error?
0
 
Leigh PurvisDatabase DeveloperCommented:
That's certainly interesting and perhaps relevant.
To be honest - the code leading up to there looks a bit hectic.  
I personally wouldn't bother with the command objects (there are no parameters being passed/retrieved).
I would create and maintain a single connection object.  Using it directly to open recordsets.
As a test - try updating a single recordset (rather than have two open).
A simple single recordset.  I actually see that your updated recordset is one based on several tables joined?
I have to ask a) why and b) is that necessary?
0
 
schmir1Author Commented:
I not very familiar with ADO objects so I don't know what you mean by command objects.  What do I need to change?

Your comment on a single connection:  Do you mean use this for all recordsets?
  strActiveConnection = "Provider=SQLOLEDB;Data Source=" & strSQLServer & ";Initial Catalog=" & strDB & ";Integrated Security=SSPI"
  dbBat.ActiveConnection = strActiveConnection
I was doing that but changed it because of the error

The way that I update one recordset based on another is a way that works.  Is there a better way?
0
 
Leigh PurvisDatabase DeveloperCommented:
I was suggesting doing away with the command objects for simplicitly (they're in your declarations and used extensively).
i.e. right above - dbBat.

So your first lot of declarations would be:

  Dim rstIBOM As New ADODB.Recordset
  Dim rstIBOMDesignators As New ADODB.Recordset
  Dim cnn As New ADODB.Connection
And then later on you'd have
  strActiveConnection = "Provider=SQLOLEDB;Data Source=" & strSQLServer & ";Initial Catalog=" & strDB & ";Integrated Security=SSPI"
  cnn.Open strActiveConnection

You'd then have no reference to dbBat and dbBat2.
Instead you'd open your recordsets as:
  rst.Open strQuery, cnn, adOpenKeyset, adLockOptimistic  
But my other question was does the first recordset (based on strQuery) really need to be a combination of joining three tables?
(Considering it's being opened for updates - is there not just one of those tables that requires updating?)
The dedicated connection object is exactly what I was expounding the virtues of earlier.
0
 
schmir1Author Commented:
Thanks for laying it out so well.  I had another incompatability with my code and SQL Server so I rewrote the sub using your approach.  I get the following error:
   Run-time error '3219': Operation is not allowed in this context.

Note: The code that I commented out is my old code that worked with Access back-end.

Private Sub DeleteInvRecord()
'Delete the Inventory record for lot # and package # if User deletes the Receiving record
'  Dim rstInv As Recordset
'  Dim strSQLDelete As String
  Dim rstInv As New ADODB.Record
  Dim cnn As New ADODB.Connection
  Dim strActiveConnection As String
  Dim strSQLServer As String
  Dim strDB As String
  Dim strQuery As String
  
'  strSQLDelete = "DELETE * FROM INVENTORY" & _
'                 " WHERE [Lot_Num] = " & "'" & sLotPack(i).strDeletedLot & "'" & _
'                 " AND [Package_Num] = " & sLotPack(i).intDeletedPack
'  CurrentDb.Execute strSQLDelete
  strSQLServer = "Mspm1bdbd54\a1"  'temp, if works will get server and db from dbConfig table TBD
  strDB = "DTLData"
  strActiveConnection = "Provider=SQLOLEDB;Data Source=" & strSQLServer & ";Initial Catalog=" & strDB & ";Integrated Security=SSPI"
  cnn.Open strActiveConnection
  strQuery = "SELECT Lot_Num FROM INVENTRY" & _
                 " WHERE [Lot_Num] = " & "'" & sLotPack(i).strDeletedLot & "'" & _
                 " AND [Package_Num] = " & sLotPack(i).intDeletedPack
  rstInv.Open strQuery, cnn, adOpenKeyset, adLockOptimistic
  With rstInv
    If Not .EOF Then
      .DeleteRecord
    End If
  End With
End Sub

Open in new window

0
 
schmir1Author Commented:
I'm going to open another question for the last implementation issue that I'm having since it involves deleting records which is different then the original question.
0
 
schmir1Author Commented:
Back to the original question.  Changed the code as your suggested but still get the same error at the same point.

Below is a code snippet:
Private Sub cmdCompleteAll_Click()
'Commit all designators (decrement inventory & generate Issued_Components record)
  Dim rstIBOM As New ADODB.Recordset    'converted to ADO but didn't find any performance improvement
  Dim rstIBOMDesignators As New ADODB.Recordset
  Dim cnn As New ADODB.Connection
  Dim strQuery As String
  Dim strSQLServer As String
  Dim strDB As String 
&
  strSQLServer = "Mspm1bdbd54\a1"  'temp, if works will get server and db from dbConfig table TBD
  strDB = "DTLData"
  strActiveConnection = "Provider=SQLOLEDB;Data Source=" & strSQLServer & ";Initial Catalog=" & strDB & ";Integrated Security=SSPI"
  cnn.Open strActiveConnection
&  
    rstIBOM.Open strQuery, cnn, adOpenKeyset, adLockOptimistic 
    rstIBOM.Filter = strQuery2 
&
        strDesignators = "SELECT Lot_Num, Package_Num, Inventory_ID FROM Issued_BOM_Designators" & _
                         " WHERE Issued_BOM_Designators.ID =" & Me.txtID
        rstIBOMDesignators.Open strQuery, cnn, adOpenKeyset, adLockOptimistic 
...
          If Not .EOF Then
            If ![Committed] = False Or IsNull(!Lot_Num) Then  
              ![Inventory_ID] = lngFirstInvID
              ![Lot_Num] = strFirstLot
              ![Package_Num] = rstIBOMDesignators![Package_Num] 
              .Update  'Error: Optimistic concurrency check failed. The row was modified outside of this cursor.
            End If
          End If
&
End Sub

Open in new window

0
 
Leigh PurvisDatabase DeveloperCommented:
You've removed the assignment of the SQL string to strQuery presumably - but it's in the code you're actually executing?
What is its defintion?  Still the three tables?  Do you really need them?  The three fields you update are one from each table?  Seems very unlikely.  

(Obviously this would all be easier if I could reproduce the error - but I've never had it).
0
 
schmir1Author Commented:
Below are the queries that I use.  I can post the whole sub again but it's like the above code (the original cmdCompleteAll_Click that I posted) except with the SQL connnection changes that you recommended.  

strQuery uses data from all 3 tables so I don't know how to simplify that.  I do this quite commonly throughtout my db.  Maybe I don't understand the correct way to do it.
  Committed is in Issued_BOM_Designators table
  Asm_Serial_Num is in Issued_BOM table
  Lot_Num, Inventory_ID, and Package_Num are in Issued_BOM_Designators table
  Where clause used data in Issued_BOM and Issued_BOM_Parts tables

Does this make sense or am I completely wrong?
I can post the entire cmdCompleteAll_Click again if that helps?


'Sets up rstIBOM
  strQuery = "SELECT [Committed], [Asm_Serial_Num], [Lot_Num], [Inventory_ID], [Package_Num], Designator, ID FROM [Issued_BOM] INNER JOIN" & _
    " ([Issued_BOM_Parts] INNER JOIN [Issued_BOM_Designators] ON [Issued_BOM_Parts].[Issued_BOM_Parts_ID] =" & _
    " [Issued_BOM_Designators].[Issued_BOM_Parts_ID]) ON [Issued_BOM].[Issued_BOM_ID] = [Issued_BOM_Parts].[Issued_BOM_ID]" & _
    " WHERE [Issued_BOM].[Project_ID] =" & Forms![Project_Form]![txtProjectID] & _
    " AND [Issued_BOM].[Generic_BOM_ID] =" & Forms![Issue_Components_to_Asm]![txtBOMID] & _
    " AND [Component_Num] =" & "'" & Forms![Issue_Components_to_Asm]![Issue Components on Asm Subform].Form![txtComponentNum] & "'"
...
'Adds s/ns for rstIBOM
      strQuery2 = strQuery2 & " [Asm_Serial_Num] = " & Forms![Issue_Components_to_Asm]!lstAssigned.Column(0, intRowIndex) 'add s/n
      strQuery2 = strQuery2 & " OR " 'pre for next s/n
...
Sets up rstIBOMDesignators
        strDesignators = "SELECT Lot_Num, Package_Num, Inventory_ID FROM Issued_BOM_Designators" & _
                         " WHERE Issued_BOM_Designators.ID =" & Me.txtID

Open in new window

0
 
Leigh PurvisDatabase DeveloperCommented:
I've never had the error you're encountering.
Searching on it doesn't return anything revealing (or relevant anyway) - so it's not particularly common.
Hence I'm speculating.  My thinking is that you have two recordsets open - both pointing to the same table, however in one it is joined the two other tables and being updated.
My question is - are you UPDATING all three tables in that join?
Have you tested not updating them all - but only one.  As a test.
When bebugging it's important to stop trying to fix everything in one fell swoop and ultimately modularise the processes.  Simplifying until it works and and then adding complicating it until it breaks.
 So ye s- you want data from all three tables.  But do you really update all three?  If not then test just updating the one that's required.
To my mind - if you're updating more than one then there's likely something wrong with the database design.

All that said it could be anything.  A bug, compatability mode, driver problems, install problems....
0
 
schmir1Author Commented:
>Hence I'm speculating.  My thinking is that you have two recordsets open - both pointing to the same table, >however in one it is joined the two other tables and being updated.
You are right.  I am only updating the Issued_BOM_Designators table but I'm doing it through the recordset that joins all three.  Both recordsets are pointing at the Issued_BOM_Designators table.

So I tried saving the data from the second recordset then closing it before I updated the first recordset.  Same error.

I think at this point I should give up and go back to the old code that worked.  This was just an experiment to find the optimal approach.  I can try your method in other area of my code when I get to it.  I do like connecting directly to the server and not through a DSN connection.  Sounds more efficient.  

So unless you have other suggestions, I will close this question.  We spent a lot of time on this and it's too bad that it didn't work but that happens sometimes.  Thanks so much for all your help.

0
 
Leigh PurvisDatabase DeveloperCommented:
It's one of those things.  Sat infront of the PC - free to try one thing after another, throwing everything at it, it's extremely likely to get solved.
Remote diagnosing can only achieve so much.  (It's difficult to convey what needs to be attempted expediently).
FWIW the technique as listed of data manipulation is sound.  It works.  (For countless thousands of applications).
In your particular situation something's preventing it.
Also - it's important to note that the goal is not to avoid a "a DSN connection".
There's actually no such thing.
A DSN is simply a repository of settings which allow an ODBC connection to be established.  (And ADO is perfectly capable of using ODBC instead).  An ODBC connection doesn't need a DSN - it's a convenience.
The ADO connections you've been establishing are using OLEDB providers - that's where the difference lies... ODBC Vs OLEDB.
0
 
schmir1Author Commented:
Thanks again for you help.  Looks like I've got a lot more reading to do.
0
 
schmir1Author Commented:
Always a pleasure to hear from you.  You have a excellent grasp of the subject.  I need to work in that direction.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 16
  • 13
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now