Topics Search

Linking with IBM AS400(iSeries) Tables

Linking with IBM AS400(iSeries) Tables
Views: 141
We can convert IBM iSeries (AS400) DB2 Table into dBase Format, download and Link or Import into MS-Access Databases. The downloading procedure must be created and run from AS400 Menus. If it requires several steps before the output file being transferred to local drive then it can be automated with Macro Scripts. The key strokes can be recorded and modified in such a way that the target file goes to a specific location on the Local Machine with a predefined name that remains linked with the Microsoft Access Database.
Sponsored Links:

More topics


Create DSN for AS400(iSeries) in Access on the fly

I managed to find this nice code that creates a DSN for a SQL server table on the fly for when a user opens a form of the application I have created in Access 2007.

Dim stConnect As String

If Len(stUsername) = 0 Then

stConnect = "Description=SQLDSNName" & vbCr & "SERVER=MySQLServer" & vbCr & "DATABASE=MyDB" & vbCr & "Trusted_Connection=Yes"
stConnect = "Description=SQLDSNName" & vbCr & "SERVER=MySQLServer" & vbCr & "DATABASE=MyDB" & stDatabase & vbCr
End If

DBEngine.RegisterDatabase "SQLDSNName", "SQL Server", True, stConnect

Which works perfectly, however there are other tables in the Application that also pull from a live AS400 table.
I tried editing the code above for the AS400 table but unfortunatly i've had no joy so I wonder if anyone knows of any code similar to above that would automatically create a ODBC DSN for a AS400 table?
I would prefer not to use a DSNless conenction

Date columns linked to AS400 table not formatted as dates

I have a table linked to an AS400 table and created a query to use as a basis for some reports. The problem is the AS400 table does not have a date column (field), but rather has 4 separate fields, one representing the century, "DATCC", one representing the year, "DATYY", one for the month, "DATMM", and the 4th representing the day, "DATDD". How can I combine the four fields into one that can be formatted correctly as a date field in Access? I'm new to Access and not familiar with SQL at all

Update Data in Table form ODBC Database

I have a table in Access that I have imported from an AS400 using a Macro, here is the Macro that I used;

Transfer Type: Import
Database Type: ODBC Database
Database Name: ODBC;DSN=mvxr10;TABLE=DATA09
Object Type: Table
Source: DATA09
Destination: tbl_DATA09
Structure Only: No

Now I have the table with the data I have added relationships so other tables can get data from this table, however in the AS400 this data can be updated, is there away of updating the table data using a macro without having to delete the table.

If I re-run the above Macro it just creates a new table which is no good.

Linked servers

I'm looking for some direction in creating new Ms Access database that would have linked tables to MSSQL 2005 ent. and in addition would be able to take advantage of DB2 database that is linked in MSSQL. What I would like to do is look up some values in linked DB2 tables while user is creating record in MSSQL to validate it and then at the end of the batch(bunch of related records) I would "push" that batch to temporary processing table on Mainframe where DB2 folks would do something with these batches/records and send me a response back. I have created linked server to DB2 using IBM OLE DB provider for DB2 (IBMDADB2) and that is working, what I'm not sure of doing is linking DB2 tables in MS Access DB.

I'm trying to all this in order to avoid installing DB2 Connect software on each machine and configuring it on each machine that would need to use this database. I would rather that server that has MSSQL talks to Mainframe instead each individual computer.

Security for Access Database

My division is having a problem with access database security. Weekly someone has access the databases and deleted tables and linking. Typically, there is a front-end and back-end database versions.

The linking between the databases are getting deleted and tables are being deleted even subform linking are being removed.

What can I do to resolve this issue? We have hidden the Database Windows, but still no success.

Access Query Issues

I'm currently have some frustrations with a database query I am trying to construct. The database is being used as a means to enter data that will be later transferred to a statistical analysis program.

For this database I have consists of five tables, all with the same parameters on the primary key. Four of the five tables are used for data entry in various forms while the fifth haspreloaded data on what relates to the previously mentioned tables.

So far I have no trouble linking the four tables used for data entry together. I'm using a one-to-one relationship between the primary keys. The issue is with the fifth table linking to anyone of the four tables used for data entry.

I'm having no luck with designing and running a query that matches up correctly.

Linking/Matching multiple IDs

Right now I've got a little project going on where I have to make a commercial loan database generate automated reports at the end of the month that lists the new applications with different information on them.

I've gotten almost everything done except for linking the applicants name to the report in my query.

I'm pulling data from 6 tables which are linked by applID. My problem is none of these tables that contain applID have the applicants name. The table that DOES have the applicants name is not organized by applID; its organized by borrowerID within my Borrower table, which does not contain any applID.

I have one other table who's purpose is to put an applID to a borrowerID.

I need some help in somehow linking those two within my query.

Problem linking two tables (relational)

I am having a problem with linking two tables in order to make a relational database. My problem is that whenever I change an information in one table, it does not seem to change in the other.

Create or Append Table from large recordset???

I am currently using VBA to query data from an AS400 system.000 records into an ADO recordset. I then add them to an access table by looping through using something like the code below.

I am wondering if there is a more efficient way to do this given the large number of records. (Also linked tables are not an option. )

Linking tables

I manage 20 facilities for a non-profit agency. I want a database that tracks a variety of information for the facilities, so I want different and various tables for each kind of data (supervisors of each facility, utility providers of each facility, fire equipment located at each facility, etc.) I have a table that contains the cost center code (a unique number field), which is my primary index, and the facility description (a unique alpha field.) I want all of the other tables to contain these two fields, and if a change is made to a record in either of these fields in the main table, I would like that change to be reflected in the other tables. I have tried linking them in the relationships window, but that doesn't yield the desired effect.