Excel to Update Access Records 1

[Wootoid]

I have an Access database with two simple tables, one for equipment and one for the yearly cost of that equipment. The Equipment table is made up of PK EquipmentNumber, Location, Parent and a few other things. It is linked to the Yearcost table with EquipmentNumber as the foreign key and the PK being an autonumber, there are only two other fields in this table, Year and Cost.

The database is designed for quickly viewing historical data on the equipment we currently use and creating trends for budgeting. I have a form that works very well for this(runs a little slow on one search, but that is a question for another day).

Once a year a query is run on an Oracle database to effectively take a snapshot of this data for the year. This is then brought into Excel where it is formatted via VBA to match the Access database. After it had been foramtted and any editing done, I need the information exported to Access.

I can get the information to come into Access with either a macro from Access or Excel, but it does not update any fields that may have changed. Particularly the Location field needs to be updated, as if a piece of Equipment becomes Archived and is no longer in use, I need all instances of that piece deleted from the database.

Currently I am looking at creating a third table specifically designed for bringing in the update, comparing it to the current database, and running an update statement to update the old table's data to match the new information. Then I would run a delete to remove all the equipment that now has a location = Archived, followed by a clearing of data from the update table.

This sounds very inefficient to me, but I am having a hard time coming up with a better solution. Any help/advice would be greatly appreciated.





Aren

 

[SkipVought]

Hi,

"I can get the information to come into Access ..."

HOW? What is your method?

Have you tried LINKING the workbook into Access and then you can query like any other table?

When you say that you "format" the data to "to match the Access database" what does that mean. FORMATTING has a special meaning, that ususally has to do with a DISPLAY value or the Format function.

Is there any reason that you don't link to the Oracle table in Access and do the VBA in Access?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Wootoid]

Hello Skip,

First off I am still a student and am working for a few companies that are having me do some basic programming and database administration. So effectively new blood just getting my career started, and will happily accept any advice on any methods for doing just about any of this.

I currently pull the information into Access via a transferspreadsheet macro in Access, but that simply adds the new equipment that may have been created and does not change any data on the old table.

I have not tried linking the workbook in, mainly I am not very familiar with how that interaction actually functions.

I format the table by having the Excel column names changed to the same names that are in the corresponding Access tables, as well as create a column that displays the current year and changes it to a number format in the cell next to it (that is what they are in Access). For some reason I have had errors when assigning aliases in Microsoft Query, so that is why I do it via the Excel VBA.

I had decided to go from Oracle to Excel first, because this program is going to be used and administrated by a few people who are much more familiar with Excel than they are Access. I will probably not see it again once we have implemented it. The main person administrating the program regularly uses Microsoft Query from Excel and I figured if the Oracle admins changed anything in the main database, he would have an easier time adjusting a query from Excel than he would from Access.

I could be wrong on that statement though.

The program is just for a small section of the company, and the main IT group will not be administering or really even looking at the file.

I am using Access and Excel 2002

I hope that makes sense.

Aren

 

[Wootoid]

So I tried linking to the Excel workbook, that worked but everytime excel closed it left an instance of itself open, causing errors the next time I ran the query. I may have to work with this, but that should be at least a better method of getting the info in. I assume a simple update query should suffice for updating my original tables.


I did link to the Oracle database, but that pulled information from the table that my users should not see and they are suppossed to be able to view the tables as per the request of my supervisor (other than the one administrating the program).


I will keep working from here, thanks for the suggestion(question really, but I have never used it so I wasn't thinking about it) about linking to the Excel sheet.

Aren