[X]My Assistant

[chrismbaylis]

I'm using the following code to read through a table definition that is documented by the user, to then generate the table as needed:

CODE

Private Sub btnBuildTable_Click()
'On Error GoTo btnBuildTable_ERR

Dim db As Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT tbl_TableDefinitions.* " & _
         "FROM tbl_Tables INNER JOIN tbl_TableDefinitions ON tbl_Tables.[Record ID] = tbl_TableDefinitions.[Table ID] " & _
         "WHERE (((tbl_Tables.[Source Object]) = '" & Me.Source_Table & "')) " & _
         "ORDER BY tbl_TableDefinitions.[Field Number];" ''

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Dim conn As ADODB.Connection
Dim strCon As String
strCon = "CREATE TABLE [" & Me.Source_Table & "]("

With rs
    .MoveFirst
    Do Until .EOF
        strCon = strCon & " [" & ![field name] & "] "
        Select Case ![Data Type]
            Case 1  'yes/No
                strCon = strCon & "BIT"
            Case 2  'Byte
                strCon = strCon & "BYTE"
            Case 4  'Long Int
                strCon = strCon & "INTEGER"
            Case 7  'Double
                strCon = strCon & "FLOAT"
            Case 8  'Date/Time
                strCon = strCon & "DATETIME"
            Case 10 'Short Text
                strCon = strCon & "CHAR"
            Case 12 'Long Text/Memo
                strCon = strCon & "MEMO"
            Case Else
                MsgBox "Error determining datatype for " & ![field name], vbOKOnly + vbCritical
        End Select
        strCon = strCon & ", "
        .MoveNext
    Loop
End With
strCon = Left(strCon, Len(strCon) - 2) & ");"

Set con = CurrentProject.Connection
con.Execute "" _
    & strCon
    
btnBuildTable_EXIT:
    Set con = Nothing
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

btnBuildTable_ERR:
    MsgBox Error$, vbOKOnly + vbCritical
    Resume btnBuildTable_EXIT

End Sub

This results in the following strCon 'string' (structure added here only for clarity):

CODE

CREATE TABLE [__tbl_Project_Core](
[Project Id] FLOAT
,  [Project] CHAR
,  [Status] CHAR
,  [MW%C] FLOAT
,  [Forecast Owner] CHAR
,  [Engagement Mgr] CHAR
,  [Consultant Lead] CHAR
,  [Start Date] DATETIME
,  [Resume Date] DATETIME
,  [Due Date] DATETIME
,  [Expiry Date] DATETIME
,  [Expiry Date (Extended)] DATETIME
,  [Expiry Date (Extended Internal)] DATETIME
,  [Budget] INTEGER
,  [Currency] CHAR
,  [Budget Hours] INTEGER
,  [Total Time Logged] FLOAT
,  [Hours Approved] FLOAT
,  [Last Updated] DATETIME
,  [Unapproved/Unsubmitted Time] FLOAT
,  [Logged Time as %age of Budget] FLOAT
,  [Approved Time as %age of Budget] FLOAT
,  [Milestone High] CHAR
,  [Milestone Low] CHAR
,  [Implementation Type] CHAR
,  [Project Module] CHAR
,  [Project Work Type] CHAR
,  [Service Line] CHAR
,  [Country of Account (SFDC)] CHAR
,  [Groups] CHAR
,  [CSOD Oversight] CHAR
,  [DA Email] CHAR
,  [DA Papering Partner] CHAR
,  [Partner Name (PO/PFA)] CHAR
,  [Contract Date] DATETIME
,  [Contract] CHAR
,  [Survey Sent] CHAR
,  [Survey Contact #1 Email] CHAR
,  [Survey Contact #1 First Name] CHAR
,  [Survey Contact #1 Last Name] CHAR
,  [Survey Contact #1 Role] CHAR
,  [Survey Contact #1 Title] CHAR
,  [Survey Contact #2 Email] CHAR
,  [Survey Contact #2 First Name] CHAR
,  [Survey Contact #2 Last Name] CHAR
,  [Survey Contact #2 Role] CHAR
,  [Survey Contact #2 Title] CHAR
,  [Survey Contact #3 Email] CHAR
,  [Survey Contact #3 First Name] CHAR
,  [Survey Contact #3 Last Name] CHAR
,  [Survey Contact #3 Role] CHAR
,  [Survey Contact #3 Title] CHAR
,  [Survey Contact #4 Email] CHAR
,  [Survey Contact #4 First Name] CHAR
,  [Survey Contact #4 Last Name] CHAR
,  [Survey Contact #4 Role] CHAR
,  [Survey Contact #4 Title] CHAR
,  [Survey Complete] BIT
,  [Archived] BIT);

The length of this statement comes in at 1023 characters.

When the code runs, it gets to the instruction con.execute and throws this error: Code: -2147467259 Desc: Record is too large.

Any ideas on how I can get around this issue? I'm having to use the ADODB method to build the table due to the need for decimal fields, which DAO does not support.

Thanks, as always!

[theDBguy]

Hi Chris. Do you know for sure it's the size of the String or could it be the names of the columns? Just a thought...

[GroverParkGeorge]

I think the problem is hard to diagnose from afar, but I would STRONGLY suggest a different naming convention for field names, as theDBGuy implied.

First, spaces in field names are problematic in and of themselves because they must always be enclosed in square brackets whenever you reference them. That's doable, but an avoidable hassle.

More significantly, using non-standard characters, such as the octothorpe, of hash sign # is dangerous. In Access, that symbol is reserved as the date delimiter, and using it for other purposes is not good practice. The use of parens ( or ) in field names is another hard to justify practice.

Combining both fixes will, no doubt, shorten the SQL String itself, but also minimize problems that can creep in elsewhere.

[PhilS]

Side note: Using the FLOAT (Double) data type as an ID is a pretty bad idea.

Now for your primary problem.
Using the plain CHAR data type in a DDL statement against an Access database will create a Text(255) column.
Unfortunately, when executing a DDL statement, the Jet-/Ace-Engine will calculate the potential maximum storage size of a text column (character count * 2 bc Unicode) towards the maximum record size of 4KB. - Based on this calculation your record is excessively too large.

You could add a length specification to your CHAR columns to reduce their size. - This might still result in a potentially too large record size considering the number of CHAR columns in your table.

When using the DAO.TableDef-Object to create the table only the minimum storage size of a text column will be counted towards the maximum record size (during table creation!). So, you should be able to create your table, but a "Record too large"-error could occur when saving a record exceeding the maximum record size to the table.

DAO.TableDef.CreateField does support the Decimal/Numeric data type.
This post has been edited by PhilS: Nov 16 2019, 01:36 PM

[chrismbaylis]

Thanks for your responses guys. I'll have to work through your comments - unfortunately naming conventions are not mine to own, but a business who have not had a strong data governance in place historically.

I'll work through the data and see if I can trim things down and take on some of your suggestions - thanks for your help. I'll follow up when done.

Regards,

Chris

[cheekybuddha]

You are using too many CHAR fields.

I don't know how many actual fields of that sizr you require, but certainly it looks like a lot can be replaced with VARCHAR(255).

I replaced all the CHAR fields with VARCHAR(255) and the table created without problem, including all the nightmare field names, and also using DAO (CurrentDb.Execute strSQL, dbFailOnError)

hth,

d

[PhilS]

I don't know how many actual fields of that sizr you require, but certainly it looks like a lot can be replaced with VARCHAR(255).

David, thank you. How could I miss this?
This is the explanation why the maximum storage size is assumed upon execution of that statement. - CHAR fields will always be padded to their maximum length. - Now it makes perfect sense.
This post has been edited by PhilS: Nov 17 2019, 06:04 AM

[cheekybuddha]

@Chris

Also check you have Option Explicit declared at the top of your module. You are using an undeclared variable for your connection object:

CODE

' ...
Dim conn As ADODB.Connection
' ...
Set con = CurrentProject.Connection
con.Execute "" _
    & strCon
    
btnBuildTable_EXIT:
    Set con = Nothing
' ...

@Phil
>> How could I miss this? <<
I know, it didn't hit me for a while too. Sometimes it's difficult to see the wood for the trees!

[chrismbaylis]

@CheekyBuddha:

I don't know how many actual fields of that sizr you require, but certainly it looks like a lot can be replaced with VARCHAR(255).

You Sir, are a genius!

Thank you very much - if you ever find yourself near St Albans here in the UK, please do stop by and let me buy you a pint! I've been tearing my hair out over this one - and it was so simple!!

Thanks again to everyone else for your input too.

You guys Rock!

[cheekybuddha]

Make sure you note my point about Option Explicit/undeclared variables



d