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


  1. This is really nice. I am just learning the vehicle routing problem -- specifically the classical capacitated VRP. I understand the mathematical formulation, but owing to my lack of experience with a modeling language or a programming language, I have no idea how to practically implement these kinds of problems. Do you have some resources you could recommend to me? Also, would you be willing to share some of your source code/files with me? I am just an undergraduate student looking to learn, no for-profit anything here. Thanks!

  2. I assume the problem here is to translate your mathematical formulation into a modeling language. Usually that should be fairly easy, and you can get help from forums dedicated to GAMS, AMPL etc.

    Note that there are no good, efficient formulations for the VRP. For large problems one would need to use something like column generation or other advanced techniques. Also often meta heuristics are used such as genetic algorithms or ant colony optimization.