Help! pls comment on my ADODB.connection

[lok1234]

Dear all,

Some of my users complains that if they have used my program for a long time (let say, 45 minutes) and do a lot of transactions, my program will have error messages prompted up and closed the program abnormally (sorry, they even have no time to capture the error messages). And what they have input even been "rollback". But actually, my program has already handled the transaction. (I have coded my program with begintrans, committrans and rollbacktrans if have error)

The background of my program is, I am using VB6 and Access database which store in a common share drive. frankly speaking, there are a lot of mass update, mass insert within a transaction in the program. (You may refer to one of my written function as belows)

I am not sure if there should have special handling for dealing a "big" transaction. Besides, I have a lot of recordsets for displaying data in the mshflexgrids at the same time. Again, I don't know if this has impact to the program.

The following codes are my general (or generic) codes for the ADODB connection. An example of my transaction is also included too.

Please comments for my codes if it has potential risk or can handle transaction better.. Many thanks.

'declare

Public gConnect_Access As ADODB.Connection

'Function for open Recordset or Table

Public Function f_OpenSQLrs(ByRef pRs As ADODB.Recordset, pSQLTable As String, _
                            pType As Integer, pConnection As ADODB.Connection, _
                            ByVal pCursorLocation, ByVal pCursorType, Optional ByVal pLocktype = adLockOptimistic) As Boolean
On Error GoTo Exception
    Set pRs = New ADODB.Recordset
    pRs.CursorLocation = pCursorLocation
    pRs.CursorType = pCursorType 'adOpenForwardOnly ' adOpenKeyset
    pRs.LockType = pLocktype 'adLockReadOnly
    pConnection.CommandTimeout = 3600
    pRs.Open pSQLTable, pConnection, , , pType
    f_OpenSQLrs = True

Exit Function
Exception:
    MsgBox Err.Description, vbCritical
    f_OpenSQLrs = False
    Exit Function
End Function

'Function for closing the recordset

Public Function fn_closeADOrs(pRs As ADODB.Recordset)
On Error GoTo Err
If Not pRs Is Nothing Then
    If pRs.State = 1 Then
        pRs.Close: Set pRs = Nothing
    End If
End If
Exit Function
Err:
    'MsgBox Err.Description, vbCritical
    Resume Next
    'Set pRs = Nothing
    Exit Function
End Function

'An example of my big transaction

Dim aStrsql As String
Dim aRs As New ADODB.Recordset
Dim aRs2 As New ADODB.Recordset

gConnect_Access.BeginTrans

str_SQL = " insert into TableA ..."
gConnect_Access.Execute str_SQL
                    
str_SQL = " insert into TableB ..."
gConnect_Access.Execute str_SQL

str_SQL = " select ... from TableC ...where ..."

If f_OpenSQLrs(aRs, str_SQL, adCmdText, gConnect_Access, adUseClient, adOpenKeyset) Then
      i = 0
      While Not aRs.EOF
            str_SQL = " insert into TableD ..."
            gConnect_Access.Execute str_SQL

            aRs.MoveNext
      Wend
      Call fn_closeADOrs(aRs)
Else
      GoTo Err
End If
                        
str_SQL = " select ... from TableE ...."

If f_OpenSQLrs(aRs, str_SQL, adCmdText, gConnect_Access, adUseClient, adOpenKeyset) Then
      i = 0
      While Not aRs.EOF
                str_SQL = " select ... from TableF ..."
 
                If f_OpenSQLrs(aRs2, str_SQL, adCmdText, gConnect_Access, adUseClient, adOpenKeyset) Then
                   While Not aRs2.EOF
                         .....
                         aRs2.MoveNext
                   Wend
                   Call fn_closeADOrs(aRs2)
                Else
                   MsgBox ("XXX cannot be found!"), vbCritical
                   'GoTo Err
                End If
              
                 str_SQL = " Update ..."
                 gConnect_Access.Execute str_SQL
          i = i + 1
          aRs.MoveNext
      Wend
      Call fn_closeADOrs(aRs)
Else
      GoTo Err
End If

  str_SQL = " insert into .."
  gConnect_Access.Execute str_SQL

  str_SQL = " select ..."

  If f_OpenSQLrs(aRs, str_SQL, adCmdText, gConnect_Access, adUseClient, adOpenKeyset) Then
        i = 0
        While Not aRs.EOF
              aRs!Field1 = ...
              aRs.Update
              i = i + 1
              aRs.MoveNext
        Wend
        Call fn_closeADOrs(aRs)
  Else
        GoTo Err
  End If

gConnect_Access.CommitTrans

Exit Function

Err:
  MsgBox Err.Description, vbCritical, Err.Source
  gConnect_Access.RollbackTrans
  Call fn_closeADOrs(aRs)
End Function

[dee-u]

Since the connection object is already declared as public then no need to pass it as a parameter to a procedure.

Access is really prone to problems specially when used in multi-user environment, you can try the other database systems which are more suited in a multi-user environment.

In order to optimize an Access database you should oftenly compact repair it. And also always make a back-up since it is prone to corruption.

EDIT:
Your fn_closeADOrs can be better written as:

Public Function fn_closeADOrs(rs As ADODB.Recordset)
    On Error GoTo Err
    If Not rs Is Nothing Then
        'first, check if the state is open, if yes then close it
        If (rs.State And adStateOpen) = adStateOpen Then
            rs.Close
        End If
        'set to nothing
        Set rs = Nothing
    End If
Exit Function
Err:
    MsgBox Err.Description, vbCritical
End Function

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

[FunkyDexter]

I know it's not specifically what you're asking but I would avoid using this:-
gConnect_Access.BeginTrans
like the plague in a multi-user system. If you begin a transaction from the client and then your program crashes (or the tea lady kicks the plug out) before you issue a commit or rollback this can leave open transactions against the database. That can lead to all sorts of problems like tables being left locked and other user being unable to work.

We set a rule, if we're going to open a transaction we do it in a Sproc (we using SQL Server) and we close it in the same sproc. That way we can be sure that transactions are never left floating (unless the tea lady kicks the plug out of the server, in which case we've got bigger problems).

[techgnome]

We set a rule, if we're going to open a transaction we do it in a Sproc (we using SQL Server) and we close it in the same sproc. That way we can be sure that transactions are never left floating (unless the tea lady kicks the plug out of the server, in which case we've got bigger problems).

I don't necessarily want to hijack the thread on this topic, but sometimes a transaction in the SP isn't enough... Normally I'd agree with your assessment Funky, but sometimes you have no choice but to initiate the transaction on the client and utilize a distributed transaction across multiple servers - a potential mess, yes, but that's where kevlar error handling comes in... it isn't bulletproof, but it certanly slows down the problems when they do arise.

OK, back on topic...

In any case, since we are talking about Access here, I agree... try not to use a transaction if you can help it. One thing though, there seems to be a lot of selecting and inserting into the same table going on... what does this process do? There might be a better way to perform the same action that isn't so.... heavy on the selects and inserts. and looping.

-tg

[lok1234]

I don't necessarily want to hijack the thread on this topic, but sometimes a transaction in the SP isn't enough... Normally I'd agree with your assessment Funky, but sometimes you have no choice but to initiate the transaction on the client and utilize a distributed transaction across multiple servers - a potential mess, yes, but that's where kevlar error handling comes in... it isn't bulletproof, but it certanly slows down the problems when they do arise.

OK, back on topic...

In any case, since we are talking about Access here, I agree... try not to use a transaction if you can help it. One thing though, there seems to be a lot of selecting and inserting into the same table going on... what does this process do? There might be a better way to perform the same action that isn't so.... heavy on the selects and inserts. and looping.

-tg

if not using begintrans, how to make sure the transaction integrity?
Since I have a lot of insert/update within a transaction, in case I remove the begintrans and it happen that the program "die" in the middle, the data will not be consistent then.

I also try to avoid so many update/insert tasks within a transaction, but it seems it is quite diffcult to avoid. Worst come to the worst, if I really have a lot of insert/update within a transaction, what is the best way for me to do? (assume I must use Access database).

Besides, after committrans, should I close anything else apart from the adodb.recordset?

[lok1234]

I know it's not specifically what you're asking but I would avoid using this:-
gConnect_Access.BeginTrans
like the plague in a multi-user system. If you begin a transaction from the client and then your program crashes (or the tea lady kicks the plug out) before you issue a commit or rollback this can leave open transactions against the database. That can lead to all sorts of problems like tables being left locked and other user being unable to work.

We set a rule, if we're going to open a transaction we do it in a Sproc (we using SQL Server) and we close it in the same sproc. That way we can be sure that transactions are never left floating (unless the tea lady kicks the plug out of the server, in which case we've got bigger problems).

sorry, my english is poor, what do u mean " tea lady kicks the plug out"?

i get ur idea about using begintrans in a stored procedure.. What do you suggest in case i just can use VB code to begin a transaction in Access database?

[techgnome]

that's why I asked what is it you are trying to accomplish here? it looks like you are selecting from a table.... then inserting that data right back into the database....

-tg

[FunkyDexter]

sorry, my english is poor, what do u mean " tea lady kicks the plug out"?

It was a joke. Don't worry about it. All it really meant was "If the computer crashes for some reason".

i get ur idea about using begintrans in a stored procedure.. What do you suggest in case i just can use VB code to begin a transaction in Access database?

I don't really use acces so I'm not sure but I think you can create "Queries" in Access that can contain several statements. That could be wrong though.

My best suggestion would be: Don't use Access. It's not really meant for multi user or complex systems. Instead I would suggest using SQLServer becaue then you can use Sprocs. If cost is an issue then use SQLServer Express which is free. To be honest, I can't think of any reason why anyone would continue to use Access when SQLServer Express is cheaper (free) and has more features.

The only other thing you could do would be to try and achieve everything you want with a single sql statement which is Techgnome was aiming at. For Example, this:-

str_SQL = " select ... from TableC ...where ..."

If f_OpenSQLrs(aRs, str_SQL, adCmdText, gConnect_Access, adUseClient, adOpenKeyset) Then
      i = 0
      While Not aRs.EOF
            str_SQL = " insert into TableD ..."
            gConnect_Access.Execute str_SQL

            aRs.MoveNext
      Wend
      Call fn_closeADOrs(aRs)
Else
      GoTo Err
End If

Could be rewritten as this:-

str_SQL = " Insert Into TableD (field1, field2,...) " & _
"select fieldA, fieldB ... from TableC ...where ..."
gConnect_Access.Execute str_SQL

There's no need for a transaction. On the other hand you may have some operations that just can't be reduced to a single statement.

[lok1234]

It was a joke. Don't worry about it. All it really meant was "If the computer crashes for some reason".

I don't really use acces so I'm not sure but I think you can create "Queries" in Access that can contain several statements. That could be wrong though.

My best suggestion would be: Don't use Access. It's not really meant for multi user or complex systems. Instead I would suggest using SQLServer becaue then you can use Sprocs. If cost is an issue then use SQLServer Express which is free. To be honest, I can't think of any reason why anyone would continue to use Access when SQLServer Express is cheaper (free) and has more features.

The only other thing you could do would be to try and achieve everything you want with a single sql statement which is Techgnome was aiming at. For Example, this:-

str_SQL = " select ... from TableC ...where ..."

If f_OpenSQLrs(aRs, str_SQL, adCmdText, gConnect_Access, adUseClient, adOpenKeyset) Then
      i = 0
      While Not aRs.EOF
            str_SQL = " insert into TableD ..."
            gConnect_Access.Execute str_SQL

            aRs.MoveNext
      Wend
      Call fn_closeADOrs(aRs)
Else
      GoTo Err
End If

Could be rewritten as this:-

str_SQL = " Insert Into TableD (field1, field2,...) " & _
"select fieldA, fieldB ... from TableC ...where ..."
gConnect_Access.Execute str_SQL

There's no need for a transaction. On the other hand you may have some operations that just can't be reduced to a single statement.

thanks I can only use Access because of company issue. My company does not allow me or our team to setup a SQLServer (even it is free). It is bank policy. I really hope I can write stored procedure which i think i am more familar with.

Anyway, I try to minimize the SQL statement. Any other comment?