TOPIC:

When testing the import for a new customer I get this error in the development database.
The problem is in the new frontend.  I've linked the data 'sql' to both dev and prod.
The error appears to be here in the sql heater.


This is what I get when I test the connection.


I've compared the two side by side and all of the settings are the same as well as the references.

Thanks, JD

I did see your response to the issue last year. Both of these databases are on the same computer/server.

Hi JD,

You wrote both of the Access Databases are on the same Computer. Did FlowHeater also run on the same computer or connect you to the database over the network from different computers?

I guess this issue based on MS Office 64-Bit installation and/or Microsoft Database Access Engine. In this case, you have to install the FlowHeater 64-Bit (x64) version. For more information please have a look to the Access Adapter documentation.

Thanks Robert,

Not only are both access databases on the same computer but Flowheater resides they as well.  We recently upgraded to Access 2013 and I've never seen an issue like this. The linked tables for dev and prod are on the same sql server.  What difference would Flowheater 64-Bit (x64) make?  We're looking to purchase it soon anyway just curious.  I'll look at the Access Adapter Information but other than the linked tables they're a mirror image of each other. 

JD

Hi JD,

There only two differences between 32-bit (x86) and 64-bit (x64).

1. You could access more than 2 GB memory. Note: This only needed for the InMemory Adapter  or in case you run definitions with “Memory” mode.
   
2. You could use 64 bit ODBC / OleDB  driver. In case your data source only delivered 64-bit drivers, you have to use the 64-bit FlowHeater version. In case you install MS-Office as 64-bit version, you also need the 64-bit FlowHeater version because this version only supports 64-bit driver. On the other hand, the 32-bit office needs the 32-bit FlowHeater version.

You can try FlowHeater V4 it without installation. Download both versions (32/64 bit) and extract the two ZIP Archive into different folders. Now you can start FlowHeater.exe from the BIN Folder and open a copy of your old V3 Definition. Just try to connect your access database.

Note: It´s possible to install and use both version (32/64 bit) in parallel.

Sorry for the delayed response…in short we upgraded FH to Version 4.1 and that took care of the ‘Unrecognized Database Format’, still don’t
understand why, I digress.  But, we have a new problem that seems to be in the same area.  We have spent a lot of time trying to figure this out before coming back to you.

Response from our network manager: ************************************************************************************************************************
We are using 64 bit V 4.5 Flowheater now.   I loaded ODBC Driver 13 for SQL Server, which I downloaded from Microsoft and verified to be 64bit. The version of Office is 2013 and verified to be a 64 bit version. 
*************************************************************************************************************************************************************************************

The problem is with the SQL Heaters.  I’m getting the attached error about 80percent of the time.  What I’ve come to realize if it works the first time I open FH it will work every time.  If it fails the first time it will continue to fail.  And, when it fails and I test the data connection it is successful but the SQL Heater will continue to fail.  We’ve done everything we can think of.

Thanks for any help you can give us, JD

Hi JD,

The reason why is within a 64-bit application you can just use 64-bit ODBC / OleDB drivers and within a 32-bit application you can only use 32-bit ODBC/OleDB drivers.

If you install MS Office/Access as 64-bit only the 64-bit Access drivers installed on your computer. The problem is that Microsoft doesn´t support to install both drivers (32/64 bit) on one physically computer.

Note: This is just a restriction for Access. Most  ODBC / OleDB drivers could be installed in both versions.

Your new problem) Why you´re using the SQL Server ODBC driver? I guess something is wrong with the 64-bit ODBC driver. You can use the build in SQLSever Adapter instead.  This is a more efficient way to connect to your SQL Server database. May I ask you to try this one and report what happened?

Robert,

We’ve updated all of the drivers and reloaded FH. We’re running SQL Server 2014 version 12.0.6.6329.1.  I cannot use the SQL Server adapter because I’m writing data to an access temporary table on the users desktop during the import.  It's strange that sometimes it works but mostly does not.  I’m sorry to drag this on but we’re in a real pickle with this one.  Any suggestions would be greatly appreciated.  We rely solely on FH for importing and export data into our WMS, FlowHeater is a critical part of our operation.

Thanks JD

Hi JD,

Before I digging deeper into analysis about the ODBC SQL driver issue I just have to understand your complete definition. As far as I know you want´s to export data from SQL Server 2014 to MS Access (.accdb) Database. Is that right or something missing?

In my understanding, it should be no problem to use on the READ side the native built-in SQL Server Adapter (not ODBC) and on the WRITE side the native built-in 64-bit Access Adapter . Did you get an error message when you do so or what´s the technical reason why you´re using the ODBC Adapter to connect you´re SQL Server database?

A few more questions and/or test to solve the issue)

1. Just for test, please try to remove the certain SQL Heater  from your Definition.  Does the Definition always run without errors?
   
2. Perhaps the SQL Server ODBC cannot handle the used default FlowHeater database transaction handling. Please try to disable database transactions in the ODBC Adapter  “Advanced” settings like the screenshot below.
   
3. With Version 4 you could create more processing steps into one Definition. Please try to read your table “tblSKUVM” with a pervious (first) step into an internal InMemory Adapter  table and configure the SQL Heater  (second step) to use this internal “InMemory” Table instead.

Disable ODBC database transactions

No, I'm importing data into a temporary access table.  The InMemory sounds tempting but we get some messed up files from our customers from time-to-time so I need to give the users a way to cancel the import prior to writing to SQL tables.  I write to SQL via VBA in Access.  The SQL Heater that's failing is looking for the SKU ID in a SQL view linked to the access database frontend.

I added the ODBCAdapter and pointed the SQL Heater to the adapter and set it up as you asked and I get this message. 
It successfully finds the first SKU ID then stops.

And I did remove the SQL Heater and runs fine every time.

Ok, now I guess the linked SQL Server table is responsible for the issue.

You can solve this as I suggested, attached you´ll find a short example.

First processing step) READ the lookup SQL Server table into an InMemory table and use this one in the second step for the database lookup

Second processing step) READ your desired data source (whatever) and use the InMemory table instead of the SQL Server linked table for the SQL Heater and import the data to the temporary Access database.

Robert, Thank You Sir

That works… as always you find a way.  I also added a filter to the ODBC Adapter on the Read side in step (1 of 2) to lessen the memory used creating the InMemory table.

JD