ODBC Import

[razchip]

I am not a programmer and have ran into a problem trying to import an ODBC table into Access 2000. If I manually go out and get it, it works, if I try to put it in a macro it bombs (can't find an installable ODBC driver). I'm not sure what to do next. Also, I have to add a user and password to access this file. Has anyone seen code that handles this type of transaction since I can't put that in a macro? Thanks.

Thanks for the help.
Greg

 

[vbajock]

What is the native format of the data?

 

[razchip]

The data is DB2. I hope I'm answering your question properly, again, this is something I've never worked with before.

Thanks for the help.
Greg

 

[vbajock]

IBM mainframe DB2 data?

 

[razchip]

The data is generated from an AS/400.

Thanks for the help.
Greg

 

[vbajock]

Dude - I have to split. If it is a DB2 database off a mainframe your going to need some system admin support. If it is PC DBase your talking about, you have to get the Borland engine from Borland and install it on the PC. Go to their website at Borland.com. They charge a license fee, which is why it is not included with Access.

 

[vbajock]

Ditto on the system support for as/400 db2. Bye

 

[pvted]

The most often used PC application used to connect to the AS/400 is IBM's Client Access (or the somewhat newer Client Access Express). This program includes an ODBC driver which I use quite frequently to retrieve data from the AS/400.

Tables linked using the Client Access ODBC driver can be queried just like regular Access tables. I would recommend setting the recordset type to snapshot (under query properties)as long as all you are trying to do is retrieve information.

Queries written on linked AS/400 tables (or reports based on those queries) can be opened using macros just like any other query (or report). You will be prompted for your AS/400 user ID and password if you have not already established a connection to the AS/400. It is possible to pass a connection string using code in order to avoid the user ID and password prompt but security minded folks would frown upon that.

 

[razchip]

Do I have to register Client Access to get this to work. This is all new to me, so I'm learning by experimentation.

Thanks for the help.
Greg

 

[vbajock]

Just a side question: does the IBM Client Access ODBC driver allow write access to the AS/400 or is it just a read only driver?

 

[razchip]

We use Powerlock for security on our AS/400, I can get to the files I need, but, I believe I have read only authority. If I need write rights, I'm sure I can obtain them.

Thanks for the help.
Greg

 

[vbajock]

It's been a while since I did any AS/400 work - in the pre-ODBC days we used to use a product called Remorra, which essentially gave you RPG-like commands in a VBA format. I currently have an opportunity for an AS/400 db2 contract, so thanks for the info.

 

[pvted]

In response to Greg's question from the 16th:

"Do I have to register Client Access to get this to work?"

If you're asking if you have to purchase IBM's Client Access product in order to connect to the AS/400 via ODBC, the answer is no. There are other products from companies such as NetManage which provide an ODBC driver for DB2 on the AS/400 (I believe). I'm pretty sure that none of them are cheap.

Check with your Information Systems department to see if they can give you Client Access Express.

If you have Client Access loaded on your system then you still do have to create a data source in the ODBC Administrator before you will be able to get at the tables on your AS/400. The process of creating a data source basically amounts to telling it that you want to add a new data source using the Client Access ODBC driver.
On the dialog box that opens up there are several tabs you can choose - you need to provide information on 3 of these tabs (General, Server, and Translation).
-On the General tab you will need to assign a name for your data source (your choice). Also on this tab you will need to identify the name of your AS/400 system.
-On the Server tab you will need to provide the name of the library (or libraries) where the tables you want access are located.
-On the Translation tab I suggest that you select the option to translate CCSID 65535. This might save you some grief in the future.

Once the data source is created you will want to link your AS/400 tables within your Access database so that you can query them using the familiar tools within Access.

Hope this helps,
Ted

 

[razchip]

Thanks, I'll give this a try today.

Thanks for the help.
Greg