Linking to a mainframe database

[Gabe]

Hello,

I was just wondering if it is possible to link a table in my Access database
directly to a mainframe database? The mainframe database is written in Cobol.
Currently, I am having to import a data extract (.xls) from the mainframe
database into my Access database (.mdb) every week. When I do this, it messes
all of the associated tables up. I am using Access 2003.

Thanks,
Gabe

 

[Klatuu]

What kind of database is on the mainframe. It is not COBOL. COBOL is
programming language. You need to know if it is DB2, Oracle, etc, then find
an ODBC driver for that database type. You may have to download the driver
from whomever the database supplier is if it is not one of the standard
drivers.

As to importing it from Excel, there are ways to do that without disturbing
the production data in your Access database, but we would need to know more
details about how you are trying to do the import.

 

[John W. Vinson]

Hello,

I was just wondering if it is possible to link a table in my Access database
directly to a mainframe database? The mainframe database is written in Cobol.
Currently, I am having to import a data extract (.xls) from the mainframe
database into my Access database (.mdb) every week. When I do this, it messes
all of the associated tables up. I am using Access 2003.

Thanks,
Gabe

Cobol is a programming language, not a database. Access can link to several
different database formats using "ODBC" - see the online help for the subject.
If your mainframe administrators can provide an ODBC compliant link to the
data you should be OK, but you'll have to talk to them about it, or get deeper
into the data storage structure of your mainframe app.

 

[Gabe]

Thanks for the info, is there a way to tell what kind of mainframe database
it is? It opens from TCP3270...?

On the import, I am just importing a new data extract named master1.xls,
cloning the old master table design, then deleting the old master table, and
renaming new master1 table to master. However when I do that, all of the
relational tables get messed up because the records have changed all around
in the new table...

Thanks,
Gabe

 

[Frank H]

Irrespective of the ODBC, if the excel workbook always has the same name, and
contains the same field names, you can link to the excel worksheet. (File -
Get External data). If necessary you can rename the old workbook, but make
sure that the new workbook goes into the same folder (with the same name).
Then, you can create a query to get the new data into whatever table(s) you
wish. If you are completely replacing the old table with the new excel data,
simply use a make table query, making a table with the same name on each
occaasion.

 

[Klatuu]

You will have to talk to someone in the mainframe group that would know.
They may even be able to give you help on finding an ODBC driver for it.

There are multipe issues with how you are doing the import. First, by
deleteing the master table each time, you are (as you are experiencing)
loosing all your relationships. Also, this method will cause serious
database bloat.

Here is a better plan.
Link to the xls file as if it were a table.
Update append and/or update queries to load the data into the master table.
This is the simplest solution; however, sometimes there may be data issue
that can require some additional massaging, but no sense going into all the
options until you need them.

 

[Gabe]

Perhaps I should clarify a bit:

Here's my dilemma, let's say that the Master table has about 100 records,
all with different employee names. I have another table called "Hours" that
tracks how many hours were spent. The Master table and the Hours table are
relationally joined by the primary key ("ID" that goes from 1 to 100). Now,
the Master table data comes from the Excel spreadsheet every week. However,
the data changes constantly, so the customer who was ID 001 last week may
have left the company since then and now someone else is ID 001. So now when
I import the new Master table data from the Excel Spreadsheet, the Hours
table data does not match the correct ID in the Master table.

The issue for me is not the import, it's getting the other tables in my
database to match the updated Master table. Any ideas?

Thanks,
~Gabe

 

[(PeteCresswell)]

Per Gabe:

However,
the data changes constantly, so the customer who was ID 001 last week may
have left the company since then and now someone else is ID 001. So now when
I import the new Master table data from the Excel Spreadsheet, the Hours
table data does not match the correct ID in the Master table.

The issue for me is not the import, it's getting the other tables in my
database to match the updated Master table. Any ideas?

The rules for IDs have to be changed. Once an ID is assigned,
it can never, ever be reused.

Using social security numbers is probably out of the question bco
personal security/privacy issues.

My preference would be to have the Master database have a column
called something like "IsActive" and never delete a person from
it. When they leave, they get IsActive set to False or
something like "TerminationDate" populated with the date they
left.

I can think of at least one other approach - but it's too
convoluted to work in real life.

 

[Clif McIrvin]

Sounds like *WAY* too much fun for my taste <grin>

In your import process create a new column that adds a date-stamp (such
as week-ending date -- presumably that data is available somewhere in
the weekly spreadsheet). Now, using the combination of week ending date,
ID and name you should be able to properly correlate data in your Access
tables.

I'm fairly certain I'm missing something ... I'm not sure I've seen the
entire thread.

It seems that by linking to the spreadsheet and utilizing queries you
should be able to both apply this weeks time data to existing employees
/ customers regardless of what ID number the spreadsheet uses this week
(Update Query); as well as add any new employees / customers that didn't
exist last week (Append Query).

I routinely pull new data from a "large" spreadsheet into Access tables
in this manner.

Have you posted your Access table structure?

 

[Klatuu]

This is doable and not that uncommon.
The first thing to establish is a way to maintain a consistent ID for each
customer. Once that is done, you can then set up some action queries to
append and update the data as needed. Exactly how, I can't say because I
don't know enough about your data, so I have some questions.

Where, exactly are these ID numbers assigned? Is it downloaded from the
mainframe, is it assigned by the Excel spreadsheet, or it it assigned when
the new Access records are appended?

How static are these customers? Also, is a customer and employee? You refer
to both, but I can't tell if they are different. If customers are not
employees, are they related? I assume the hours are related to an employee.
Do the hours records show the number of hours for a specific week? How do
you identify the week? What day does the week start on?

And, When you import the new week, do you delete the previous week or is it
saved?

Are customers or employees ever deleted?

 

[Gabe]

I just wanted to say thanks to everyone who responded.

The ID numbers are assigned in Access via auto number in my table when I
import the data from the Excel spreadsheet. Disregard the customers field, I
meant to say employees. The Master table only contains employee data. The
Hours table is a manual entry process, the Excel spreadsheet doesn't contain
the hours data, we type the information into it from a form. I have a record
date field in the Master table that identifies the day the last import was.
The record date field data comes from the Excel Spreadsheet. I delete the old
week data during the import process...otherwise I have duplicate records in
the Master table. Employees can be deleted, usually when they leave the
company I'll just go in there and delete thier record. I wish there was a way
to attach the files to this thread so you can see it, but I can't figure out
how to do that.

Thanks again,
~Gabe