DataWarp2OleDb::MSAccess Issue

Question

Hi, I have built my workflow. It runs beautifully the first time and saves my data via the output tool to Access DB 2007, 2010, 2013, 2016 (*accdb) with no problem if I set it up as a new connection.  However, the next time I run it, it fails and gives me the following error each time.  Therefore, every time I have to set up a new connection, choose the same DB, and give the same table name, and then it works.  This would not normally be a problem but... this dataset feeds into a PowerBI Report and every time loses the connection.

I have provided a screenshot.

here is the full error:

Error: Output Data (1284): DataWrap2OleDb::SendBatch: Microsoft Access Database Engine: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.\3049 = -78972086
Insert into `ETL_Marketable_Contacts`(`ContactID`,`Eloqua Contact ID`,`Email Address`,`Company`,`ZI Company ID`,`ZI Parent ID`,`ZI Parent Name`,`ZI Ultimate Parent ID`,`ZI Ultimate Parent Name`,`D&B DUNS Number`,`D&B Doing Business As`,`Website`,`CRM Lead Reject Reason`,`SFDC Account ID`,`contact_sk`,`ETL_JobTitle`,`ETL_MgmtLevel`,`ETL_Country`,`ETL_Industry`,`ETL_Region`,`domain`) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

The last field in the dataset is domain.

Any ideas?

Alteryx_Error.png

CherylWalsh71 · Answer

Thanks Dan.  I have tried all of the options in the configuration window under # 3 - always getting the same result. The only difference is they all error at different timeframes.  I do not have the database open in any other app.  Starting to wonder if there is the different output that I can use besides access or PBI (corporate security won't approve the use of this one) that I can use as an input for PowerBI.  Any thoughts? I am a new user to PBI and generally, my outputs from Alteryx have been .xls summaries, reports, .csv, etc.  But now working with "Big Data" and need to do visualizations in PBI.

danilang · Answer

Hi @CherylWalsh71

I haven't used MSAccess in long time, but here are some things to investigate

* Is the file opened by another application/user?  Dropping a table requires elevated permissions so having the .accdb file open may prevent that
* Have you tried using Delete Data and Append?  Should require fewer permissions than dropping the table.
* You can try putting the Output Data tool in a batch macro and use the control parameter to overwrite the connection string before the data is written.  Technically this should be equivalent to deleting and recreating the Output Data tool in the main workflow, but it may not solve your problem

Dan