Solved

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

Posted on 2016-08-10
19
44 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
  • 8
  • 7
  • 2
  • +2
19 Comments
 
LVL 34

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 3

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 45

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
 
LVL 49

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 84
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 3

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 49

Expert Comment

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

/gustav
0
 
LVL 34

Expert Comment

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

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 34

Expert Comment

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

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 34

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 3

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 34

Expert Comment

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

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 34

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 3

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 34

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 3

Author Closing Comment

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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now