Linked CSV Locking

[Jeff Hunt]

I have several CSV files that I have to link to in the database I'm building.
They have to be linked because they update periodically throughout the day.
However, others need to link to them as well, so I'm looking for a way to
import them several times each day so users aren't locking each other out of
the files. Right now I'm planning on having an OnTimer event on my main form
that will make the linked data into a local table every half hour. Not sure
if I want to run as a delete/re-append or just a re-make the table. Either
way, the problem I have is how to tell if the file is already in use before
running. If I try to delete/append or re-make the local table while someone
else is locking the file, it will remove the local data already in the table.
This would leave my database w/o the table until it runs again in half hour.
Is there a way I can tell if the file is available, and run the update only
on condition of availability? I would rather have 30 min old data than no
data at all for that time.

 

[Chris O'C via AccessMonster.com]

The unconventional db design is causing the problem. Conventional db designs
require data updates to be made in databases, not csv files.

Chris
Microsoft MVP

 

[Jeff Hunt]

The data is an output from a proprietary piece of software used by my
company, one for which I have no direct access. Unfortunately there is no
way around using CSV files if I want the data at all.

 

[Chris O'C via AccessMonster.com]

Since the proprietary piece of software doesn't meet your company's needs,
have you contacted the vendor to alter or replace it with something that does?
Access can link to tables in a lot of different file formats, not just
exported csv files.

Chris
Microsoft MVP

 

[Jeff Hunt]

Not trying to sound rude here, because I totally appreciate any help anyone
can offer, but this is getting kind of off the topic of my question. The
software my company uses is huge and does hundreds of things for other users
that I don't even know about. It is very possible that I could connect to
the data source directly (I suspect it's Oracle, and I have connected to that
many times before) but in all honesty going that route would be more trouble
than it's worth compared to this occasional inconvenience. As it stands,
there is a datasource out there that already has everything I need and I am
already connected to it. It only has the one drawback of being CSV. I am
already using the DateLastModified property to check the last update
timestamp, I was just hoping to find some similar option to check if the file
is readable before executing a piece of code. I've had occasion to do some
Perl scripting, and I know that in Perl there are file test codes to check if
the file exists, if it is readable, writable, etc. I was hoping to find a
similar command in VBA. Looking through the help I have found some commands
to check the existence of the file, and to see or set the read-only property,
but none that can tell me if the file can be accessed. Does such a command
exist in VBA?

 

[John W. Vinson]

I was just hoping to find some similar option to check if the file
is readable before executing a piece of code.

I'd just try to read the file in code, and trap the error if it's not
readable.

 

[Chris O'C via AccessMonster.com]

I'll explain it in non db terms.

A guy comes up to me and says "I've got a 1 yr old baby, an ice cream cone
and 6 puppies. Every time I give the baby the ice cream he ends up crying.
How can I give the baby the ice cream without the puppies eating it?"

I say "Put the baby in a high chair, stand and hold him in your arms,
whatever you can to separate the puppies and the baby while he eats ice cream.
"

The guy says "I can't do that, I have to keep the baby and the puppies
together. I can't change this."

I say "You're setting up for failure."

The guy says "I don't mean to be rude, but I can't do what you're suggesting.
I just need to stop the puppies from eating the baby's ice cream. Can you
help?"

Those who listen to solutions get help. Those who first decide what needs to
be done and ask how to implement it even after being told it's a set up for
failure either do a lot of unnecessary work or end up failing.

Chris
Microsoft MVP

 

[rquintal]

Your story shows a lack of creative thinking.

There are times and places where the simple direct way to accomplish a
task is forbidden and one must seek workarounds.

One might be to get additional ice cream to distract the puppies.

Q

 

[rquintal]

There are a couple of approaches that you might try. First you could
use the antiquated open # statement to see if you can read the file,
and then delete the rows in the table, then read the new data from
the .csv.
Otherwise you could build a temporary table, .
You then try to append the data to the table from the csv, if locked
wait 1 minute and try again, else clear the main table and append from
the temp table and delete its rows when done.

Q

Not trying to sound rude here, because I totally appreciate any help anyone
can offer, but this is getting kind of off the topic of my question.  The
software my company uses is huge and does hundreds of things for other users
that I don't even know about.  It is very possible that I could connectto
the data source directly (I suspect it's Oracle, and I have connected to that
many times before) but in all honesty going that route would be more trouble
than it's worth compared to this occasional inconvenience.  As it stands,
there is a datasource out there that already has everything I need and I am
already connected to it.  It only has the one drawback of being CSV.  I am
already using the DateLastModified property to check the last update
timestamp, I was just hoping to find some similar option to check if the file
is readable before executing a piece of code.  I've had occasion to do some
Perl scripting, and I know that in Perl there are file test codes to check if
the file exists, if it is readable, writable, etc.  I was hoping to find a
similar command in VBA.  Looking through the help I have found some commands
to check the existence of the file, and to see or set the read-only property,
but none that can tell me if the file can be accessed.  Does such a command
exist in VBA?

:

Since the proprietary piece of software doesn't meet your company's needs,
have you contacted the vendor to alter or replace it with something that does?
Access can link to tables in a lot of different file formats, not just
exported csv files.

Chris
Microsoft MVP

 

[Chris O'C via AccessMonster.com]

Nice try. 1 baby, 1 ice cream cone and 6 puppies doesn't mean there's more
ice cream, hot dogs or a bunch of kittens to distract the puppies - or a
mountain lion, wolves or coyotes to carry off the puppies for supper - while
the baby eats the ice cream.

But my point was he decided what the solution was without listening to other
approaches, which he thinks are off topic. He didn't do adequate research to
find what the data source is (might be Oracle - but he doesn't know! - and
he's been able to connect to Oracle before - but he's *not* willing to do it
again). Inadequate research and skills lead to poor software design
decisions.

A competent db developer's first choice would be a link to a view in Oracle
because that gives real time data and no locking. (If Oracle's the data
source, but it's speculation because the developer hasn't done his homework).
Workarounds can end up with inconsistent data, data delays, locking, etc.

Jeff's workaround needs workarounds. Competent db developers do research and
look at other solutions besides the first workaround that needs workarounds.

Chris
Microsoft MVP

 

[Chris O'C via AccessMonster.com]

This occasionally leads to no data in the table when the csv file is locked
after the first read. Jeff *already* has this problem and is asking how to
work around it.

Chris
Microsoft MVP

 

[Jeff Hunt]

I know putting "not trying to sound rude" in a statement is often an excuse
to say something rude, but I really did not mean that at all. My intent was
merely to redirect the conversation to my original question of whether a
certain type of command exists (which it appears it does not). Chris, I
honestly do appreciate the effort you put in to showing me the correct and
preferred method to solve the problem. I would prefer to do it that way
myself. Unfortunately, Q has it right, that there is no practical way for me
to do what you proposed. I've only been with this company for 3 months, and
when people with 5 years more tenure tell you that you are not supposed to
link to that source, then I'm not inclined to try. My comments about it
being Oracle were an attempt to show that I know how to connect that way but
am not able to (as opposed to simply being unwilling to) but I worded it
poorly and my intent backfired.

I was able to find a workaround that does not disrupt the data, similar to
what John suggested. I created a database with the sole purpose of tying up
that CSV in a predictable manner, and used that to run a series of tests. I
usually use CurrentDB.Execute to run queries from code, but I found that
running a make-table query using DoCmd.OpenQuery fails before deleting the
table if the CSV is locked. It appears to produce a predictable error code
(3051) so I was able to trap it to prevent the users from freaking out. The
downside is that it puts the table in my front-end, which I would prefer it
didn't, but it is not a huge table so it is a minor inconvenience (an
invisible one to the users, too).

Thank you all for your input on this issue. It has been very informative.

....jeff...

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-