Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Access DB
Hi!
I'm creating a report in Excel. The report is derived from several thousands rows of data (typically 40-50.000). The data comes from 2 different databases (Oracle and SQL-server). I would like to set up an Access db for temporary data storage. With the data in the same db I can create a recordset which joins the 2 tables. Ideally the db is created (with two tables) every time the Excel-report is executed, and thereafter removed (I could of course make it permanent but the users have 15-20 other reports (all in Excel) and I don't want to trouble them with an Access DB). I think that creating the DB from code within Excel doesn't take to much extra time(?). I need some example code for setting up the db with 2 tables. I don't know if I need to set up the tables, perhaps I could just append the Excel recordset, containing the data, directly to the db?? I run Excel 2000 and Access 2000. TIA PO |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Access DB
i keep reading what you are trying to do and can see no
real advantage to doing it that way. If you are going to do a record set, you can get that direct from the oracle db and sql db (access not needed). how are you planning to get the data in the access db for temporary storage? why temporary? linked, it would give you permanent on demand real time data. once created, are you going to delete the access db? sounds to me like you fixing to write a whole lot of code that is really not needed. with what you have stated and with the features in access and excel, you can point and click through the whole process. -----Original Message----- Hi! I'm creating a report in Excel. The report is derived from several thousands rows of data (typically 40-50.000). The data comes from 2 different databases (Oracle and SQL-server). I would like to set up an Access db for temporary data storage. With the data in the same db I can create a recordset which joins the 2 tables. Ideally the db is created (with two tables) every time the Excel-report is executed, and thereafter removed (I could of course make it permanent but the users have 15-20 other reports (all in Excel) and I don't want to trouble them with an Access DB). I think that creating the DB from code within Excel doesn't take to much extra time(?). I need some example code for setting up the db with 2 tables. I don't know if I need to set up the tables, perhaps I could just append the Excel recordset, containing the data, directly to the db?? I run Excel 2000 and Access 2000. TIA PO . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Access DB
Frank,
My english is not so good but I'll try to explain the best I can: 1. Since I get the data from 2 db's I have to use 2 separate connections/recordsets (or is it possible to query both db's in one SELECT joining the two tables? How?). And as far as I know there is no way creating a third recordset based on a join between the other two recordsets. I have tried to use an array as an intermediary to piece the two recordsets together, but since there is a great amount of data involved, I quickly get out of memory. 2. The Access db. About 30 persons use this xl-report and it is sent out by mail. Usually they just place the xlt-file on their desktop and double-click the icon when they want to run the report. I have previously used Access as an intermediary for creating reports. Every time I made changes to the report, an extra column of data for instance, I had to resend both the xlt and the access db. Some of the users replaced the xlt-file but not de Access db, and then, of course the application failed. Therefore, just to keep things simple, I just want to send out a xlt-file, any changes to the report (and the db) is done in the xlt-files code. 3. Linking data. The problem is that some of the rows have to go into separate sheets. I have a procedure which loops through the records, and be a certain logic, rows are moved to other sheets, which in turn makes the links useless. I'd still like to try creating an access db from within the VBE. If it turns out to be to time-consuming I'll try something else. Regards PO "Frank Stone" wrote in message ... i keep reading what you are trying to do and can see no real advantage to doing it that way. If you are going to do a record set, you can get that direct from the oracle db and sql db (access not needed). how are you planning to get the data in the access db for temporary storage? why temporary? linked, it would give you permanent on demand real time data. once created, are you going to delete the access db? sounds to me like you fixing to write a whole lot of code that is really not needed. with what you have stated and with the features in access and excel, you can point and click through the whole process. -----Original Message----- Hi! I'm creating a report in Excel. The report is derived from several thousands rows of data (typically 40-50.000). The data comes from 2 different databases (Oracle and SQL-server). I would like to set up an Access db for temporary data storage. With the data in the same db I can create a recordset which joins the 2 tables. Ideally the db is created (with two tables) every time the Excel-report is executed, and thereafter removed (I could of course make it permanent but the users have 15-20 other reports (all in Excel) and I don't want to trouble them with an Access DB). I think that creating the DB from code within Excel doesn't take to much extra time(?). I need some example code for setting up the db with 2 tables. I don't know if I need to set up the tables, perhaps I could just append the Excel recordset, containing the data, directly to the db?? I run Excel 2000 and Access 2000. TIA PO . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Access DB
you will have to know the tables names in the oracle
database and in the SQL database that your report needs. you may need permission and passwords. see IS if you do. do/know above before proceeding. create an access database. Open accessfilenew databaseblank database create click the table tab then Fileget external datalink table a dialog box will appear. find your oracle data base. High Light the tables you want/need then click link do the same for the SQL database.you are now linked. click the query tab. then click the new button. from the show table box, double click both tables. Link the two table by common fields. drag the fields from each table you want to the query in the order you want. save the query. (run it to make sure it's want you want.) open excel. filedateget external datanew database query from the choose data sorce box, select your database.(if it is not showing, click brouse and find it.) once found, from the choose column box, douple click the query. drag all of the columns from the left side to the right or click the arrow until all columns have moved over to the left side. click next, next, next, finish. you should now have data in the xl file from the access query based on a table in oracle and SQL giving you real time up to date data each time you refresh. there is a exteral data toolbar that can refresh the query,edit, set properties, creat new ect. I ususally put a commandbutton on the screen with the following code behind it. sub commandbutton_on click() range("A1").select 'assuming the query is at A1 Selection.querytable.refresh backgourndquery:= false end sub the button has spoiled my users. -----Original Message----- Frank, My english is not so good but I'll try to explain the best I can: 1. Since I get the data from 2 db's I have to use 2 separate connections/recordsets (or is it possible to query both db's in one SELECT joining the two tables? How?). And as far as I know there is no way creating a third recordset based on a join between the other two recordsets. I have tried to use an array as an intermediary to piece the two recordsets together, but since there is a great amount of data involved, I quickly get out of memory. 2. The Access db. About 30 persons use this xl-report and it is sent out by mail. Usually they just place the xlt-file on their desktop and double-click the icon when they want to run the report. I have previously used Access as an intermediary for creating reports. Every time I made changes to the report, an extra column of data for instance, I had to resend both the xlt and the access db. Some of the users replaced the xlt- file but not de Access db, and then, of course the application failed. Therefore, just to keep things simple, I just want to send out a xlt-file, any changes to the report (and the db) is done in the xlt-files code. 3. Linking data. The problem is that some of the rows have to go into separate sheets. I have a procedure which loops through the records, and be a certain logic, rows are moved to other sheets, which in turn makes the links useless. I'd still like to try creating an access db from within the VBE. If it turns out to be to time-consuming I'll try something else. Regards PO "Frank Stone" wrote in message ... i keep reading what you are trying to do and can see no real advantage to doing it that way. If you are going to do a record set, you can get that direct from the oracle db and sql db (access not needed). how are you planning to get the data in the access db for temporary storage? why temporary? linked, it would give you permanent on demand real time data. once created, are you going to delete the access db? sounds to me like you fixing to write a whole lot of code that is really not needed. with what you have stated and with the features in access and excel, you can point and click through the whole process. -----Original Message----- Hi! I'm creating a report in Excel. The report is derived from several thousands rows of data (typically 40-50.000). The data comes from 2 different databases (Oracle and SQL-server). I would like to set up an Access db for temporary data storage. With the data in the same db I can create a recordset which joins the 2 tables. Ideally the db is created (with two tables) every time the Excel-report is executed, and thereafter removed (I could of course make it permanent but the users have 15-20 other reports (all in Excel) and I don't want to trouble them with an Access DB). I think that creating the DB from code within Excel doesn't take to much extra time(?). I need some example code for setting up the db with 2 tables. I don't know if I need to set up the tables, perhaps I could just append the Excel recordset, containing the data, directly to the db?? I run Excel 2000 and Access 2000. TIA PO . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Access DB
additonal thoughts
make sure the xl file and the access db are on a public drive that all 30 users have access to. they will not need to open access. only the excel file. they click the button - they get the data(report). your work was done when you finish setting it up. this way mail is not necessary and saves space on the mail server. IS will like that. your users can put the file up as an icon on they desktop. and they get real time info when they want as often as they want at the click of a button. remember: week old data is weak ol' data. -----Original Message----- Frank, My english is not so good but I'll try to explain the best I can: 1. Since I get the data from 2 db's I have to use 2 separate connections/recordsets (or is it possible to query both db's in one SELECT joining the two tables? How?). And as far as I know there is no way creating a third recordset based on a join between the other two recordsets. I have tried to use an array as an intermediary to piece the two recordsets together, but since there is a great amount of data involved, I quickly get out of memory. 2. The Access db. About 30 persons use this xl-report and it is sent out by mail. Usually they just place the xlt-file on their desktop and double-click the icon when they want to run the report. I have previously used Access as an intermediary for creating reports. Every time I made changes to the report, an extra column of data for instance, I had to resend both the xlt and the access db. Some of the users replaced the xlt- file but not de Access db, and then, of course the application failed. Therefore, just to keep things simple, I just want to send out a xlt-file, any changes to the report (and the db) is done in the xlt-files code. 3. Linking data. The problem is that some of the rows have to go into separate sheets. I have a procedure which loops through the records, and be a certain logic, rows are moved to other sheets, which in turn makes the links useless. I'd still like to try creating an access db from within the VBE. If it turns out to be to time-consuming I'll try something else. Regards PO "Frank Stone" wrote in message ... i keep reading what you are trying to do and can see no real advantage to doing it that way. If you are going to do a record set, you can get that direct from the oracle db and sql db (access not needed). how are you planning to get the data in the access db for temporary storage? why temporary? linked, it would give you permanent on demand real time data. once created, are you going to delete the access db? sounds to me like you fixing to write a whole lot of code that is really not needed. with what you have stated and with the features in access and excel, you can point and click through the whole process. -----Original Message----- Hi! I'm creating a report in Excel. The report is derived from several thousands rows of data (typically 40-50.000). The data comes from 2 different databases (Oracle and SQL-server). I would like to set up an Access db for temporary data storage. With the data in the same db I can create a recordset which joins the 2 tables. Ideally the db is created (with two tables) every time the Excel-report is executed, and thereafter removed (I could of course make it permanent but the users have 15-20 other reports (all in Excel) and I don't want to trouble them with an Access DB). I think that creating the DB from code within Excel doesn't take to much extra time(?). I need some example code for setting up the db with 2 tables. I don't know if I need to set up the tables, perhaps I could just append the Excel recordset, containing the data, directly to the db?? I run Excel 2000 and Access 2000. TIA PO . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Access DB
I never thought of putting one db on a public drive!
I'll try it our way. Thanks a lot! PO "Frank Stone" wrote in message ... additonal thoughts make sure the xl file and the access db are on a public drive that all 30 users have access to. they will not need to open access. only the excel file. they click the button - they get the data(report). your work was done when you finish setting it up. this way mail is not necessary and saves space on the mail server. IS will like that. your users can put the file up as an icon on they desktop. and they get real time info when they want as often as they want at the click of a button. remember: week old data is weak ol' data. -----Original Message----- Frank, My english is not so good but I'll try to explain the best I can: 1. Since I get the data from 2 db's I have to use 2 separate connections/recordsets (or is it possible to query both db's in one SELECT joining the two tables? How?). And as far as I know there is no way creating a third recordset based on a join between the other two recordsets. I have tried to use an array as an intermediary to piece the two recordsets together, but since there is a great amount of data involved, I quickly get out of memory. 2. The Access db. About 30 persons use this xl-report and it is sent out by mail. Usually they just place the xlt-file on their desktop and double-click the icon when they want to run the report. I have previously used Access as an intermediary for creating reports. Every time I made changes to the report, an extra column of data for instance, I had to resend both the xlt and the access db. Some of the users replaced the xlt- file but not de Access db, and then, of course the application failed. Therefore, just to keep things simple, I just want to send out a xlt-file, any changes to the report (and the db) is done in the xlt-files code. 3. Linking data. The problem is that some of the rows have to go into separate sheets. I have a procedure which loops through the records, and be a certain logic, rows are moved to other sheets, which in turn makes the links useless. I'd still like to try creating an access db from within the VBE. If it turns out to be to time-consuming I'll try something else. Regards PO "Frank Stone" wrote in message ... i keep reading what you are trying to do and can see no real advantage to doing it that way. If you are going to do a record set, you can get that direct from the oracle db and sql db (access not needed). how are you planning to get the data in the access db for temporary storage? why temporary? linked, it would give you permanent on demand real time data. once created, are you going to delete the access db? sounds to me like you fixing to write a whole lot of code that is really not needed. with what you have stated and with the features in access and excel, you can point and click through the whole process. -----Original Message----- Hi! I'm creating a report in Excel. The report is derived from several thousands rows of data (typically 40-50.000). The data comes from 2 different databases (Oracle and SQL-server). I would like to set up an Access db for temporary data storage. With the data in the same db I can create a recordset which joins the 2 tables. Ideally the db is created (with two tables) every time the Excel-report is executed, and thereafter removed (I could of course make it permanent but the users have 15-20 other reports (all in Excel) and I don't want to trouble them with an Access DB). I think that creating the DB from code within Excel doesn't take to much extra time(?). I need some example code for setting up the db with 2 tables. I don't know if I need to set up the tables, perhaps I could just append the Excel recordset, containing the data, directly to the db?? I run Excel 2000 and Access 2000. TIA PO . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Access DB
"Frank Stone" wrote ...
Since I get the data from 2 db's ... is it possible to query both db's in one SELECT joining the two tables? you will have to know the tables names in the oracle database and in the SQL database that your report needs. you may need permission and passwords. Still won't be able to join tables in two different databases. However, the OP could create linked tables in a Jet database and do the JOIN in Jet. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating mailing lists in outlook web access | Excel Discussion (Misc queries) | |||
Creating pivot table from Access database | Excel Discussion (Misc queries) | |||
Creating spreadsheet from access query. | Excel Discussion (Misc queries) | |||
Creating Excel Worksheets from Access Database | Excel Discussion (Misc queries) | |||
Creating a Query in Excel based on an Access Qry... | Excel Discussion (Misc queries) |