Results 1 to 14 of 14
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Access PK int limitation to 32,000 using SQL 2008 pk of int. Need to change data type.

    Front end Access 2007. Back end SQL server 2008 R2. In development phase only.



    When I set this up I created the sql server 2008 primary keys as int. Access worked fine until it hit its 32,000+ limitation. I need to change the sql Server PK to something other then int. Since I am in the development phase I have the luxury of deleting all records and altering the Primary Keys. But I can't find any clear guidance of what to change things to. This is my first Access SQL Server development. What data type in SQL Server would work and not hit the 32 K limit in access. Would Numeric work?

    For example I could reverse engineer the existing SQL Database with Modelright. Then change the primary keys globally in Modelright. Push the new design with the new primary key back into SQL Server and the change is done. (I have a feeling it won't be that easy but I have to hope).

    But again...what data type should I select? The database will never exceed probably a few million records in any situation but it certainly will exceed 32K.

    Thanks Phred

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    int is what you want

    The small int is comparable to Access' Integer

    int is comparable to Access' Long Integer

    EDIT:
    Here is a reference
    https://msdn.microsoft.com/en-us/library/ms187745.aspx

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I agree that Int is the correct type in SQL Server. What's curious is that when linking tables Access will normally treat that as a Long Integer and you wouldn't have a problem. I wonder if you have VBA code where you've declared a variable as Integer or something?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    I have a lot of declared variables as integers. Dim intSomePK as Integer. If it is an integer does dimming it as an integer produce a change in the integer? This has occurred in a SQL append query where I have entered lots of test data. The number of records tested has exceeded 32K so what you mentioned could be true. But this would probably mean I will have the same problem in other places when I exceed 32K records. Would you recommend dimming it as something else? I could easily run a test. Thanks.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That limits that variable to the 32k. You want

    Dim intSomePK as Long

    edit: to maintain naming convention:

    Dim lngSomePK as Long
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Huh. I never would have thought of that. I can easily test this. I'll let you know. Thanks.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. I learned this one the hard way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Additional questions on Int 32K limitations

    I have an Insert Into SQL query running inside VB. It writes a new Property record and appends it to the Property Table. When that happens the record receives a new Primary Key (Integer) assigned by the sql 2008 database.

    Right after that query runs a DLAST statement executes and grabs the Primary Key from the record just appended.

    varX2 = Nz(Dlast("PropertyID", "dbo_Property"),0)

    And passes that varX2 variable into the next SQL append statement that writes the varX as the Foreign Key to the new table Property_Year_Detail.

    In this situation I have dimmed the PK Integer as a variant and not an INT. When I use it as a variant, and write it to the next table where the FK is an integer is it inheriting the same limitations from the Integer?

    I have several situations where I do this and it may be boosting me to the 32k limit for integers rapidly. I am not sure of the scope of my problem and correcting it. I am posting code for clarification:

    Code:
    Dim varX2 as Variant
    Dim varX3 as Variant
    
    ~Bunch of other code, variables, and stuff here,~
    
    stSQL = "INSERT INTO dbo_Property" & _
         "( County, kPIN, TownshipNO, TownshipNM, CycleID, CycleName, CountyClass, PropertyDesc, LevelofAssess, Type, OpenFile, KattyAssigned, Paddress1, Paddress2, Pcity, PState, Pzip5, TaxCode)" _
        & "VALUES ('" & stCnty & "','" _
        & stKPIN & "','" _
        & stTwnNo & "','" _
        & stSelTwn & "','" _
        & stCycleID & "','" _
        & stCycleNM & "','" _
        & stSelClas & "','" _
        & stDesc & "','" _
        & noLOA & "," _
        & stTypeNM & "','" _
        & stOpenFile & "','" _
        & stKattAs & "','" _
        & stPaddress1 & "','" _
        & stPaddress2 & "','" _
        & stPCity & "','" _
        & stPState & "','" _
        & PZip5 & "','" _
        & stTaxCode & "');"
    
       DoCmd.RunSQL stSQL
                                     
     varX2 = Nz(DLast("PropertyID", "dbo_Property"), 0) 'Grabs Primary key from Property record  just created.
             
    'CREATES ONE COOK COUNTY PROPERTY-YEAR-DETAIL RECORD USING PRIMARY KEY IN varX2 ABOVE.
                                         
    stSQL2 = "INSERT INTO DBO_PropertyYearDetail" & _
        "( PropertyID, TaxYear, County, TownshipNM, TownshipNO, kPIN, CycleID, CycleName, LevelofAssess, kAttyAssigned, FirstYRTriFeeP3, FirstYRTriFeeP4, SecondYrTriFeeP3, SecondYrTriFeeP4, ThirdYrTriFee, VOPTABFeeP5, CEYEAR, TaxCode) " _
        & "VALUES ('" & varX2 & "','" _
        & stTaxYear & "','" _
        & stCnty & "','" _
        & stSelTwn & "','" _
        & stTwnNo & "','" _
        & stKPIN & "','" _
        & stCycleID & "','" _
        & stCycleNM & "','" _
        & noLOA & "," _
        & stKattAs & "'," _
        & varFYTFP3 & "," _
        & varFYTFP4 & "," _
        & varSYTFP3 & "," _
        & varSYTFP4 & "," _
        & varTYTF & "," _
        & varVPFP5 & "," _
        & varCEYEAR & "," _
        & stTaxCode & ");"
        DoCmd.RunSQL stSQL2
        
        varX3 = Nz(DLast("PropertyID", "dbo_PropertyYearDetail"), 0) 'Grabs Primary key from Property Year Detail record just created.
    
    ~And so on~
    Does this problem also exist inside of Access Queries? I have situations where I take the number of records in a table let's say 500 and multiply it times 5 and append 2,500 records into another table. Time after time it creeps up to the 32K limitation thus causing the error and alerting me to the whole issue. How is this situation handled in an Access Query?

    Phred

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I would be explicit and Dimension it as a Long Integer. However, implicitly instantiating the variable as a Variant "should" Cast to a Long Integer if above 3,300. It may be that a Variant Access behaves differently and always stays a Variant. I don't know for sure.

    Just be explicit everywhere within your app. If it is a field in a table, make it a big enough data type. If it is in a control in a form, make it a big enough data type ... etc.

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see anything there that would limit to 32k, though I see things I don't like. I personally wouldn't trust DLast(), but it could work. If you're doing single records, I'd probably use a recordset and get the inserted ID from that. Also, I don't see how varX3 could be any different than varX2, as you're looking up the ID you just inserted using varX2. It's curious you have the varX2 value surrounded by single quotes, which normally would only be appropriate for a text data type.

    You're mixing terms a little, which confuses my old tired brain. Int is the data type in SQL Server. Integer is a data type of a variable declaration in Access, and it can also be the field size of the Number data type in Access. I'd look at the linked table in design view and see what data type Access has interpreted the SQL Server data type to be. Typically it should show Number/Long Integer for an Int field in SQL Server.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    I am starting to get it and I am mixing terms. In design view all of the primary keys PropertyID is AutoNumber. And below in the Fields Properties it is Field Size Long Integer. I can straighten out the other items above.

    You are correct on varX2. I just did that to keep things straight in my head.

    The Dlast statement fires directly after the Append query and so far has always grabbed the correct PK (in design mode, not production). Would you create the recordset after the append and thus get the last PK just added? If the issue is multiple users creating a new property record simultaneously it is possible the two records would be created so fast the dlookup could pick up the PK of the wrong record. But wouldn't the same problem exists with the record set? Perhaps there is something I don't understand about recordsets. I guess I could just ask why you don't like the Dlast?

    I have started making the explicit everywhere. I think I need to take a closer look at things and be more specific. I would like to leave this open and come back with other questions or clarifications. Thanks Paul and Itsme.

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I meant I would use a recordset to do the append, and then get the ID. Here's an example, without the declarations and such (ARKey being the autonumber field in the first table and a foreign key in the second):

    Code:
            With rsMaster
              .AddNew
              !FieldName = Value
              ...
              .Update
              .Bookmark = .LastModified
              lngARKey = !ARKey
            End With
    
            With rsDetail
              .AddNew
              !ARKey = lngARKey
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Trying to translate this to my situation. I have little experience with Recordsets.

    With rsMaster 'The dimmed Recordset dbo_Property Parent Table
    .AddNew 'Add a new record to dbo_Property
    !FieldName = Value 'PropertyID = Value
    ... 'Is this my SQL Query? I am not sure where the query is placed or executed.
    .Update 'Does this run the SQL Query?
    .Bookmark = .LastModified 'A bookmark is set at the last PK just added.
    lngARKey = !ARKey ' This grabs the Primary key of the bookmarked record.
    End With 'ends first recordset

    With rsDetail 'With the dbo_PropertyYearDetail Child Table
    .AddNew 'Add a new record to the child table
    !ARKey = lngARKey 'Use the variable to write the child FK (PropertyID) to the child table

    I understand there are two recordsets.
    I understand that I need to dim all my variables. I need to create variables for rsMaster and rsDetail.
    I understand that I need to populate my variables with values.
    I basically understand what the recordset is doing in your example.
    I don't understand the specifics such as where the SQL Insert Into statements are placed in the example.

    I hope I haven't worn out my welcome.
    Thanks Fred

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The AddNew method of the recordset replaces the INSERT INTO statements. Instead of

    INSERT INTO TableName(Field1, Field2, Field3)
    VALUES(Value1, Value2, Value3)

    you have

    .AddNew
    !Field1 = Value1
    !Field2 = Value2
    !Field3 = Value3
    .Update

    The table name having been given when opening the recordset.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How Do I Change Data Type
    By LeadTechIG in forum Database Design
    Replies: 9
    Last Post: 01-07-2015, 03:05 PM
  2. While Importing Change in data type
    By drunkenneo in forum Import/Export Data
    Replies: 1
    Last Post: 09-01-2014, 06:49 AM
  3. change data type for multiple fields
    By axg275 in forum Access
    Replies: 1
    Last Post: 03-11-2014, 09:31 AM
  4. Change Percentage Data Type
    By jo8701 in forum Access
    Replies: 1
    Last Post: 02-08-2012, 07:15 AM
  5. Replies: 5
    Last Post: 01-09-2012, 05:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums