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

    Unanswered: Connection problem

    Using the code below I am trying to find the Max ID in my table. I have an error message

    Run-time error '2147217900'
    [Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near the keyword 'IN'

    Code:
    sql = "SELECT MAX(PatientID) AS MaxID " & _
                "FROM IC_ReferralRecord IN '' [ODBC;Driver={SQL Server};Server=C****1;Database=C****O;Trusted_Connection=Yes] "
        rs.Open sql, cnn, adOpenStatic, adLockReadOnly
        Me.txtPatientID.Value = rs(0)
    Using the Debug.Print sql it runs this code,

    Code:
    SELECT MAX(PatientID) AS MaxID FROM IC_ReferralRecord IN '' [ODBC;Driver={SQL Server};Server=C****1;Database=C****O;Trusted_Connection=Yes]
    which works great when I have run it through query builder.

    How can I get around this?

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

    On the basis that this is following on from your previous post relating to 'Using INSERT INTO & ADO' and assuming the 'cnn' connection is OK, and connection is to this to this database

    "[ODBC;Driver={SQL Server};Server=C****1;Database=C****O;Trusted_Conn ection=Yes]"

    then see if this works

    sql = "SELECT MAX(PatientID) AS MaxID " & _
    "FROM IC_ReferralRecord"

    rs.Open sql, cnn, adOpenStatic, adLockReadOnly

    Me.txtPatientID.Value = rs(0)

    ??

    The difference between the two is that one makes the connection with the IN clause, the other is connected using the ADODB.Connection object.

    Let us konow how you get on.


    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
  •