error output to an Access DB - record is too large

[DAM]

Dear Community,

The output of an wf goes into an Access DB (I overwrite the table every time). I have tried with both mdb and accdb and I get the following error:

Error: Output Data (51): Error creating table Table1: Microsoft Access Database Engine: Record is too large.\3047 = -67175426CREATE TABLE "Table1" and all the fields are shown.

I have changed the Type and Size of the fields to String (less than 100) and Fixed Decimal, but no luck. What could be the issue?

I want to run the Workflow also on the Alteryx Server.

Thank you in advance for your help,

DAM

[danilang]

hi @DAM 

Depending on the structure of your data, you may be hitting this limit

Number of characters in a record (excluding Long Text and OLE Object fields) when the UnicodeCompression property of the fields is set to Yes

4,000

Source

Dan

[DAM]

Hi danilang,

It seems that when I refershed the data, the Access DB exceeds 2 GB (although the data that I have it is not so big - aprox 1 mil records). The funny thing is that it worked before.

How can I overcome this? Also, I need an output file that works better, and in the same time to feed a pivot in excel.

Thanks   

[danilang]

hi @DAM 

"the Access DB exceeds 2 GB"  Are you sure that you're updating existing records and not just continually inserting new ones?

"Also, I need an output file that works better, and in the same time to feed a pivot in excel."  You can send your data to excel as well as Access.  But if your process is writing more than 2 GB of data to Access, it's not going to work in excel either.

[DAM]

Hi Again,

In the Output Options I choose "Overwrite Table (Drop)". Excel it's not an option in this case.

[afv2688]

If the size is bigger than 2GB don't bother using access, use an excel worksheet with linked table. This way you can keep more records without a problem.

Access DB spec: https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

[DAM]

I have tried to use excel but the number of records exceed 1 mil.

You are referring to have multiple worksheets and build a pivot from them based on unique field?

[afv2688]

Yes that was my idea, but after it, trying to open the files is gonna put a lot of strain in the program and gonna be difficult to manage it.

Another solution would be to drop the table to an sql DB and use access as a front end, would also go faster.

There are some versions from the Microsoft sql server which are free (if you dont have one)

[DAM]

Can you please provide more details, how I should do it.

I am not sure if I am allowed to install it on my machine.I will give it a try.

[afv2688 · Accepted solution]

Just install the software on your machine. On the output tool select then SQL server and configure the connection. If the connection was a success everything should go smoothly from there on.