This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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:
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.
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.
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.
@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.
@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: