Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: upsize from access backend to sql backend run-time error "3155" problem

    I have been seaching the web for a week on and off and very intersively the last couple of days and this morning. I tried all that there is to try with no success. I hope someone can shine some light on my problem.

    My access database is running fine and now we would like to upsize it to sql backend.

    When I try to save the main form with the following command,

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


    I got this error message

    Run-time error "3155"
    ODBC -- insert on a linked table 'tblChangeControlFormDetails' failed.

    p.s. I tried to add reference in the vba code but nothing help.

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The first thing I would check is that the SQL Server table has a primary key.
    Paul

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes (as Paul said). I found that sometimes (although rarely), upsizing to SQL Server using the MSAccess upsizing wizard for some reason doesn't create the primary key or make the autonumber field incremental in SQL Server. Also check to make sure your relationships are established on the SQL Server tables. The upsizing wizard works very well but I have had occasional problems with it. Make sure to refresh (or relink) your table(s) in the MSAccess app after modifying any SQL tables. There was 1 time (a long time ago) where I actually had to delete and relink the SQL table into the mdb.

    Also, I've never really had to issue an acSaveRecord type command on the data forms as long as I have an autonumber field (although sometimes I'll use a Refresh or me.requery command in code). I'm a strong believer that every data table (that is updated often) should have an autonumber (or in SQL Server, an int type (set to increment) field.) I've seen way too many problems without one.
    Last edited by pkstormy; 07-11-08 at 22:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Sep 2007
    Posts
    148
    Thank you much. I will try

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The first thing I would do is change the line to:

    DoCmd.RunCommand acCmdSaveRecord

    But only because I hate using DoMenuItem

    Other than that, agree with the others in the you need to verify that the upsizing wizard has created the tables in SQL Server correctly.
    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
    Sep 2007
    Posts
    148
    I have changed all my DoMenuItem to DoCmd.Runcommand. Of course, it is still not working.

    Looking at the primary keys. Do I look at the primary key in Access or Sql? Not sure what to do and the sql kid doesn't know what he is doing either.

    Thanks

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can look at the linked table in design view in Access, but what you really want is to check it in SQL Server.
    Paul

  8. #8
    Join Date
    Sep 2007
    Posts
    148
    Is it truth that if the sql table has no primary key, the table cannot be updated?

    Thanks

Posting Permissions

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