Monday, July 7, 2008

How to call GAMS from Access?

Short Answer: invoke gams.exe using the CreateProcess API call. In a practical application much more is needed:
  1. Create a button so user can launch GAMS
  2. Find the location where GAMS.EXE is located
  3. Create unique directory where scratch files can be read and written
  4. Provide a mechanism to write problem data to a GAMS file
  5. Call GAMS, capture log output and show in window, if needed add an interrupt button to stop the solution process
  6. Provide a mechanism to read solution data from the GAMS run
  7. Presentation of solution
Below is an example: it solves the Traveling Salesman Problem or Minimum Spanning Tree Problem against a 42 US city data set. The TSP is solved using a cutting place algorithm coded in GAMS. All the steps described above are coded in VBA. Click to enlarge.


You'll see four windows inside Access. The first window contains some buttons that allow the user to drive the application. The log window shows any progress by GAMS. It also has some buttons to allow the user to interrupt the solution process. The graph is a simple way to display the solution. The right-lower window shows a data table with city related information (name, coordinates etc.)