odbc table handler problem

[Timo S.]

Hi,
I have just begun to learn how to use Excel spreadsheets for data input and output in AMPL, but i have a problem with the odbc table handler. Maybe you can help me.

First my system:
-Windows 10 Edu 64-bit
-option version 'AMPL Student Version 20150415 (MS VC++ 6.0, 32-bit)\';
-MS Office 2016 Edu (ProPlus) 64-bit

What works:
-reading a .xls file
-reading/writing a .tab file

What does not work:

a) writing a .xls file

I use a litte test.run script for testung:
set Iteration := {1..5};
param cost {i in Iteration};
let {i in Iteration} cost[i] := round ( Uniform (0,500) );
table test1 OUT "odbc" "test2abc.xls" : Iteration <- [Iteration], cost;
write table test1;

The following error occurs:

ampl: include test.run;
Error at _cmdno 2 executing "write_table" command
(file test.run, line 5, offset 189):

        Error writing table test1 with table handler odbc:
        DROP TABLE `test1` failed.
        Is another application using "test2abc.xls"?

But the file does not exist, so it cannot be in use. Even if i create an empty test2abc.xls the error occurs. Excel is not running.

b) writing/reading a .xlsx file

Same script. The following error occurs:

ampl: include test.run;
Error at _cmdno 2 executing "write_table" command
(file test.run, line 5, offset 190):

        Error writing table test1 with table handler odbc:
        No suitable driver found.

I remembered to use tableproxy because of 32 bit AMPL and 64 bit Excel: So i downloaded it from http://www.ampl.com/NEW/TABLEPROXY/tableproxy64.mswin64.zip
Changed the table row in my script to:
table test1 OUT "tableproxy" "odbc" "test2abc.xlsx" : Iteration <- [Iteration], cost;

ampl: include test.run;
Error at _cmdno 2 executing "write_table" command
(file test.run, line 5, offset 203):

        Error writing table test1 with table handler tableproxy:
        Error with proxy table handler:  could not load "ampltabl.dll".

But ampltabl.dll is in the AMPL directory. The same error occurs when i try to read a .xlsx file.

Any suggestions?

[Victor Zverovich]

a) The most likely cause for this error when writing an .xls file is the read-only attribute which seems to be the default in some versions of MS ODBC driver for Excel. Please see this post for the solution: https://groups.google.com/d/msg/ampl/2iwU4vzNlbY/VHrX8AFMWC0J

b) The error indicates that you don't have an ODBC driver capable of reading .xlsx files installed. To fix this install Microsoft Access Database Engine 2010 Redistributable:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

HTH,

Victor

--
You received this message because you are subscribed to the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.
To post to this group, send email to am...@googlegroups.com.
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.

[Timo S.]

Hi Victor,

a) I have manually created a test.dsn like you suggested in the other thread. This works! Thank you

b) I have installed the MS Acces Database Engine 2010 Redistributable. But I sillt get the error message:

ampl: include test.run;
Error at _cmdno 2 executing "write_table" command

(file test.run, line 12, offset 428):

        Error writing table test1 with table handler tableproxy:
        Error with proxy table handler:  could not load "ampltabl.dll".

I tried 32 and 64 bit odbc drivers.

Any further suggestions?

[Victor Zverovich]

The error indicates that ampltabl*.dll is missing. You should have both ampltabl.dll and ampltabl_64.dll (available from http://www.ampl.com/NEW/TABLEPROXY/) if you are using tableproxy. However, if you install Microsoft Access Database Engine 2010 Redistributable compatible with the version of AMPL you use (both 32-bit or both 64-bit) then you will not need tableproxy.

HTH,

Victor

--