Connection Pooling with Microsoft Access?

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hello,

Is it possible to connection pool with Microsoft Access? I am coding in
VB.NET and my application is giving me errors because the connection is
still open and fetching records.

Thanks!
 
¤ Sure, here is the bit of code throwing the exception.
¤
¤ Try
¤ objCommand.CommandText = "INSERT INTO
¤ advancementHistory (agreementID, paymentReceivedDate, advanceAmount) VALUES
¤ (" & agreementID & ", #" & dtEditAdvanceReceived.Text & "#, '" &
¤ CorrectStrings(FormatNumber(CDbl(txtEditAdvance.Text), 2)) & "')"
¤ objCommand.ExecuteNonQuery()
¤ ''Initialize balance data
¤ Dim dblCurrentBalance As Double = 0
¤ objCommand.CommandText = "SELECT TOP 1 balanceID,
¤ currentBalance FROM currentBalance WHERE agreementID = " & agreementID
¤ objDR = objCommand.ExecuteReader
¤ If objDR.Read = True Then
¤ dblCurrentBalance = objDR.GetDecimal(1)
¤ ''Update current balance
¤ objCommand.CommandText = "UPDATE currentBalance
¤ SET currentBalance = '" & CorrectStrings(FormatNumber((dblCurrentBalance -
¤ CDbl(txtEditAdvance.Text)), 2)) & "' WHERE balanceID = " _
¤ & objDR.GetInt32(0)
¤ Else
¤ ''Insert record into current balance
¤ objCommand.CommandText = "INSERT INTO
¤ currentBalance (agreementID, currentBalance) VALUES (" & agreementID & ", '"
¤ & _
¤ CorrectStrings(FormatNumber((-1 *
¤ CDbl(txtEditAdvance.Text)), 2)) & "')"
¤ End If
¤ objDR.Close()
¤ objCommand.ExecuteNonQuery()
¤ Catch exp5 As Exception
¤ MessageBox.Show(exp5.Message, "Error Saving Advance
¤ Payment", MessageBoxButtons.OK, MessageBoxIcon.Error)
¤ myUpdater.DoError()
¤ bolSaveAborted = True
¤ End Try
¤
¤ The code works fine when I run if locally, but if we use the database over
¤ the network it gives the open/fetching error.


On which line does it fail and what is the error message?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Since I couldn't replicate the error on my development machine and I didn't
have time to try more extreme error trapping, I assume the error was caused
by resetting the CommandText while a DataReader was still open. I found
that if I set the CommandText to a string variable, close the DataReader,
the reset the Command Object's CommandText to the string, and finally
execute - everything works fine.
 
Back
Top