If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Linking to a mainframe database
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 |
#3
|
|||
|
|||
Linking to a mainframe database
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] |
#4
|
|||
|
|||
Linking to a mainframe database
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 |
#5
|
|||
|
|||
Linking to a mainframe database
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 |
#6
|
|||
|
|||
Linking to a mainframe database
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 |
#7
|
|||
|
|||
Linking to a mainframe database
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 |
#8
|
|||
|
|||
Linking to a mainframe database
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 |
#9
|
|||
|
|||
Linking to a mainframe database
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 |
#10
|
|||
|
|||
Linking to a mainframe database
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 |
|
Thread Tools | |
Display Modes | |
|
|