Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57

    Unanswered: Sql Svr 2000 via ODBC & bigint & Bit

    I am using MS Access 2003 to link Sql server 2000 tables via odbc. When I initially created the links, some of the tables were editable and some were not. Some of the tables kept giving me the error “This record has been edited………”. After pounding my head against the wall for hours I finally figured out what the problem was. It was the bit and Bigint fields. As soon as I changed the bit fields and bigint fields to integer fields, I no longer had a problem – I could add, delte and edit data from all my tables.

    Is this a standard problem with ODBC, MS Access or something else? Has anyone had this problem and if so how do you get around it other than changing field types?

    Thanks
    GEM

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to MS Access topic - I think you'll get a better response there.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    IIRC you can add a timestamp column to the SQL Server tables and Access plays ball again. You might want to Google that to confirm - some years ago that I came up against this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup... it could also be that every table requires a primary key field. I make sure every table has both a PKF and a timestamp field.
    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

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes (again)....With SQL Server, for some reason, after adding in a timestamp type field, a lot of my bit, bigint problems went away. It also seemed to have something to do with tables that had memo (or Text) field types in them.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    The time stamp field didn't fix the problem. I created a test table with a bigint identity field as the primary field, an nvarchar field, a bit field and a timestamp field. When I linked the table I got nothing but #'s. When I changed the identiy field to int, and the bit to int I could view and edit the data.

    Adding a timestamp field didn't fix my problem.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is the bit NULLable?
    Do you have a primary key on the table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    Yes my primary key was the identy field. Initially the identiy field (primary key Field) was bigint. only #'s when I linked. When I changed the primary key to int I could view, add, update and delete records.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - not replicated the problem but looking around suggests you are stuck. Int will do you for about 2 billion records anyway. I believe ADPs are ok.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    I agree ADP's are really great, but the only problem is that the ADP exposes all of the sql server objects and unless you code the hell out of your database to prevent viewing the database window, users have access to everything.

    On the other hand if you are upsizing a completed application to SQL Server, if you use linked odbc tables, all of the queres, forms and reports for the most part will convert directly over because you are using access sql as opposed to Sql Server sql. All you have to do is changed the names of the sql server linked tables (Remove the DB0_ or UserName_ tables) from
    the tables ie (dbo.Table1 to just Table1) and all of the queries should work. If you created an adp you would have to create all the views and stored procs to replace all the quereis in the mdb, not to mention all the sql behind your forms and reports.

    thats my approach.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Wow.... so you can't have a bigint as a PKF... that's a new one for me.

    Thanks for posting the details gem1204

    I guess if you have > 2 billion records, it's probably time for a PHP front-end!
    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

Posting Permissions

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