Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2004
    Posts
    156

    Unanswered: Unbound Forms & ADO

    Hello all!

    I designed a database with unbound forms using ADO to add records. The back-end is located on a network server and the front-end is on each PC. There is also user-level security in place.

    When they first started using the database, there were no problems in entering information. The database has been in use for a little over a month now, and it's starting to crash on them after entering so many records (one estimate was 8 - 10 records) at a time. It'll give them an error, crash and then they have to reboot before they can log back in to the database through Access security. As soon as they reboot, the process happens all over again. At first, I thought this was just localized to a certain form/table. However, it's now doing it on two unrelated forms/tables.

    Does anyone have any knowledge of issues that may be causing this? I'm in a fix! Help if you can. Thanks!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DocX
    Hello all!

    I designed a database with unbound forms using ADO to add records. The back-end is located on a network server and the front-end is on each PC. There is also user-level security in place.

    When they first started using the database, there were no problems in entering information. The database has been in use for a little over a month now, and it's starting to crash on them after entering so many records (one estimate was 8 - 10 records) at a time. It'll give them an error, crash and then they have to reboot before they can log back in to the database through Access security. As soon as they reboot, the process happens all over again. At first, I thought this was just localized to a certain form/table. However, it's now doing it on two unrelated forms/tables.

    Does anyone have any knowledge of issues that may be causing this? I'm in a fix! Help if you can. Thanks!
    At what level is your ADO connection variables? (Basically how often are you opening and closing them?)

    Can I assume that after a crash you kick everyone and run a compact & repair on the BE?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2004
    Posts
    156
    Here's my ADO code in brief for one of my forms:

    Code:
    Set rst = New ADODB.Recordset
    rst.Open "LaborCost", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
    With Me
        rst.AddNew Array("Example1", "Example2"), _
        Array(.txtExample1, .txtExample2)
    End With
    rst.Update
    rst.Close
    Set rst = Nothing
    This code happens every time they add a new record, which may be as often as one every 4 or 5 seconds.

    Concerning your second question, no, I don't have the code in place to repair on crash. I don't know how to do that.

    Need any other information, just let me know.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DocX
    Here's my ADO code in brief for one of my forms:

    Code:
    Set rst = New ADODB.Recordset
    rst.Open "LaborCost", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
    With Me
        rst.AddNew Array("Example1", "Example2"), _
        Array(.txtExample1, .txtExample2)
    End With
    rst.Update
    rst.Close
    Set rst = Nothing
    This code happens every time they add a new record, which may be as often as one every 4 or 5 seconds.

    Concerning your second question, no, I don't have the code in place to repair on crash. I don't know how to do that.

    Need any other information, just let me know.
    Yes ... AT WHAT LEVEL? Is it Global? Form level? Function level?

    What I've found is that the ADO Connection objects are a bit shaky in that I had 2 ADO Connections going and I would have the same kinds of problems ... I think the ADO driver is not thread safe and is subject to overwrite corruption ...

    Look at how often you instantiate your ADO connection(s). I have 1 connection and it is instantiated once upon entry ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jul 2004
    Posts
    156
    This is done on a form level, because each form accesses a different table.

    Well, I have a table constantly open as soon as the database opens that leaves the connection open. I do this by opening a hidden form as soon as my start form opens. Of course, this is a bound form, which uses DAO. Since the current project's connection is constantly connected, assuming ADO's connection is based on this same one, ADO wouldn't have to open and re-open the connection. Is this a wrong assumption?
    DocX

    The teachings of God's Begotten: 2 John 1:9

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DocX
    This is done on a form level, because each form accesses a different table.

    Well, I have a table constantly open as soon as the database opens that leaves the connection open. I do this by opening a hidden form as soon as my start form opens. Of course, this is a bound form, which uses DAO. Since the current project's connection is constantly connected, assuming ADO's connection is based on this same one, ADO wouldn't have to open and re-open the connection. Is this a wrong assumption?
    No ... Assuming that you're doing ADO within the current DB project ... I do the same.

    Now, why on a form level? I can see a recordset instantiation ... But not an ADO Connection. Are your forms modeless?
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jul 2004
    Posts
    156
    I'm not sure I know what you mean by modeless. And the reason I do it on a Form level is that it seemed to make the most sense to me. Each form is going to add records a different way so I'd have to have different code for each one. Is there something I'm missing?
    DocX

    The teachings of God's Begotten: 2 John 1:9

  8. #8
    Join Date
    Jul 2004
    Posts
    156
    Got any more help on this M Owen or anyone? I'm still having the same problem and haven't figured it out yet.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Your recordset is ForwardOnly ... You cannot add records under this type of cursor. Try using the adOpenDynamic cursor or even the KeySet ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Jul 2004
    Posts
    156
    Are you sure? Not to doubt you or anything like that, but this has been what I've been using for a little over a month now and it's been working up until lately. I'm using ForwardOnly because my Access book (Access 2002 Developer's Handbook) suggests that it's the fastest recordset and it's updateable.

    Thanks for your help, M Owen. I really appreciate it.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well ... I rescind that ... There is nothing that says you can't ...

    One thing I did notice tho ... You say this runs every 4-5 seconds? The ADO driver is a lazy write driver and it takes 5-6 seconds to flush the buffer. That might be where you're having your problem ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Jul 2004
    Posts
    156
    What if I were to leave the recordset open until the form closed? Do you think this could help? On Form close, I would close the recordset and set Recordset = nothing. Would that help the speed? Would this cause memory issues by any chance? The only thing I would have to do differently is declare the recordset variable at the form level instead of the procedure level, correct?

    Also, you mentioned earlier that you can code it to where it will compact/repair the database after a crash. I'm assuming this wouldn't be the simplest things to do, so do you have any references to point me to to get help on this?
    DocX

    The teachings of God's Begotten: 2 John 1:9

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DocX
    What if I were to leave the recordset open until the form closed? Do you think this could help? On Form close, I would close the recordset and set Recordset = nothing. Would that help the speed? Would this cause memory issues by any chance? The only thing I would have to do differently is declare the recordset variable at the form level instead of the procedure level, correct?

    Also, you mentioned earlier that you can code it to where it will compact/repair the database after a crash. I'm assuming this wouldn't be the simplest things to do, so do you have any references to point me to to get help on this?
    Check out the CompactRepair method ...

    Waiting to close will not help. It's the driver itself ... (You could write a new one ...) What you might want to consider doing is trap if the recordset is still updating ... Also look at increasing your delay between writes ...
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Jul 2004
    Posts
    156
    What this is is data entry of hours worked. The employees automatically advance alphabetically. So, normally, the only thing that needs to be done is the hours typed in and Enter is hit. Then it advances to the next one. The only extra time is when the entrant looks at the next employee to figure out his hours.

    By the way, one of the errors that is shown is Error 3129 Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.

    As I said before, this error only pops after entering around 9 or 10 records. If it displays this error, it will possibly let you add another few records. Then it will crash with no error.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DocX
    What this is is data entry of hours worked. The employees automatically advance alphabetically. So, normally, the only thing that needs to be done is the hours typed in and Enter is hit. Then it advances to the next one. The only extra time is when the entrant looks at the next employee to figure out his hours.

    By the way, one of the errors that is shown is Error 3129 Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.

    As I said before, this error only pops after entering around 9 or 10 records. If it displays this error, it will possibly let you add another few records. Then it will crash with no error.
    That's th error that pops up when you try to run a SELECT SQL statement using the RunSQL method ... Is it possible that you're blowing out the recordset by/with a buffer overflow?
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •