Results 1 to 9 of 9
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: Adding New Records

    Often on my projects I will create a form that is specifically meant for adding new records. When a button is clicked, I run code to enter a new record into a table. But I have a question for you experts out there; I know of two distinct methods to do insert records:
    Code:
    Dim dbs As DAO.Database
    Set dbs = currentdb
    Dim rstTable As DAO.Recordset
    Set rstTable = dbs.OpenRecordset("TblName", dbOpenDynaset)
    With rstTable
        .AddNew
        !FldName1 = Me.ControlName1
        !FldName2 = Me.ControlName2
        ...
        .Update
    End With
    Code:
    Dim strSQL As String
    strSQL = "INSERT INTO TblName ( FldName1, FldName2, ...)" & _
                "VALUES ( " & Me.ControlName1 & ", " & Me.ControlName2 & ", ...);"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    My question is, what's the better way? Or more accurately, what are the advantages and disadvantages of each method? Thanks for any input!

    EDIT: Or is there an even better 3rd way?
    Last edited by nckdryr; 02-26-07 at 11:43.
    Me.Geek = True

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Nick,

    Both of your examples are DAO ... The 1st obviously ...

    What about ADO? Why not .Execute your insert?

    CurrentProject.Connection.Execute YourInsertSQL,,,adcmdtext

    Or use an ADO recordset to do the same ... (no example off the top of my head - although quite similar to the DAO example) ...

    Answer: SPEED

    Generally speaking, DAO is faster than ADO (at least in VB) ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought SQL was always the faster to execut method - but then again - the difference is probably miniscule.
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought SQL was always the faster to execut method - but then again - the difference is probably miniscule.
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    From here:
    http://support.microsoft.com/kb/225048

    DAO is considerably faster when dealing with Jet.

    Performance

    DAO makes much more efficient use to the Microsoft Jet database engine than ADO and many operations are faster under DAO, sometimes up to 5 or 10 times faster, such as use of Batch updates. Another issue is that the calls made to retrieve schema information are inefficient when applied against Jet. This results in queries and updates against tables with a large number of columns being 30 percent to 80 percent slower than the equivalent query using DAO. One example of inefficient usage of Jet by ADO is illustrated in the next section on Connection Issues.
    Inspiration Through Fermentation

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Does any method have certain properties regarding how it locks the record when adding? That is to say, if I use one of the methods, and a user clicks the button to add a record, and another user clicks the button to add a seperate record, is there a chance (however miniscule) that there could be overwriting issues? Or do these methods lock the record until completed?

    Any help will again be appreciated! Thanks to all.
    Me.Geek = True

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by nckdryr
    Does any method have certain properties regarding how it locks the record when adding? That is to say, if I use one of the methods, and a user clicks the button to add a record, and another user clicks the button to add a seperate record, is there a chance (however miniscule) that there could be overwriting issues? Or do these methods lock the record until completed?

    Any help will again be appreciated! Thanks to all.
    Remember, your locking is set/done at the database level ... Table, Page, or Row level. Then you have your LockTypes - optimistic and pessimistic ...

    To answer your question:Yes, there is always a "chance" for a collision on new records in a multi-user setting ... Realistically, not much because of the preceding settings ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Not sure if this applies here but can't you wrap a .Application.Lock -> .Application.Unlock around the save code?

    Can't remember if that's VB or not
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is there a chance (however miniscule) that there could be overwriting issues?

    Jet INSERT?
    No!


    Jet UPDATE?
    Yes!
    ...but easily fixed with a 'no-lock lock' along the lines of
    UPDATE tblX SET fldZ = newVal WHERE fldZ = oldVal And pkX = 12345
    and then check .recordsaffected to review/repeat the UPDATE if another user 'stole' your update (i.e. other user's UPDATE makes your fldZ = oldVal False -> your UPDATE doesn't happen)

    izy
    currently using SS 2008R2

Posting Permissions

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