Mike McCracken
asked on
Using Excel spreadsheet as a frontend for Access Database
I have several Excel spreadsheets that are used for entering data. CUrrently all data is entered in the spreadsheets and the saved as a spreadsheet.
SInce I need to do analysis of the data entered (other spreadsheets are used) and issue reports to c lients, I would prefer to put the data in a database. I know I could recreate the spreadsheets as forms in the database and enter the data that way but the spreadsheets are quite complex and have many columns and lines.
Is it possible to use an Excel spreadsheet for the data collection then click a button and add a record(s) to the database in one or more tables?
What code would be used behind a save button?
mlmcc
SInce I need to do analysis of the data entered (other spreadsheets are used) and issue reports to c lients, I would prefer to put the data in a database. I know I could recreate the spreadsheets as forms in the database and enter the data that way but the spreadsheets are quite complex and have many columns and lines.
Is it possible to use an Excel spreadsheet for the data collection then click a button and add a record(s) to the database in one or more tables?
What code would be used behind a save button?
mlmcc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The reports will probably be built in Crystal though I may use Access since the client only has CR7.
Each spreadsheet actually represents a single record. To be able to update a record, I would need to read the data from the database. Would it be essentially the reverse of the update process by assigning a data base field to a cell in the spreadsheet?
mlmcc
Each spreadsheet actually represents a single record. To be able to update a record, I would need to read the data from the database. Would it be essentially the reverse of the update process by assigning a data base field to a cell in the spreadsheet?
mlmcc
Just remember, to import an Excel spreadsheet into an Access table, each column must be the same datatype, except for the first row, which may be a string, to represent the field name after import. The data in the columns has to make sense. While you can import a spreadsheet where line 100 is the sum of the previous 98 lines, that line will not make any sense when viewed as a field in an Access table. I think you have to very specific about what parts of a spreadsheet you import.
ASKER
Good idea but the spreadsheet is really being used as a form. For example, if this was a tax return program the spreadsheet would be the form 1040 complete with all text, lines, boxes, etc.
I can use the idea from kittenwhisky to get the data to the database. I am trying to decide if it is easier to do the entire thing in the database with forms (recreate the spreadsheets as forms) or write the code to mvoe the data to the database. The spreadsheets wont be saved but could be recreated from the database.
Looking at the code required to pass the data to the database, it seems to be a tossup as to which will require more effort.
mlmcc
I can use the idea from kittenwhisky to get the data to the database. I am trying to decide if it is easier to do the entire thing in the database with forms (recreate the spreadsheets as forms) or write the code to mvoe the data to the database. The spreadsheets wont be saved but could be recreated from the database.
Looking at the code required to pass the data to the database, it seems to be a tossup as to which will require more effort.
mlmcc
<off topic>
I would say use Crystal Reports, but I don't think you have the necessary skillset to do that. Maybe when you top 11 million points in the CR Zone, but not with a measly 10,900,000 (and change).
LOL
</off topic>