"Could not connect to" MS Access database

[julc...@gmail.com]

Hi

My PC crashed and I am now having a problem running AMPL.  I am using 64 bit Win 10, 64 bit Office 2016 and 32 bit AMPL (confirmed by "ampl -v").

I use an MS Access database which holds data imported by AMPL and holds data exported from AMPL.

I can successfully import data from Access to AMPL and run AMPL however when AMPL attempts to return data to Access the following message occurs:

Error writing table RESULTS with table handler odbc:
 Could not connect to "P1Dbase.accdb"

The output line in the AMPL run file is:

table RESULTS "odbc" "P1Dbase.accdb" "Results1":

The AMPL folder includes ampltabl.dll and I have the MS Database engine 2010 installed.

When I execute "display _HANDLERS" I get the following:

set _HANDLERS := tab bit odbc tableproxy;

I changed the output line to include "verbose"

table RESULTS "odbc" "P1Dbase.accdb" "verbose":

and the following output is produced

AMPL ODBC driver, version 20131212.
Calling SQLDriverConnect("DSN=MS Access Database;DBQ=C:\ampl\P1Dbase.accdb")
Failed to connect with DRIVER="MS Access Database"
SQLDriverConnect returned -1
sqlstate = "IM002"
errmsg = "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
native_errno = 0

I have uninstalled and re-installed Office but the problem still exists.

I am at a real loss with this problem.  Any help would be most appreciated

Thanking you, Julius

[Filipe Brandão]

Could you please run odbcad32.exe and send us prints of the tabs "User DSN" and "Drivers"?

Best regards,
Filipe

[julc...@gmail.com]

Hi Filipe

Thank you for your reply.  Here are screenshots as requested.

Thanking you, Julius

[Filipe Brandão]

Did this problem started occurring after a Windows Update? Could you please try with the following table statement that specifies the driver:

table RESULTS "odbc" "Driver=Microsoft Access Driver (*.mdb, *.accdb);ReadOnly=0;DBQ=C:\ampl\P1Dbase.accdb" "verbose":

Best regards,
Filipe

[julc...@gmail.com]

Hi Filipe,

On using 

table RESULTS "odbc" "Driver=Microsoft Access Driver (*.mdb, *.accdb);ReadOnly=0;DBQ=C:\ampl\P1Dbase.accdb" "verbose":

I get the following:

AMPL ODBC driver, version 20131212.

Connection string: "DBQ=C:\ampl\P1Dbase.accdb;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;ReadOnly=0;"

CREATE TABLE `RESULTS ` (`d100` DOUBLE, `d101` DOUBLE) returned -1

sqlstate = "S0001"

errmsg = "[Microsoft][ODBC Microsoft Access Driver] Table 'RESULTS ' already exists."

native_errno = -1303

"DROP TABLE `RESULTS `" succeeded.

The problem only occurred after a crash.  I also had an expected error message on opening some Excel workbooks.  I therefore uninstalled and re-installed Office and downloaded the latest MS updates - but both problems still occurred after the re-install.

Thanking you, Julius

[Filipe Brandão]

Your file may have been corrupted by the crash. Have you tried creating a new one?

Best regards,
Filipe

[julc...@gmail.com]

Hi Filipe,

Are you referring to the database?

Thanking you, Julius

[Filipe Brandão]

Yes, I am referring to the database file P1Dbase.accdb. The file may have been corrupted by the crash or it may be locked. This last error message only indicates that the table already exists, so a new database file should fix this issue. In case this does not fix the problem, do you remember the error message you got when opening the Excel workbooks?

Best regards,
Filipe

[julc...@gmail.com]

Hi Filipe

Yes, that has fixed the problem!

Thanking you very much for your help, Julius