Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Using INSERT INTO & ADO

    I have this code below which when tables are linked works great and inserts a record into the table.

    How is it possible to use ADO and create a recordset to insert the same data into a table using ADO?

    How can I do this?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You gonna post the code Jez?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'll just answer YES, it's possible to use ADO to do an INSERT, but SQL would be quicker.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Sorry yes code would be good

    Code:
    DoCmd.RunSQL "INSERT INTO tblICReferralRecord (Forename) " & _
                                "VALUES ('" & Me.txtForename & "')"
                                Me.txtPatientID = DLookup("max(PatientID)", "tblICReferralRecord ")
        Me.RecordSource = _
                "SELECT tblICReferralRecord.* " & _
                "FROM tblICReferralRecord " & _
                "WHERE tblICReferralRecord.PatientID = " & Me.txtPatientID & " "

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I use DAO
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    To use DAO, hows best way?

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have managed to get the lines of code below to add a new record for the form

    Code:
          sQRY = "INSERT INTO tblICReferralRecord (Forename) " & _
                        "VALUES ('" & Me.txtForename & "')"
        cnn.Execute sQRY
    but my issues is that when I use this code below it doesnt like the tblICReferralRecord

    Code:
    Me.txtPatientID = DLookup("max(PatientID)", "tblICReferralRecord
    It says
    The Microsoft Jet Database engine cannot find the input table or query tblICReferralRecord. Make sure it exist amd that its name is spelled correctly

    How can I get around this?

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think you mean this
    Me.txtPatientID = DMax("PatientID", "tblICReferralRecord ")

    ??

    MTB

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thank I tried that and I still get the error message

    The Microsoft Jet Database engine cannot find the input table or query tblICReferralRecord. Make sure it exist amd that its name is spelled correctly

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If this works
    Code:
    sQRY = "INSERT INTO tblICReferralRecord (Forename) " & _
                        "VALUES ('" & Me.txtForename & "')"
        cnn.Execute sQRY
    and this does not
    Code:
    Me.txtPatientID = DMax("PatientID", "tblICReferralRecord")
    then, provided that is the only error, either the cnn connection is not using the 'CurrentProject.Connection' (ie. its connected to a different DB) or you have left the trailing space in the table name from my previous post (oops!!).

    ??


    MTB

  11. #11
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    How can I tell which database its connecting to. I have told the cnn.open the SQL Server I want?

    I havent left a trailing space so that takes that out.

  12. #12
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Irrespective of which database the ADO connection is connected to, the question is, as the error message says, does the table 'tblICReferralRecord' exist in the database that the code is written in, either as a physical or linked table?

  13. #13
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Yes it exists, and named as I have written it.

    Now as I am using tables that are not linked is that the issue? does it matter using ADO is tables are linked or not?

    Which is best way to go?

  14. #14
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Using ADO it does not matter if the tables are linked or not, provided the connection string (what connection string is currently being used?) provided it connects to the DB with the relevant tables. However, to use and domain functions (ie DMax() etc.) in the current Database (ie. the one in which the code is written) then the tables have to be in or linked to that Database. If this is not the case then you will recive the error message indicated.

    Using the ADO connection you will need to return the value using a record set like

    sql = "SELECT Max(PatientID) as MaxID FROM tblICReferralRecord"

    Dim rs as New ADOBD.Recordset

    rs.Open sql, cnn, adOpenStatic, adLockReadOnly

    MsgBox "Last ID = " & rs(0)


    (air code only)


    Does that help?


    MTB

Posting Permissions

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