Linking to a mainframe database

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

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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

On Mon, 28 Sep 2009 15:24:07 -0700, Gabe
wrote:

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

John W. Vinson [MVP]

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

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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

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.
--
Frank H
Rockford, IL


"Gabe" wrote:

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

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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

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



"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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

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.

--
PeteCresswell

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?

--
Clif

"Gabe" wrote in message
...
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



"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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



--
Clif

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?
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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



"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Gabe" wrote:

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