Solved

Is there a way 2 users can open Access app linked to excel BE?

Posted on 2016-08-10
19
53 Views
Last Modified: 2016-08-11
Hi Experts,

I have an Access app that compares two excel files and shown the differences in different colors, currently while one user has it open another receives an error when opening the form bound to those excel files.

Wondering if is there a way to make it sharable (as its anyways read-only)?

PS, I know its possible to write code that reads data line by line etc.. but that will require I re-write the entire app.
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
  • +2
19 Comments
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41751231
Not that I know of.  Access seems to want to lock Excel files when it opens them so no other user can open them.  

You might want to reconsider rewriting the app since the data should be in Access rather than Excel.  Then you can export to Excel for reporting if that makes for a better presentation than an Access report.

You might be able to get by if you import the data into a temp table and work with it in Access and then reexport the result.  This will of course result in bloating your application so you will need frequent compacts unless behind the scenes create a temp database and import into that instead of importing into the FE or BE.  Then since you are recreating the temp BE each time you run the process, you won't have to worry about bloat.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41751252
Hi,

The app is just meant for users to compare two excel files they get them from 3rd party..

Perhaps you have some code that can do the following.

When the form opens checks the status of the excel file, if someone has it open then copy those files to a different location and relink those tables to that location, and when form closes link back to original location.

Thanks,
Ben
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 41751363
Possible work-arounds are:
* dynamically attach the Excel Workbooks with VBA code rather than using the persistently attached workbooks
* use ADODB to instantiate two recordset objects
* use Excel automation object to get the data
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
ID: 41751555
> Perhaps you have some code that can do the following.

I would follow a much simpler route:

  • Copy the two files to a local folder, say, Documents
  • Attach the two local files and compare

The first can be done with Access' VBA.FileCopy command.

/gustav
0
 
LVL 85
ID: 41751891
Or use software designed for this, like Araxis Merge:

http://www.araxis.com/merge/index.en

There are some other programs that will do this as well, and some of them are free.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41752856
@aikimark, @Gustav,

What are you calling attach it, are you referring to importing the data vs linking it to Excel?
If yes, do you have some code that imports as a table?
Note- As Pat stated, this will likely blow the Access file in no time..

@Scott,

The reason we have custom app for comparing is that some fields require some logic to build before we get to compare, for example some values are considered the same for our purpose, or only start counting from Xpos and also only display records with so and so etc..which I doubt of the shelf program will be ideal for that.

Thanks,
Ben
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41752930
No, linking I mean.

/gustav
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41752952
"Attach" was the original terminology for "Link".  Some of us still use the terms interchangeably.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41752961
OK, so the only issue I have with that is, it will have to unnecessary copy every time those files from the shared location to their local drive, or else I build some logic that determines when new files are downloaded and only then perform the copy.

Thanks,
Ben
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41752978
Why would you not do the copy at the time the user requests a compare?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41752989
@Pat,

Users get new files lets say once a week, and work with comparison reports as long as it takes to finish what they have to..

Thanks,
Ben
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41753078
OK.  So, they don't want to choose which files to compare and when?  You have to figure out which files to compare and when new files have arrived?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41753088
no, right now every time a new file arrives its on a network sherable folder and they open access to run reports as many time  they need. (the only issue, 2 users cant open at the same time)

While if I do Gustav's approach (or your import suggestion for this matter), I will have to do it each time they open access.

Thanks,
Ben
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41753107
No.  You can log the files so you know which ones you've copied/opened.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41753122
they all have same name and stored at same location.

In other words, user downloads File1.xls and File2.xls to F:\Data folder on server and run the access app for reports, when new files arrive they download and replace those two files on the server and run reports..

Thanks,
Ben
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41753127
Clearly I'm missing something.  What is the problem with copying the files when the user wants to run the compare?  Do both files get replaced at once?  Wouldn't you want the Access app to delete them from the download folder so you could tell they were processed?  You could rename them and move them to a processed folder.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41753167
at the moment this is meant only for handling comparison, while keeping track when there is new file or if file was already processed its up to user's.

right now one user is in charge of downloads while other users just connects to what's there and work.

What is the problem with copying the files when the user wants to run the compare?  

if I would do that, it will have to copy every time users open access to see reports, which its really not necessary.

but if I see no choice, I guess will have to settle for something like it-:(

Thanks,
Ben
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41753177
Why do you think it isn't necessary?  In a perfect world, it wouldn't be necessary because Access would play nice with Excel but Access doesn't play nice with Excel.  So, if you have to allow other users to look at the files while one has them open doing the compare, I don't see an alternative.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 41753304
Thanks to all participants!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question