How to move data automatically between Excel and Access '97? 1

[Guest_imported]

I am trying to set up a system that will keep track of stock and profits for a company using Microsoft Office '97. To do this I need to move data from Access to Excel, calculate it and then move it back. It needs to be automated, so I can't just copy and paste each time. I have managed to get the data I need into Excel by pasting in the database using paste special and then linking the cells where I want the data to it. I have since discovered that a pivottable report would probably have been easier, but it works as it is.

The problem comes when I try to move the data back to Access. I can copy and paste it, but I can't work out how to do any sort of automatic update. I'm sure that Microsoft can't have been so stupid as not to make this feature only go one way, but I don't know how to do it. Can anyone help me please? I know no Visual Basic whatsoever (my programming knowledge consists solely of Commodore 64 Basic), but I would be prepared to do some of that as long as someone could explain how to do it

 

[tlbroadbent]

You can link Excel spreadsheets to Access. You can then query them as any table. You can select columns and rows to append or update the Access table.

If the Excel workbook stays in the same location, you can keep it linked. Then you'll be able to view the data in Access or run queries against it any time that you need to.

You can also set up a data import using the TransferSpreadsheet action in a macro or the DoCmd.TransferSpreadsheet method in VBA. TransferSpreadsheet easy to use and start. Both the Action and the DoCmd method are described in HELP. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Guest_imported]

Thanks, that was very helpful, and I was surprised to get a reply so quickly. Anyway, I'm nearly done, but I keep getting errors regarding the primary key when I run the append querys.

 

[mac318]

Terry, I wasn't aware of the linking capability of Excel to Access. It's a great solution. I have a client who is proficient in Excel. I've been exporting the data and leaving her to do the work in Excel. You gave me a solution I wasn't aware of.

Thanks. Have a kudo.

mac

 

[tlbroadbent]

The error probably means that you are attempting insert rows with the same primary keys as existing rows. If you exported the data from Access to Excel for calculations you should have also deleted the records in the rable so it would be prepared to import all the rows after being updated. If you don't delete the rows in the table, then you should create an Update query rather than Append query. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Guest_imported]

Heh, well, I tried the update query and got no errors, but it doesn't do anything. When I view the query it shows the field names but not the records, and when I run it Access says "You are about to update 0 row(s)," and doesn't update anything. I've joined all related records and in the "update to" section of the field I want updated I've put the

.[fieldname] of the source that I want it to update from. Sorry for being a pain, but can anyone help me with this? I'm not very experienced with Access.

 

[Guest_imported]

Ok, no need now, I've got it sorted. I didn't understand how joining related fields worked, so I joined fields that didn't have equal values and it didn't work. With just the ID numbers joined its working fine. Thanks to everyone who helped me.