Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL server connections (2003)

    With help from previous posts to this forum I have managed to set up DAO and ADO excel connections to Access databases. Our techies have now told me that I must upgrade my Access databases that are working quite happily to SQL server 2000, and have installed a copy of SQL server on my desktop for me to play with.

    I have managed to create an ODBC connection to the data source on my desktop.

    Can somebody please advise how I connect excel VBA to this data source so that I can retrieve data in the normal DAO/ADO manner?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL server connections (2003)


  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL server connections (2003)

    Thanks, Example B worked with a bit of adaptation at the first attempt

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL server connections (2003)

    Hans

    I have now got 90% of the functionality back again using SQL server. However can you please advise on the following code for inserting data into the database

    Within Access the following worked:
    strSQL = "INSERT INTO ITS_ReqEndUser (ReqNo, DateUpdated, EndUser) " & _
    "VALUES (" & CurrentRequest.RequestNo & ", #" & Date & "#, '" & CurrentRequest.EndUser & "');"
    cn.Execute strSQL, dbFailOnError

    However when connected to SQL Server the macro fails on the final line

    For your additional information strSQL = converts to
    INSERT INTO ITS_ReqEndUser (ReqNo, DateUpdated, EndUser) VALUES (54645, #16/05/2007#, 'End User');

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL server connections (2003)

    Using VALUES in an append query is Access-specific. Try this:
    <code>
    strSQL = "INSERT INTO ITS_ReqEndUser (ReqNo, DateUpdated, EndUser) " & _
    "SELECT " & CurrentRequest.RequestNo & ", #" & Date & "#, '" & CurrentRequest.EndUser & "';"
    </code>
    This uses SELECT instead of VALUES.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL server connections (2003)

    FYI the following works:
    strSQL = "INSERT INTO ITS_ReqEndUser (ReqNo, DateUpdated, EndUser) " & _
    "VALUES (" & CurrentRequest.RequestNo & ", " & Date & ", '" & CurrentRequest.EndUser & "');"
    cn.Execute strSQL, recaffected

    I also found various examples on the following site:
    http://www.w3schools.com/ado/ado_add.asp

Posting Permissions

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