Linking with IBM AS400(iSeries) Tables

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:

Related 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"
Else
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.


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


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.


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.


RecordLocks Property
I created a database (frontend and backend) that links to a SQL database and an AS400 database. There are some linked Access tables and a few local tables (in the frontend) that allows users to customize their environment. I have all the queries and forms local (in the frontend).

This database will replace a different Access database (that I did not write) that consistently locks everyone out . forever. In order to use it again, they have to replace it with a backup, which often means losing data.

My question is . do I need to change the default RecordLock property from "No Locks" to "Edited Records" to keep from having this problem, or will having everything but the linked tables local solve this problem? Is there any risk in having everything but the linked tables local


Appending data to a table from Excel, with odd layout
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?


Linked Table Manager - failes on more than 3 tables at a time
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
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.


Link to FoxPro tables using OLEDB
I am able to "Get External Data" using OLEDB provider for Visual FoxPro in Excel 2003 but what I need is to do that from Access.

But Access only shows native or ODBC connections for Linking or Importing tables.

I cannot use the ODBC driver for Visual FoxPro since it does not return correct results. I was told that the latest ODBC driver for FoxPro only support up to version 6 and we're using version 9.0.

What I'm trying to do is the following.

We have a project management Foxpro application that stores data in 6 separate folders (free tables). We need a program wide report that combines the 6 projects.

I'm using Crystal for reporting. I started by simply linking the FoxPro tables in MS Access then created a union query that combined the various project tables together. That scenario never require the user to open the Access database, just running the Crystal report. Access is just used as a go-between.

Everything worked fine until I noticed that Access returned different results on every run.

What I would like is to be able to link to the FoxPro tables via OLEDB just as I was linking via ODBC. This mechanism is available in Excel as well as in Word. Why in the world is it not available in Access?


Creating a form while linking two tables
I would like to create a report that allows me to add a montly review in access.

So my first table is my client data and I would like my second table to house my review data. Review month, actual review, date reviewed, etc.

I would I go about doing this as I don't have alot of experience linking tables and such.

P.S. I was just going to keep it all on one form but I don't believe I have enough columns


RecordLocks Property
I created a database (frontend and backend) that links to a SQL database and an AS400 database. There are some linked Access tables and a few local tables (in the frontend) that allows users to customize their environment. I have all the queries and forms local (in the frontend).

This database will replace a different Access database (that I did not write) that consistently locks everyone out . forever. In order to use it again, they have to replace it with abackup, which often means losing data.

do I need to change the default RecordLock property from "No Locks" to "Edited Records" to keep from having this problem, or will having everything but the linked tableslocal solve this problem? Is there any risk in having everything but the linked tables local?


Linking built queries to new tables
I have a set of tables and queries built for those tables. Now I'll be recieving another 50 tables that contain similar data. I can't use the append query because the table would be really big and it woudn't be efficent. Is there a way I can put all those tables in lets say five tables in different databases and yet use the same set of queries I have created to get the results? Help will be


Another date issue....argh...
I am using Access07 to link to tables on an AS/400 via the Ibm Client access driver, on a table I am building a quick and dirty query on I need to gather records between 4/1/10 and 4/5/10, however the as/400 stores the dates as 20100401 and 20100405

how can I use the critiera field on the query design view to accomplish pulling data from these types of dates fields


Where is the Linked Tables' system data stored...?
I have an MS Access app with hundreds of linked tables, spread among 3 different .MDB files. Long story, but every year, I have to re-link all tables to different .MDB files for the given year. The re-linking process is time consuming (I have to visually check every table in the Linked Table Manager and assign it to the correct .MDB file). I wonder if there is any hidden MS Access "system table" which has the linked tables information, and which I could use to automate this yearly re-linking process.? I reckon that this linked table information has to be stored SOMEWHERE in the .MDB file. But where