[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

open recordsed of excel file read only issue

Posted on 2014-12-09
14
Medium Priority
?
237 Views
Last Modified: 2014-12-11
Hi Experts,
I am trying to read records from an excel file using the code below, however I am getting an error message when user has that file open, although I only need read only access.
   conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\Application;"
   rs.Open "SELECT * FROM [Application.csv]", conn, adOpenStatic, adLockReadOnly, adCmdText

Open in new window

how can I avoid this issue?
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
  • 7
  • 5
  • 2
14 Comments
 
LVL 85
ID: 40489965
I don't believe Excel will allow you access if someone else has the file open. All you can do is report that to the user and let them try later.

You may be able to use Automation to open the file ... I haven't tried that, and would be surprised it if would work, however.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40489993
Hi,
Just wonder why would it be different then any other program (MS Word for example), that asks you if you want to open as read only?
In my case, users must download and save file, then open access and do whatever..
Mainly the user itself is the one who has it open (forgot to close), how can I have the program closing it for them?
Thanks,
Ben
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40490683
This is a known issue or misbehaviour if you like.

There really is no other way than rethinking the concept. For example, when the user has finished editing the data in question, provide a macro for the user to call that will copy these data to another workbook (or export in some other format) and then let your Access application link or import the copy.

/gustav
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 4

Author Comment

by:bfuchs
ID: 40492502
Hi Gustav,

There is no editing here, the story is as follows, user downloads from web a csv file and saves this in a local folder, then opens access and imports records from there.
But when user opens that file first with excel (for whatever reason), and don't close it then the import procedure fails.

Thanks,
Ben
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40492518
Oh, I see. Then I would provide the Excel users with a macro that opens the file, imports the data, and closes the file.
Simple to do, easy for the users, and you will be a star (and they will ask for more).

/gustav
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40492542
The portion of opening and importing I already have it, what I am missing is just the command to close the file, in case user had it open (before the import process takes place) so it does not disrupt the process,
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40492559
That sounds like an educational task.

Or perhaps you could expand the macro to - via VBA - first make a copy of the csv file. If that fails, prompt the user to close the file.

By the way. I just made a test using Excel to open a CSV file. Then I was able to copy the file in Windows Explorer. So I guess you could - via VBA - in Access first create a copy of the file, then import it. Then it doesn't matter if an Excel user has the original open or not.

/gustav
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40492665
ok, the copy option sounds good, will see how it works & let you know.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40492700
I tried the FileCopy command from VBA while the file was open and got permission denied error.
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40493257
Then use Shell to call a DOS copy:

strSource = Chr(34) & "F:\Imports\filemaster.csv" & Chr(34)
strDestination = Chr(34) & "F:\Imports\filecopy.csv" & Chr(34)
Shell "cmd /c copy " & strSource  & " " & strDestination  & ""

/gustav
0
 
LVL 85
ID: 40493528
I've had similar issues with Excel in some of my .NET programs which import from Excel sheets. As Gustav indicates, it's generally just a training issue. After the users see the "You must close the Excel workbook" message a few times, they tend to get the picture.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40494736
@Scott,
the problem is that I have many users and the first thing they do is complain to the manager program is not working...this is what I was trying to avoid.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40494748
that actually worked, Thank you!
just wonder why are dos shell different then windows commands, and perhaps thru DOS there is a way to open the original document as read only?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40495561
You can also copy the file manually with Windows Explorer, so it is FileCopy that is special - it probably, as a precaution, tries to open the file for writing before copying.

/gustav
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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