Write on .mdb file from "Job Queue Entry"

SpikeSpiegel · 2017-09-14

Hi all,

I'm migrating a codeunit from 2009 to NAV2015, that uses automations to update an mdb file from "Job Queue Entry". But I can't found a way to do the same with DotNet variables whith RunOnClient=No (the code is executed from "Job Queue Entry").

In 2009 I have this variables:
Connection Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Connection
RecordSet Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Recordset

I'm working in a virtual machine, then server and client are the same computer.

For the connexion in 2015 I have tried two different ways:
- System.Data.Odbc.OdbcConnection.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
- System.Data.OleDb.OleDbConnection.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

Both works with RunOnClient=Yes, but if I put RunOnClient=No I get the error:
- With OleDb: The provider 'Microsoft.Jet.OLEDB.4.0' is not registered on the local computer. (also with Microsoft.ACE.OLEDB.12.0)
(But I'm in a virtual machine where RunOnClient=Yes works)
- With Odbc: ERROR [IM002] [Microsoft] [ODBC Driver Manager] The data source name could not be found and was not specified

Code for Odbc:
ConnectionString := 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\test\SF02A140.MDB;';
ODBCConnection := ODBCConnection.OdbcConnection();
ODBCConnection.ConnectionString(ConnectionString);
ODBCConnection.Open();

Code for OleDb:
ConnectionString := 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test\SF02A140.MDB';
//ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\SF02A140.MDB';
OleDbConnection := OleDbConnection.OleDbConnection();
OleDbConnection.ConnectionString(ConnectionString);
OleDbConnection.Open();

How can I do this?
Thanks a lot!!

SpikeSpiegel · 2017-09-15

Hi Slawek_Guzek,

After uninstall the office, and install AccessDataEngine2013 64-bit version, the connexion works!.
And how I don't know how to work with 32-bit version, the 64-bit version will be a requirement.
(the AccessDataEngine 2016 doesn't works in my case).

Another thing is that now I can't use RecordSet, but at least it works. I will write the inserts.
thanks a lot!

Slawek_Guzek · 2017-09-14

Hi,

The database is "C:\test\SF02A140.MDB" - if it works with RunOnClient=yes I guess the mdb is on your computer local C drive.

Copy the mdb to the NST's C: drive, into the same c:\test\ folder, and give it a go with the ODBC driver. Or setup a network share and reconfigure the ConnectionString to use the DB from the share,

Slawek

SpikeSpiegel · 2017-09-14

Hi Slawek_Guzek,

I'm working on a virtual machine with server and client on the same windows.
In fact, I can load files from C:\.
anyway I copied the file to a shared folder, but I get the same error.

Slawek_Guzek · 2017-09-14

If you work with server and client on the same box then of course copying the mdb to a network share does not make much sense.

Have you checked folder/file permissions? The service account for the NST should have read/write permissions to your C:\Test and files in it

SpikeSpiegel · 2017-09-14

Yes, the user has full control on this folder.
In my case, the user of the service acount is the same than I'm using with windows (is an administrator).

Slawek_Guzek · 2017-09-14

Maybe try to setup a system DSN, and then point your ODBC connection to use this DSN? (Connection string: DSN=MyAccessDSN;Uid=myUsername;Pwd= )

SpikeSpiegel · 2017-09-14

Ok, I have created a "DSN of system" with the mdb file.
But when I use this DSN (ConnectionString := 'DSN=ACCESSTST01';) I get the error:
ERROR [IM014] [Microsoft] [ODBC Driver Manager] The specified DSN architecture does not match between the driver and the application.

I supose that is because is an 32 bits DSN.
But now I can't create an 64 bits DSN because I have installed the office and AccessDatabaseEngine of 32 bits.
In any case, I can not know if the office/AccessDataEngine will always be 64-bit.

Slawek_Guzek · 2017-09-14

Why you cannot now if the office/AccessDataEngine will always be 64-bit? It should be simpler to know and control as with RunOnClient=No the connection is supposed to be made from a single place, the NST server box, rather than individuall PCs where old clients were run?

The last thing which comes to my mind is trying linking the Access database directly to the SQL Server, and then accessing it through the SQLconnection (System.Data.SqlClient.SqlConnection.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' )

May work unless you have more than one mdb and need dynamically select which one to connect to and update..

SpikeSpiegel · 2017-09-15

Hi Slawek_Guzek,

After uninstall the office, and install AccessDataEngine2013 64-bit version, the connexion works!.
And how I don't know how to work with 32-bit version, the 64-bit version will be a requirement.
(the AccessDataEngine 2016 doesn't works in my case).

Another thing is that now I can't use RecordSet, but at least it works. I will write the inserts.
thanks a lot!

Write on .mdb file from "Job Queue Entry"

Best Answer

Answers

Categories