Results 1 to 15 of 15
  1. #1
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21

    Question Access 2003 exporting to SQL 2014 via ODBC - error 3047 record is too large

    Using these VBA commands on a query from local tables which returns only 105 records with 6 fields none of which are longer than 80 characters.
    I think the mdb may be corrupted, but repair and compact makes no difference.
    tables were originally imported from sql2019.

    ODBC = "ODBC Database"


    DSNConnection = "ODBC;DSN=ToPTSQL;UID=sa;PWxxxxx"
    DoCmd.TransferDatabase acExport, ODBC, DSNConnection, acQuery, "OwnerQuery1", "Owner"

    (This command works fine for several other tables and queries)

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you export the query manually (using the External Data ribbon - Export\More\ODBC Database button)? What driver is the DSN using?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    Same error exporting manually - record is too large.
    DSN is using SQL Server Native client 11.0

  4. #4
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    Other options available are :
    ODBC Driver 11 for SQL Server
    ODBC Driver 17 for SQL Server
    SQL Native Client
    SQL Server
    SQL Server Native Client 10.0

    Should I try one of these ?

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is the 80 characters max determined by a Len() function or is the actual size of the Short Text fields in the source table (instead of the default 255?
    Can you try to change the select query (OwnerQuery1) into a make-table query then try to export that new fresh local table instead?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    One Text field is defined in table as 50
    One date field (Date/Time)
    Two Yes/No fields
    Len(Text field) shows as 50 (biggest record)

    Make Table gives same error :
    DoCmd.TransferDatabase acExport, ODBC, DSNConnection, acTable, "Owner1", "Owner"

  7. #7
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    Using Access 2003, SP3

  8. #8
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    Sorry - there was a number field as well - defined as long integer

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Weird, have you checked the permissions in the receiving SQL db for the DSN user that it has create tables permission? Maybe try another more recent drive like "ODBC Driver 17 for SQL Server"?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    The same command has worked fine for other tables being exported to the SQL database, so permissions should be OK
    Will try drive 17 as suggested

  11. #11
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    Same error with drive 17 - record too large:
    ODBC = "ODBC Database"
    DSNConnection = "ODBC;DSN=ToPTD17;UID=sa;PWD=Da686568-"
    DoCmd.TransferDatabase acExport, ODBC, DSNConnection, acTable, "Owner1", "Owner"

  12. #12
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    Also tried switching the long integer field to text (50) - no luck

  13. #13
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    Also tried adding an 'x' to the end of all field names in case the issue was a field with a reserved word name - same error

  14. #14
    RonAbbott is offline Novice
    Windows 11 Access 2007
    Join Date
    Nov 2022
    Posts
    21
    Solved !
    I removed each of the 5 fields one by one and found that it worked without the date field.
    Have now formatted the date field as a text field in the query before exporting it.
    The access field is defined as Date/Time and a record displays as 16/05/2016 5:07:17 p.m. when viewing the field.
    (New Zealand settings use dates as d/m/yyyy)

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This might be of some help for the next time you run into that problem
    http://allenbrowne.com/ser-36.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 8
    Last Post: 01-22-2019, 09:38 PM
  2. Need help exporting large Access file into Excel
    By phidelt in forum Import/Export Data
    Replies: 2
    Last Post: 02-27-2013, 06:14 PM
  3. Replies: 2
    Last Post: 09-19-2012, 12:01 AM
  4. Replies: 1
    Last Post: 04-03-2010, 09:35 PM
  5. Replies: 2
    Last Post: 11-10-2009, 02:49 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