Microsoft Access Database Driver

[Jason Au]

My company runs OpenVMS on HP Alpha Server. I need to write a script to port data from VMS system to MS Access database. Is there any API avaliable, and in what language? Any suggestions will be appreciated.

[Hein van den Heuvel]

>> Is there any API avaliable, and in what language?

Ah, you must not have been with OpenVMS too long, otherwise you woudl surely know that OpenVMS API's can be called from any of it's dozens of programmign languages available.

Welcome to the HP OpenVMS ITRC Forum.

There are several ODBC packages avaibale for OpenVMS.
Just google around for: +openvms +odbc

Traditional choices are Attunity and ConnX.
Serveral more are available.

>> need to write a script to port data from VMS system to MS Access database.

I think you should probably NOT use an ODBC API solution.
I suspect it is better to just create flat files or CSV/TSV files from the (indexed?) RMS file data, FTP them over and use a standard Access bulk import tool.

The tools/companies mentioned will also have facilities to create odbc/microsoft data definition from OpenVMS description (CDD, Basic MAP, Cobol LIB, whatever...)

If you have a further, detailed, question then be sure to come back after looking around some more for existing advise.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Hoff]

When you're migrating from or accessing data from a hierarchical file and from an application built on record structure and RMS over into a relational database, you need create the code that knows the mapping from the RMS contents over to the relational database, and that can generate the XML or other code as part of reading in the input data source.

You'll end up writing a conversion tool, in other words.

You're performing a conversion from RMS into XML, or into the surprisingly evil CSV, or otherwise. There's just no way around this step, though cases where the data on OpenVMS is stored with CDD/Repository definitions (rare), or within a relational database on OpenVMS can be somewhat easier.

If you are not seeking a conversion but are seeking on-line and in-place remote access, ODBC and JDBC clients and servers are available for various configurations and file structures, and there are some pointers to some of the options in the OpenVMS FAQ www.hoffmanlabs.com/vmsfaq -- but even with these tools, you're still going to have to do some mapping and some code spelunking, and particularly if the data is stored in RMS files.

I've posted up a C API into basic RMS over at the new website a week or so back; C code that might give you some idea of what you're in for here if this is a data migration from RMS: http://64.223.189.234/node/595

Stephen Hoffman
HoffmanLabs LLC

[Robert Atkinson]

Jason,
given the potential costs of Windows-to-VMS interface software, I would question whether you need a permanent connection, or as my colleagues have said, you should simply extract the data from the RMS files into an importable format.

One other point I would mention. I would steer away from using Access to store the information. If you're thinking about installing an interface API, then I'm guessing you must be looking at moving a fair amount of data, and Access is not brilliant once you go to multiple users.

The way I would do this is to store the data in MySQL instead, which is an extremely good database platform, and has the advantage of being free.

You can still use the reporting power of Access, but use MyODBC to connect Access to the MySQL data, assuming you're using Access for your reports.

Another benefit of doing it this way is that you could install the MySQL client on the VMS system, and use PHP (or Perl?) to read the data from the RMS files, and write it into the remote MySQL database in a single step.

If you really wanted to harness the power of VMS, you could even create the MySQL database on your VMS platform, and configure Access to look at that, giving you stability, performance and reliability for you all-important data.

Rob.

[Maurizio Rondina]

I had the same problem, and solve it very well and simply.

I buy the Easysoft ODBC RMS Gateway for OpenVms (available both IA and AXP, few MegaByte of a Kit server side, a Windows Client DSN driver and documentation), and now i can dinamically view (and update with privileged account) RMS data directly from Microsoft Access, with OpenVms User authentication and security. You can access RMS Data with SQL92 standard language, from any programming language. And from Excel (import ODBC source), and Access (both import and sincronize)

The product had a good documentation,i simply to install and configure and had very efficient technical support.

It open several development opportunity.

Maurizio.

[Andy Bustamante]

How do you store data on the VMS side?

Oracle and Oracle RDB include ODBC drivers. Advanced Server (aka Pathworks) allows you to map a network share from a Microsoft client to a VMS host, most Alphaservers included a hand full of client licenses.

Andy

[Maurizio Rondina]

reply to Andy.

With Easysoft ODBC driver (whose purchase cost, is very much lower than Oracle), Advanced Server in not necessary. This ODBC gateway works directy on the follow TCPIP Service.

$ tcpip show service easysoft /full

Service: EASYSOFT
State: Enabled
Port: 7777 Protocol: TCP Address: 0.0.0.0
Inactivity: 5 User_name: EASYSOFT_SQL Process: EASYSOFT
Limit: 9999 Active: 0 Peak: 1

File: EASYSOFT_SQL_SYSTEM:RUN_SERVER_UCX.COM
Flags: Listen

Socket Opts: Rcheck Scheck
Receive: 0 Send: 0

Log Opts: None
File: not defined

Security
Reject msg: not defined
Accept host: 0.0.0.0
Accept netw: 0.0.0.0