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

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.
LVL 5
bfuchsAsked:
Who is Participating?
 
PatHartmanCommented:
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
 
PatHartmanCommented:
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
 
bfuchsAuthor Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
aikimarkCommented:
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
 
Gustav BrockCIOCommented:
> 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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
bfuchsAuthor Commented:
@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
 
Gustav BrockCIOCommented:
No, linking I mean.

/gustav
0
 
PatHartmanCommented:
"Attach" was the original terminology for "Link".  Some of us still use the terms interchangeably.
0
 
bfuchsAuthor Commented:
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
 
PatHartmanCommented:
Why would you not do the copy at the time the user requests a compare?
0
 
bfuchsAuthor Commented:
@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
 
PatHartmanCommented:
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
 
bfuchsAuthor Commented:
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
 
PatHartmanCommented:
No.  You can log the files so you know which ones you've copied/opened.
0
 
bfuchsAuthor Commented:
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
 
PatHartmanCommented:
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
 
bfuchsAuthor Commented:
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
 
bfuchsAuthor Commented:
Thanks to all participants!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.