Results 1 to 9 of 9
  1. #1
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150

    Access VBA - CREATETABLE command returns Error 3047 "Record too Large"

    Hi,



    Using the CREATETABLE method with 12 CHAR fields returns Error 3047. However, if I remove some fields, it creates the tables fine. I'm not adding any data, just creating fields. Is there a limit with the CREATETABLE method?

    P.s. this is in a loop for multiple tables. See code below.

    Code:
    For i = 1 To 2
        db.Execute "CREATE TABLE Temp_QuoteProcs" & i & " (ID CHAR, PARTNUMBER CHAR, PROCID CHAR, PROCVERSION CHAR, RATE CHAR, TOOLING CHAR, RESOURCES CHAR, BURDEN CHAR, SETUP CHAR, TEARDOWN CHAR, INSTRUCTIONS CHAR, orderfield SINGLE);"
        
    Next i
    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Two thoughts. You aren't specifying a length, so you're probably getting the max on each field (255 I think). I'd specify a reasonable field size for all those and see what happens.

    Second, CHAR will create a fixed width field (data padded with spaces to the length). I'd use TEXT instead.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try to use Text or Memo for the data type as CHAR is not a valid Access data type:
    https://www.w3schools.com/sql/sql_datatypes.asp

    Cheers,
    Vlad

  4. #4
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Quote Originally Posted by Gicu View Post
    Try to use Text or Memo for the data type as CHAR is not a valid Access data type:
    https://www.w3schools.com/sql/sql_datatypes.asp

    Cheers,
    Vlad

    Gicu,

    Thanks for the reply... What constitutes 'valid'? I can use CHAR with less fields and produce a table with 'short text' fields as a result. The documentation, especially by MS, is extremely weak on data types with the CREATETABLE method.

    Thanks for the link also! I will try and report back.

    Regards

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Gicu View Post
    CHAR is not a valid Access data type:
    It can't be chosen from the design grid but it will work in a DDL query. Does create a fixed width field, which typically is not desired.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you see post 2? This works fine:

    CREATE TABLE Temp_QuoteProcs1 (ID CHAR(20), PARTNUMBER CHAR(20), PROCID CHAR(20), PROCVERSION CHAR(20), RATE CHAR(20), TOOLING CHAR(20), RESOURCES CHAR(20), BURDEN CHAR(20), SETUP CHAR(20), TEARDOWN CHAR(20), INSTRUCTIONS CHAR(20), orderfield SINGLE);

    so the problem is with the max length being specified for all fields. But I'd use TEXT as mentioned.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by ironfelix717 View Post
    The documentation, especially by MS, is extremely weak on data types with the CREATETABLE method.
    This may help:

    http://allenbrowne.com/ser-49.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    All,

    Thanks, I ended up using TEXT and all is good.

    Thanks for the resources and help with this.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    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. Replies: 8
    Last Post: 06-28-2018, 09:10 AM
  2. Replies: 2
    Last Post: 12-23-2015, 09:32 PM
  3. Replies: 4
    Last Post: 12-12-2014, 08:48 AM
  4. Replies: 2
    Last Post: 01-03-2014, 09:35 AM
  5. Replies: 11
    Last Post: 11-26-2010, 10:53 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