Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: Add New Records

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Add New Records

    I have an error message on rs.Update
    "ODBC-- Call Failed" Run Time 3146

    What does this mean?

    I have all the tables linked

    Code:
    Private Sub cmdAddNew_Click()
    Dim rs As DAO.Recordset
    Dim NewID As Long
    Dim sQRY As String
        
        varInput = InputBox("Enter the NHS Number", "Add new Data")
        If varInput = "" Then Exit Sub
            sQRY = "SELECT * FROM jez_SWM_InputDetails WHERE False"
                Set rs = CurrentDb.OpenRecordset(sQRY, dbOpenDynaset, dbSeeChanges)
        rs.AddNew
        rs.Update
        NewID = rs.Fields![PersonalID]
        rs.Fields![NHSNo] = varInput
            sQRY = "SELECT jez_SWM_InputDetails.* FROM jez_SWM_InputDetails WHERE jez_SWM_InputDetails.PersonalID = " & NewID
        Set rs = CurrentDb.OpenRecordset(sQRY, dbOpenDynaset, dbSeeChanges)
        rs.Close
        Set rs = Nothing
        Me.RecordSource = sQRY
        Me.txtDummy.SetFocus
        Me.Requery
    End Sub

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You might also want to look at this statement in your code:

    sQRY = "SELECT * FROM jez_SWM_InputDetails WHERE False"

    I think it should be something like sQRY = "SELECT * FROM jez_SWM_InputDetails WHERE <somefield> = False"

    Also, you should probably close rs (ie. rs.close, set rs = nothing) before your next Set rs = CurrentDb.... code.

    Also, why are you opening rs a 2nd time and closing it again (without doing anything)?

    ie..
    sQRY = "SELECT jez_SWM_InputDetails.* FROM jez_SWM_InputDetails WHERE jez_SWM_InputDetails.PersonalID = " & NewID
    Set rs = CurrentDb.OpenRecordset(sQRY, dbOpenDynaset, dbSeeChanges)
    rs.Close
    Set rs = Nothing

    And, if you do this....
    Me.RecordSource = sQRY
    you "usually" don't need this...
    Me.Requery

    (which just causes delays if you have a large recordset.)

    I've also never really had to worry about sp_configure 'user options',512 for SQL Server linked tables. But I also use ADO coding so I'm not sure if DAO is different.
    Last edited by pkstormy; 11-08-08 at 01:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    If I was to this using ADO, what would be my best way of doing this?

    I have only ever used ADO to return data from a dataSet never written to it...

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    (I'm doing this from memory so excuse any syntax errors but here's what I might suggest if you want to use ADO.)

    Private Sub cmdAddNew_Click()
    dim varInput as variant
    varInput = InputBox("Enter the NHS Number", "Add new Data")
    if isnull(varInput) then
    msgbox "You didn't enter any value."
    exit sub
    else
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "SELECT * FROM jez_SWM_InputDetails WHERE <somefield> = False"
    (Note: you need to come up with your SQL statement above as it's confusing on what you're trying to do.)
    rs.open strSQL, currentproject.connection, adopendynamic, adlockOptimistic
    rs.addnew
    rs!SomeField = varInput
    rs.update
    rs.close
    set rs = nothing
    end if
    (I'm not sure what you want to accomplish with these next lines of code)
    Me.RecordSource = sQRY (or strSQL)
    Me.txtDummy.SetFocus
    Me.Requery
    (It looks like you want to set the recordsource of the current form to your SQL statement but again, it's unclear on what you're trying to accomplish with this whole thing. You need to clarify that first.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe you are using the
    WHERE false
    to retrieve an empty recordset - that is all you need for an append.

    but the whole recordset exercise seems a little complicated for the task in hand.

    how about
    strSQL = "INSERT INTO jez_SWM_InputDetails thisNumber = " & me.someNumberTextbox & ", thatString = '" & me.someStringTextbox & "';"
    currentdb.execute strSQL

    it is shorter to type
    it executes faster.
    it is independent of DAO/ADO references
    and no need to declare/close/nothinig the rs object .

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by izyrider
    it executes faster.izy
    CurrentDb.Execute is using DAO. Yes, when comparing currentDb.Execute to DoCmd.RunSQL the currentDb.Execute is faster. But I think it's debatable when comparing currentDb.Execute to ADO and using the rs.AddNew.

    When comparing DAO versus ADO, DAO is generally faster but there are advantages to ADO versus DAO.

    I started using ADO about 10 years ago as I found it easier (for me) to trap errors with SQL Server linked tables. Also, when I was "Inserting" thousands of records, I found using the rs.addnew to be faster (about 20 times) than using CurrentDb.Execute. Regardless, I think you do gain some performance enhancements when getting the server to handle the manipulation as opposed to trying to manipulate the data from the client side.

    I could've also made the strSQL = "INSERT INTO....." in the above example and avoided using the rs.addnew. This is my personal preference as I like to open the recordset (rs.open strSQL....) and then do the rs.addnew. I got into the habit of doing this to help trap errors with the specific problematic field when doing the update.
    Last edited by pkstormy; 11-09-08 at 14:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    [QUOTE=pkstormy
    strSQL = "SELECT * FROM jez_SWM_InputDetails WHERE <somefield> = False"
    (Note: you need to come up with your SQL statement above as it's confusing on what you're trying to do.)
    [/QUOTE]

    How do you mean <somefield>? I was wanting to append the records from the form to all fields in the table, do I need to code all fields?

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have tried the ADO code and get the following error...

    Run-time error '2105'
    You can't go to the specified record.

    it falls on the Me.txtDummy.SetFocus

    After that, on opening the form again its just a blank form apart from the label at top of the form. Why would this happen?

    I have attached my DB to make it easier to explain
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Just thinking out loud, would it not be a better idea if I, created the connection to the Server and then had a Stored Procedure, do all the hard work, instead of having Linked Tables?

    Would that have any advantages?

  11. #11
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Ok, I have been looking at this over and over, I seem to have it writing the new varInput into the table. But how can I get it to populate the textbox on the form so that I can input new data for that record?

    Code:
    Private Sub cmdAddNew_Click()
    Dim cnn As ADODB.Connection
    Dim varInput As Variant
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=sqloledb;Data Source=CISSQL1;Initial Catalog=CORPINFO;Integrated Security=SSPI;"
        Set rs = New ADODB.Recordset
            
        varInput = InputBox("Enter the NHS Number", "Add new Data")
        If IsNull(varInput) Then
            MsgBox "You didn't enter any value."
            Exit Sub
        Else
            strSQL = "SELECT jez.SWM_InputDetails.* FROM jez.SWM_InputDetails WHERE jez.SWM_InputDetails.NHSNo = " & varInput
            Debug.Print strSQL
            rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
            rs.AddNew
            rs![NHSNo] = varInput
            rs![InputBy] = fOSUserName()
            rs![InputDate] = VBA.Now
            rs.Update
            rs.Close
            Set rs = Nothing
        End If
        Me.txtDummy.SetFocus
    End Sub

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by JezLisle
    Just thinking out loud, would it not be a better idea if I, created the connection to the Server and then had a Stored Procedure, do all the hard work, instead of having Linked Tables?

    Would that have any advantages?
    Yes. But stored procedures can be a bit tricky. You may want to get this working first using linked tables and then work on the code for a stored procedure. It's one less issue to deal with as I think you need to work on your SQL syntax.

    I only used stored procedures for specific tasks. I found no "big" advantage of using stored procedures over using ADO code other than it performed tasks on large recordsets (ie. 2-3 million) much faster. Don't get me wrong - there are other advantages but I don't think you're dealing with a vast number of records here but I could be wrong. I would still work on getting your ADO (or DAO) code working first. Then I'd tackle the stored procedures.
    Last edited by pkstormy; 11-10-08 at 11:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Quote Originally Posted by pkstormy
    Yes. But stored procedures can be a bit tricky. You may want to get this working first using linked tables and then work on the code for a stored procedure. It's one less issue to deal with as I think you need to work on your SQL syntax.

    I only used stored procedures for specific tasks. I found no "big" advantage over using ADO code other than it performed tasks on large recordsets (ie. 2-3 million) much faster. I don't think you're dealing with a vast number of records here but I could be wrong.
    No problem, I was exploring differing ways this morning, trying to understand my best options

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personally I prefer sprocs by a long way to this method. It is easy to say this when you know T-SQL, but I would have thought writing & calling a sproc much easier than adding records via a recordset.

    I never, ever use recordsets for write operations.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Dim cnn As ADODB.Connection
    Dim varInput As Variant
    Dim rs As ADODB.Recordset
    Dim strSQL As String

    Set cnn = New ADODB.Connection
    cnn.Open "Provider=sqloledb;Data Source=CISSQL1;Initial Catalog=CORPINFO;Integrated Security=SSPI;"


    Ok...why are you using cnn here? First tell me what you're hoping to accomplish by doing this versus just using rs.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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