Share a linked table / Excel file

[Autoeng]

This is a good one and I'm at a loss to overcome it.

I have a database (Database1) that has a table (tblReqdParts) that is a link to an Excel spreadsheet (allparts1.xls).

I have another database (Database2) that has a table (tblReqdParts) that is a link to an Excel spreadsheet (allparts2.xls) and has another table (tblDB1ReqdParts) that is a link to the Excel spreadsheet (allparts1.xls) from above.

In Database2 I perform a query that subtracts allparts1 from allparts2 and report this to an Excel spreadsheet via an ODBC link.

Now, if I open the ODBC linked spreadsheet which fires the query in Database2 and someone has Database1 open I get an error that the Excel spreadsheet allparts1.xls cannot be opened because it is opened exclusively by another member. Of course! I need to set the Excel properties to shared workbook. AAAAUGGGGHHHH! It still doesn't work.

Can anyone see a way around this?

 

[Autoeng]

Oh, come on, now!

I told you it was a good one. I can't believe that no one has offered a solution to the problem.

I guess I'll have to quit linking to the tables and actually import them as that is the only way I see around it but was hopeful that someone might propose an alternative that I missed.

 

[mcadle]

Sorry for not responding as quick as you would like. Why can't you just import the the excel data into a table? For instance, create a command button or macro then code in transferspreadsheet.


ADDENDUM: Wierd I posted before your second post had come up. As soon as I posted then yours popped up.

 

[Autoeng]

Sorry for not responding as quick as you would like. Why can't you just import the the excel data into a table? For instance, create a command button or macro then code in transferspreadsheet.


ADDENDUM: Wierd I posted before your second post had come up. As soon as I posted then yours popped up.

New guy, heh? I was joking about no posts. Check my numbers and I've been here a while and have a joking relationship with most old-timer members. Welcome to the board and come over to the Watercooler for some light hearted banter (but stay away from Rich, he bites).

With a linked file I don't have to do anything in any of the databases (there are actually 3 but I only described 2 in my post). If I import the table then I have to remember to do it in all the db's everytime it changes (once, every day). I was hoping that someone saw something I was missing as to why Access was locking the Excel file even though it is set to shared.

 

[Autoeng]

Thinking about the macro that would run on form open I created one that:

1) Imports the Excel file via transferspreadsheet
2) Runs an update query that updates another table not related to the imported table
3) Runs an update query that updates another table based on data in the imported table

On macro run I get the error "Operation must use an updateable query". I am running update queries but is it referring to something else?

 

[Autoeng]

Found some help file info on the error.

I'm getting the message "Operation must use an updatable query."
This error occurs when the current query's Update To row includes a field from either a crosstab query or select query in which an aggregate (total) was calculated for the field (using either the Totals row or a domain function in the Field row). To update a field using the aggregate of another field, calculate the aggregate in the update query itself, not a different query.

Neither of the queries has this condition. The Update To row does not include a field from a crosstab or select query. These queries run fine when maually activated, it was only when I tried to put them in a macro did the error occur.