community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Error writing dates to .accdb using 64 bit access database engine

Our company is moving to a 64 bit office deployment, which means I have to upgrade to the 64 bit access database engine (2010) for excel to continue to write data to access via ODBC; in tandem, this means I need Alteryx to play nice with the 64 bit driver as well when writing to access. The issue is that it can't write more than 1 row of data at a time without errors if it has a date field. Here is my test workflow (attached) which has 2 rows x 1 date column:

Capture.PNG

 

The error is :

 

Error: Output Data (16): DataWrap2OleDb::SendBatch: Microsoft Access Database Engine: Multiple-step OLE DB operation generated errors. 
Check each OLE DB status value, if available. No work was done. Insert into `test2`(`Field1`) Values (?)

 

Note that this error goes away when using the 32 bit access database engine.

 

I'm looking for a "suitable" workaround. I have various constraints:

1) I need excel to be able to write to access. It seems to me like the only solution is the 64 bit access database engine (2010) but I'm game for any alternate solutions.

2) I need my users to be able to select accdb from the output and write like normal. I don't want people to have to re-write their alteryx modules to use a special macro, etc.

3) I don't want to write out dates as integers/strings. I imagine this would be a lot of rework on both the alteryx and access sides.

4) I don't want to keep switching between the 32 and 64 bit access database engines. That's what I'm currently doing.

 

I realize no "suitable" workaround may exist (ie the 64 bit engine just won't work with alteryx and there's no fix given my constraints). Any tips on debugging this would also be appreciated as I can't even trace excel/alteryx when they write to access.

Pulsar

Hi @patrick_digan,

 

I ran your workflow in my 2018.4 Alteryx and got the same error, but when I ran it in the 2019.1 Beta, I didn't. Can you check if you get the same in the 2019.1 Beta?

 

patrick_digan.png

Fireball

Hi @patrick_digan,

 

Been looking at your problem and one interesting I noticed is that, although writing one record does work, when opening the table in access the data type of the written field is ShortText, not Date. This made me think that maybe it is a conversion problem between Date formats used within Alteryx and Access.

 

I still haven't been able to find a way yet to write dates into an Access table but I did find that exporting the date as pre-formatted strings that Access likes does work. Again these appear in Access as ShortText fields.

 

I know you don't want to output strings but hopefully, this can be of some help in your further diagnosis.

 

Apologies, it seems the database I was looking at was after I had altered your workflow, the single record export does work as intended.

 

Sam :)

Fireball

Hi @patrick_digan 

 

I got the multiple date output working but by using an ODBC connection. Setting up a User DSN in the 64-bit ODBC administrator, using the 2010 64-bit driver and selecting the database to output to. I'm not sure whether this breaks your third stipulation or not as I'm not sure I'm interpreting it correctly or not.

 

Capture.PNG

 

Sam :)

@SamDesk  I may be able to make that work. I had forgotten about that path. I think I used something similar to read/write to xlsb before Alteryx provided native support.

 

@DavidP hmmm.... I wasn't able to get it to work with 2019.1 Beta. I have 2018.4.5.55718 non-admin and 2019.1.0.56868 admin and both produce the same error on the same machine. For the sake of the argument, I also tried non admin 2019.1.0.56868 also without any luck. They all produced the error for me.

Pulsar

That weird @patrick_digan, I ran it on 2019.1.0.56868 admin.

 

At the risk of sounding stupid - do I have to have an Access driver installed? I don't use Access, so I don't have the driver.

 

I have 2018.4 and 2019.1 on 2 different machines.

 

 

@DavidP  Not stupid at all. It's my understanding that in order to write to .accdb files using the output tool like normal, you need the Microsoft Access Database Engine (2010) as seen here in my programs and features list:

 

Capture.PNG

 

Highlighted
Pulsar

Ah, I do have it, but slight difference in the file size!

 

access db engine.png

Labels