Topics Search

Linking with IBM AS400(iSeries) Tables

Linking with IBM AS400(iSeries) Tables
Views: 145
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:

Similar posts...


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

Create DSN for AS400(iSeries) in Access on the fly Icon
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

Linked servers

Linked servers Icon
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

Security for Access Database Icon
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

Access Query Issues Icon
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

Linking/Matching multiple IDs Icon
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)

Problem linking two tables (relational) Icon
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.

Appending data to a table from Excel, with odd layout

Appending data to a table from Excel, with odd layout Icon
I need to append data from a spreadsheet (many spreadsheets over time) into an access table. There are several aspects of this I am not sure how to do.

First is that the spreadsheets have many tabs and within each tab are “tables” that contain the data. Thus, I believe that I need to designate named ranges for where to get data. And I believe the idea of linking to Access is out of the question considering the issue of multiple “tables’ within tabs in the spreadsheet.

(And I really don’t want to think about linking and then unlinking tables via code.

Maybe I just stated two problems, the idea of named ranges and the seeming unavailability of linking the tables to access.

Second, some of the “tables” in Excel have the field names vertically in a row with the data to the right. I have never tried to append data that was not in the traditional layout of field names spread horizontally in a row with the data below it. Is there a way to deal with that in the append query?

Create or Append Table from large recordset???

Create or Append Table from large recordset??? Icon
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. )

Linked Table Manager - failes on more than 3 tables at a time

Linked Table Manager - failes on more than 3 tables at a time Icon
Access 2007 - linking Access 2007 Be on network or C:\ drive folder

Linked Table Manager, Select All Choose a Be (back end tables) 2007 Access db

Problem: each and every table asks for the user to re-select the Access 2007 Be path again, and again, and again.

However: Instead of Select All, check 3 to 8 tables at a time and it will update just the 3 to 8 all at once (as it normally should).

Earlier this week, it behaved properly dozens of times and updated all tables (select All) with a single click.
Not using ODBC, just directly linking Access 2007 to Access 2007.

Linking two tables

Linking two tables Icon
I want to create query by linking two tables.And to create data entry marks form other table. I want to get automatically student name and his grade" when you just enter student's number on top of form.

Simply I want to get displayed students details on the form just entering his student number on the top of form.