Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access table in SQL (Access 97/ SR2)

    Hi all,

    I have the below VBA code which used to work perfectly. However, I have moved the access table to SQL and linked it and the code now does not work. The permissions for the table are all set up correctly, so it isn't that.
    My knowledge of VBA recordsets is a bit vague, therefore I was wondering if there is something wrong with the line: dbs.OpenRecordset("OrderHeader") and I might need to change something now the table is in SQL? Any help would be much appreciated.


    Dim crs As Recordset
    Set dbs = CurrentDb()
    Set crs = dbs.OpenRecordset("OrderHeader")
    With crs
    .AddNew
    !OrderNumber = 148386
    !AccountId = 8
    !OrderDate = Forms![Menu]![DateSelect]
    !TeacherId = 1
    !DocumentStatus = "P"
    !InvoiceNumber = 180233
    !InvoiceDate = Forms![Menu]![DateSelect]
    crs.Update
    End With

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access table in SQL (Access 97/ SR2)

    You indicate it doesn't work but you don't indicate the error you are getting, so this is a shot in the dark:

    With SQL Server, you nearly always need to specify the type of recordset you are opening, and the option "cbSeeChanges" needs to be specified as a part of the OpenRecordset command. I presume you do have DAO specified.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access table in SQL (Access 97/ SR2)

    Sorry WendellB, posted it in a bit of a rush (and no, I didnt rush to watch the football!).

    The error returned was ODBC call failed, with no description of why it failed.

    Appologies again, but i'm not entirely sure what you mean by: "I presume you do have DAO specified."
    I thought the .OpenRecordset part was the DAO? (Did mention that I wasn't very experienced with recordsets didn't I?).

    Ill have a look at using dbseechanges, thanks for the tip.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access table in SQL (Access 97/ SR2)

    Wendell may have missed the Access 97 version. In Access 2000 and later, DAO is only *one* of the object models, and both of them have recordset objects ... *different* recordset objects. If you ever plan to upgrade <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>, it might be a good idea to get into the practice of specifically declaring your objects as DAO.Database, DAO.Recordset, etc. That will make it easier to upgrade the databases to 2000 or 2002.

    You might want to download and install the latest Jet service pack for Jet 3.5/Access 97. I think the SPs for that version went up to 4 or 5. That error in Access 2000 is certainly a cause for installing the latest Jet service pack. It might be the same in 97.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access table in SQL (Access 97/ SR2)

    Thanks for the tip about the use of DAO. we have used that command in many databases and Its just dawned on me the scale of the task of upgrading. Which, to my knowledge, is the reason we haven't upgraded yet.
    I don't suppose you could tell me if that error is likely to be caused by something i've done (or not) in Access, something i've done in SQL or the function literally isn't available for use on an SQL table, hence the need for the Jet Service pack?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access table in SQL (Access 97/ SR2)

    Sorry - I've been at clients since shortly after I posted the message. In any event, ODBC isn't very informative about errors that occur on the SQL side of things - you typically get some sort of error message, sometimes with a number, that is meaningless from a debugging standpoint. Your code looks straightforward, so it seems unlikely that the problem is there, though I believe you do need to specify the recordset type as dynaset or something similar. (It's been too long since I've actively worked with 97.) BTW, the cbSeeChanges in that post should be dbSeeChanges - but may not apply to 97. Given that doesn't solve the problem, are you prehaps missing the reference to DAO altogether (my comment this morning attempted to suggest that, but not very well)? Another possibility is trying to store a text string in an integer or datetime column. Hope you are making progress on the problem.
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access table in SQL (Access 97/ SR2)

    I think i've finally worked out what the problem is.

    I'm not 100% sure but I believe, when adding records through vba to an SQL table, the default value of a field doesnt get picked up. As the SQL table has a few fields that no not allow nulls, an odbc error is then returned as the field wasnt given a value and the default value didnt get automatically inserted.

    Ill have to try and find the time to test it further just to be certain.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access table in SQL (Access 97/ SR2)

    Did you create any default constraints on the SQL side? This is one area I had to manually add when I upgraded to SQL tables.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access table in SQL (Access 97/ SR2)

    That will almost surely cause that kind of problem - we see it frequently. One solution is to set default values in SQL Server, the second is to set default values on a form, and the third is to allow null values for that field/column.
    Wendell

Posting Permissions

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