I need Ideas. Linked Excel File won't save when a repot is open...

[smc678]

Hello all, I have a database that depends on 2 external excel files that are linked to my tables. Through out the day we run reports that are an excel format that we save as the linked excel file which updates the database. The problem is everyone needs to be out of all the running reports to be able to save this report dump to the linked file. Is there a way to have the reports close down when I update the file or the reports auto close after 2min, or any other ideas?




Thank you

[rpeare]

I don't think I understand your description.

Do you have 2 excel files
People are updating those excel files during the day
The excel files are in turn linked into your access database

OR

is all of your data in MS access and you are periodically exporting data from the access database TO excel but you can't overwrite the existing file because someone has it open?

[smc678]

Yes the 1st one. The 2 excel files are reports from our warehouse management system. These reports are saved to excel. Say every 2 hrs we pull a fresh report and we save as the linked files to overwrite the old data the all my queries and reports in the database will have the updated data. So yes there linked. Is there a way I can have them not linked where if you open the database or a report then it would grab the data instead of it being linked?

[rpeare]

Yes there is.

The way I normally do this is to import the excel file (don't link it), just to get the table structure so you don't have to type it all in by hand

When you want to 'refresh' your data if you want to TOTALLY OVERWRITE what you currently have in your table, in other words, if what's in the updated excel file is a full history and you don't care about anything prior to it.

Run a delete query against the table that's holding your excel data
then do a docmd.transferspreadsheet (there should be help when you do this through a vba window) to import the file into your table.

If you want to 'update' your data in other words you want a cumulative set of data and the file that you are importing is NOT a full set of data just 'new' data you'll have to set up a PK on your table within access if there isn't one available in your excel file and just import the data, any records that already exist with the defined PK in your database should not be imported.

[smc678]

How do you do this?

Run a delete query against the table that's holding your excel data then do a docmd.transferspreadsheet (there should be help when you do this through a vba window) to import the file into your table. Is this all in Code? How do you write this?

[rpeare]

Create a query based on the table you want to purge records from
change the query type from SELECT to DELETE
Add the * (all fields, all records)

Save the query

That's a delete query

link to the docmd.transferspreadsheet method

http://msdn.microsoft.com/en-us/libr.../ff844793.aspx

Do a web crawl for docmd.transferspreadsheet, there is a ton of support out there for it.