Help With Linked Excel Documents Being Locked 1

[Herdrich]

Here is my issue i have a Access database with multiple Excel documents linked to it. Everything works fine except when someone is using the Access database the Excel document becomes locked. I know the reason behind that but i want to be able to have someone have the Access Database open (not accessing the specific data being changed) while another person can update information in the Excel document as needed. When the Excel documents are updated they are just replaced with new ones from the companies using the same name.

 

[Remou]

Under Tools in Excel, you will find Share Workbook. Is this set to allow more than one person to edit the workbook?

http://lessthandot.com

 

[Herdrich]

I tried a few settings with Share Workbook in Excel but no luck. Instead of the popup that it is being locked by a specific user i get an error that the workbook is either corrupt, locked or read only.

Can i have Access do something like run a query of the linked tables and put the data into a table every few hours or so then run my form from the table so its not directly accessing the query like it is now.

 

[Remou]

You can certainly import the excel sheet to a table on a regular basis. It would be best to use the same table: delete then import, to cut down on bloat.

http://lessthandot.com

 

[Herdrich]

Can the inport of the tables be automated because i dont want to inport 100 Excel sheets everyday. Or can the one query of all the data just be inported into a table.

 

[Remou]

Yes.

* You can use DoCmd.TransferSpreadsheet in either code or a macro, if you want to import each workbook;
* You can use an append query through code or a macro to add the query to an existing table;
* You can use a make table query to create a new table from the query, once again, in code or a macro - this is not a good route, except for the first time, because it will lead to bloat.

You do not even need to attach the Excel tables, a query can reference an external spreadsheet.

* Finally, you can use ADO to do quite a lot.

What do you need help with?

http://lessthandot.com

 

[Herdrich]

I just found the appent query and that seems to be what i want to go with im not sure how to make it work with the code i already have.

SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Company Name], [Point of Contacts Name], [Point of Contacts Phone Number], [Date Added], [Date Expires], [Escort], [Mission Esential] FROM [Access List Test]

UNION ALL SELECT [Last Name], [First Name], [MI], [Drivers License Number], [Drivers License State], [Last Four of SSN], [Company Name], [Point of Contacts Name], [Point of Contacts Phone Number], [Date Added], IIF([Date Expires],"Null","NA"), IIF([Escort],"Null","No"), IIF([Mission Esential],"Null","No") FROM [CAPONES PIZZA BONIFACE]
ORDER BY [Last Name];

 

[Remou]

First run a make-table query based on your existing query, say:

SELECT * INTO NewTableName
FROM YourQuerName


This table can then be used for all your future queries. Every hour you will run a delete query:

DELETE * FROM NewTableName

And an append query:

INSERT INTO NewTableName
SELECT *
FROM YourQuerName;

Does that seem to suit?

http://lessthandot.com

 

[Herdrich]

This looks like its getting me to whare i need to go. Whould using the Update query eliminate the need to deleat then append?

 

[Remou]

It depends on your data. Update may suit, but you may need to run an append as well, because update will not add new records.

http://lessthandot.com

 

[Herdrich]

What can i do to automate this process? I only really need it to be updated once a day but more often would be beneficial. I made a Marco that updates the lists and im going to link it to a button but what can i add in the condition so that when i open up the main Forum or the database it will run the command.

 

[Remou]

You can run the macro in the load event of the form or using the /x start up command for the database:

http://support.microsoft.com/kb/209207

If you want to go for something a little fancier, you can use a form with a timer event, though this can be nuisance in some situations.

http://lessthandot.com

 

[Herdrich]

Well i think the just using the button might work for my needs. Do you know how to get rid of the prompts asking if im sure i want to delete or add rows? Or know where i can find some info on it.

 

[Remou]

If you are using a macro, you can use SetWarnings: False for before the queries and back to True for after the queries.

http://lessthandot.com

 

[Herdrich]

You are my Hero. Thanks for everything

 

[accessgeek55]

If you haven't checked it out, I have used Total Visual Agent (previously called Total Access Agent when it only worked for Microsoft Access). It is a low cost product by FMS Inc. I have used many of their versions starting with that for Access 2.0. I currently have Total Visual Agent for 2003. I have never had a problem with there software.

Why you want to check them out? Because this tool/software is an easy to use (no programming, even though I am a programmer) GUI interface program to schedule Microsoft Access or any JET ENGINE maintenance. It also can run macros at scheduled times. It keeps a log, etc......

I've had customers too cheap to buy it (I think it's $200 now, my first version was $100). For them I have written a form with timer (like mentioned above). Even bringing it from one customer to another free, they definitely still spend more in my time (and thus money) by just not having the history logging.

And NO I don't get a commission or anything, I just love their product and ask for it at any place I provide Access support services.