Write on .mdb file from "Job Queue Entry"

SpikeSpiegel

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

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

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

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

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

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

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

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

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

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!