Problem with AMPL ODBC Handler

[Mohammadi, Abdollah]

Dear Madam./Sir.

We have AMPL software installed in our lab in UNC Charlotte SEEM department and I have problem with reading tables from EXCEL using AMPL ODBC handler. Whenever I run the ".run" file I receive this error:
Error reading table I with table handler ODBC:
"ODBC is not available."

I have added "ampltabl.dll", "ampltabl_64.dll" and "tableproxy32.exe" in the folder in which "ampl.exe" is located, but yet I receive this error.

Could you please help me with this problem?

I should mention that for small size problem I can read from EXCEL using the student version of AMPL with the same code, so the code is set correctly!

Thanks.
Abdollah.

[victor.z...@gmail.com]

Dear Abdollah,

Usually this error occurs when trying to use 64-bit AMPL with 32-bit ODBC drivers as discussed in this thread: https://groups.google.com/d/msg/ampl/NuhpFZVsmBw/aZGDZyE5on4J. To fix it you should either switch to 32-bit version of AMPL, install 64-bit ODBC drivers or use a table proxy.

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+unsubscribe@googlegroups.com.
To post to this group, send email to am...@googlegroups.com.
Visit this group at http://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.

[abed.moh...@gmail.com]

Dear Victor,

   Thanks for your reponse.

   I have 64-bit AMPL and 32-bit Office installed on my PC, and copied all the required files to the same folder as "ampl.exe". Also changed "ODBC" to "tableproxy" "odbc" in my code. But yet the same error continues. Does it mean that the ODBC doesn't work and I should change my office or AMPL version in order to make it work?

On Monday, March 9, 2015 at 8:08:05 PM UTC-4, Victor wrote:

Dear Abdollah,

Usually this error occurs when trying to use 64-bit AMPL with 32-bit ODBC drivers as discussed in this thread: https://groups.google.com/d/msg/ampl/NuhpFZVsmBw/aZGDZyE5on4J. To fix it you should either switch to 32-bit version of AMPL, install 64-bit ODBC drivers or use a table proxy.

HTH,

Victor

On Mon, Mar 9, 2015 at 4:00 PM Mohammadi, Abdollah <amoh...@uncc.edu> wrote:

   Dear Madam./Sir.

   We have AMPL software installed in our lab in UNC Charlotte SEEM department and I have problem with reading tables from EXCEL using AMPL ODBC handler. Whenever I run the ".run" file I receive this error:
Error reading table I with table handler ODBC:
   "ODBC is not available."

   I have added "ampltabl.dll", "ampltabl_64.dll" and "tableproxy32.exe" in the folder in which "ampl.exe" is located, but yet I receive this error.

   Could you please help me with this problem?

   I should mention that for small size problem I can read from EXCEL using the student version of AMPL with the same code, so the code is set correctly!

Thanks.
Abdollah.

--
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.

[victor.z...@gmail.com]

Could you add an option "verbose=1" to the table declaration that causes the error as in

  table dietFoods "ODBC" "diet.xlsx" "Foods" "verbose=1": ...

then rerun the AMPL code and send us full output together with the table declaration?

Regards,

Victor

[haji...@gmail.com]

Dear Victor,

I have encountered the exact same issue and found this post while looking for a solution. If you are still following this post, could you help me out too?

I typed the code:

-------------------

table dietFoods IN "tableproxy" "odbc" "diet.xls" "verbose=1":

FOOD<-[FOOD], cost, f_min, f_max;

read table dietFoods;

---------------------

Then I got the following output. 

---------------

Verbose table-handler output:

AMPL ODBC driver, version 20151130.

Calling SQLDriverConnect("DRIVER=Driver do Microsoft Excel(*.xls);DBQ=C:\Users\AMPL_practice\diet.xls")

Connection string: "DBQ=C:\Users\AMPL_practice\diet.xls;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

SQLNumResultCols returned -1

sqlstate = "S0002"

errmsg = "[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'dietFoods'.  Make sure the object exists and that you spell its name and the path name correctly."

native_errno = -1305

---------------

Sincerely,

Hajime

[haji...@gmail.com]

Dear Victor,

I just realized I have not defined the "range" on my excel sheet after copying the content from the original diet.xlsx. I actually did not even know I had to. I would imagine a lot of users who are not familiar with database (and so that prefer to read data from Excel) are encountering the same issue, so I hope the instruction explicitly explain how it is referring to the range within a sheet.

Anyway, sorry for bothering. AMPL is working really great for me.

Sincerely,

Hajime

On Wednesday, March 11, 2015 at 12:33:08 PM UTC-4, Victor wrote:

[danielcas...@gmail.com]

I'm having the same issue...Can you explain how to define the "range" you talked about.

[AMPL Google Group]

See for example this explanation:
https://www.lifewire.com/define-a-named-range-in-excel-3123627

--
Robert Fourer
am...@googlegroups.com

{#HS:750735653-33588#}

Visit this group at https://groups.google.com/group/ampl.

[Abdollah Mohammadi]

Problem solved?