open recordsed of excel file read only issue

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?
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
bfuchsAuthor Commented:
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
Gustav BrockCIOCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

bfuchsAuthor Commented:
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
Gustav BrockCIOCommented:
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
bfuchsAuthor Commented:
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
Gustav BrockCIOCommented:
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
bfuchsAuthor Commented:
ok, the copy option sounds good, will see how it works & let you know.
0
bfuchsAuthor Commented:
I tried the FileCopy command from VBA while the file was open and got permission denied error.
0
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
bfuchsAuthor Commented:
@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
bfuchsAuthor Commented:
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
Gustav BrockCIOCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.