Solved

open recordsed of excel file read only issue

Posted on 2014-12-09
14
231 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 84
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 50

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 50

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 50

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 50

Accepted Solution

by:
Gustav Brock earned 500 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 84
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 50

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

Technology Partners: 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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
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.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

738 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