[AccessD] Access Application Obtaining Data from Excel-Another User Has Excel File Opened

Considering the many problems with Excel's multiple use controls, and 
reccomendations from experienced eople that it should not be used, it may be 
politic, and appropriate to setup a facility where the data is held in 
Access, and SQL selected from the Access tables into an Excel environment, 
with updates done by passing change requests back to Access having got them 
via an Excel form, or change-event processing macro.

That way the data will be held safely, securely, and with an indication as 
to who did what.
and control for an appropriate management of mulltiple updates by different 
users to the same data entry

In addition to the last saver getting to choose to keep someone-else's 
update, or their own,
(As in - both users added to the 'sold' target - but Excel will only allow 1 
set of 'sales' to be saved!)
- A bulk delete of sheets of data in the Excel environment will result in 
the data being missing, and little chance a day or two later of identifying 
who-dun-it, or even what, exactly are the values that have been deleted.

JimB

----- Original Message ----- 
From: "Brad Marks" <BradM at blackforestltd.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Saturday, February 09, 2013 5:18 PM
Subject: Re: [AccessD] Access Application Obtaining Data from Excel-Another 
User Has Excel File Opened


Mark,

Thanks for sharing your ideas.

In the situation that I am dealing with, the users control the Excel file 
and they update it several times per day.  The Access application that I am 
working on simply needs to read the data from the Excel file.

I believe that your approach will work in some situations but I don't think 
that I will be able to lock cells or password protect the file in this case.

Thanks,

Brad






-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Mark Simms
Sent: Sat 2/9/2013 10:04 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access Application Obtaining Data from Excel -Another 
User Has Excel File Opened

Just insure all cells are Locked on that worksheet and then pass-word
protect it.
If the user must make changes, unlock the sheet via a Form's button or check
box setting.
Keep the form up in vbModeLess mode.....and when it is closed, then
pass-word protect the sheet again.
This will effectively keep the sheet in a ReadOnly state most of the time.


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Friday, February 08, 2013 4:27 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Access Application Obtaining Data from Excel -
> Another User Has Excel File Opened
>
> All,
>
> I am struggling with a small Access 2007 application that obtains data
> from an Excel file.
>
> The Excel file is defined as a Linked Table in Access.  There is a
> single query which is used via Record Set processing.
>
> The data is returned perfectly when no one else is using the Excel
> file.
>
> If another user is looking at the Excel file but has not clicked on any
> cells, the data is returned to Access properly.
>
> However, if another user has the Excel file opened and has clicked on a
> cell, the Access application runs into a problem.
>
> The Access VBA code that does the Record Set processing has error
> handling code, but the error that is returned is not trapped by the
> error handling code.  Instead a message is shown that says "This action
> cannot be completed because the Microsoft Excel - (the Excel file name)
> is busy.
>
> I would like to either be able to catch this error with the normal
> error
> handling code
>
> Or
>
> Invoke some sort of routine to determine if the Excel file is tied up
> before trying to obtain the data via Access.
>
> Any ideas?
>
> Thanks,
> Brad
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.





--------------------------------------------------------------------------------


> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>