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.

 

[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.

 

[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.

 

[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

 

[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...

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