Query to Linked Excel CSV by Multiple users

[Brian Cook]

I don't know if this is supported.

We have some very large CSV files that the Executive staff open and build
statistics from. When Access opens the CSV, it creates a lock of the file so
that only one user can access and manipulate it.

Is it possible to have more than one user open the linked file and manipulte
it like a shared workbook in Excel?

If not what would be a better suggestion for this?

 

[JimS]

Instead of linking to it, import it each time you use it.

Why is it still a CSV file? Why not keep it in Access where you could easily
have shared access to it, even from Excel?

 

[Brian Cook]

The files are too large to maintain as an excel file. CSV is the format that
they want. Some users use Access most use excel. The Access users are the
issue.

 

[JimS]

OK, well if you must use .csv, then we have to discuss the definition of
"use". If the Access users are simply "reading" and never "write" (add rows
or update columns), then just import it. Create a macro that implements the
"transfertext" action. You can find lots of info by searching the help
section on Access for the docmd.transfertext command.

When you transfer it, it's a copy, and not the original, so any updates
would be lost.

If you need to be able to update it, you'll need to stay with Excel or
Access, but not mix 'em. Access is conservative, and refuses to share. I'm
not even sure if it lets you update a linked csv file from access. It won't
let you update an Access or dBase table. It looks like it works, then reverts

If you maintained the csv table in Access (as a native Access table...) you
can get to it from Excel easily using MSQuery. You could set that up for your
users so they wouldn't have to do anything but hit one key to refresh the
data. Again, though, I believe it's read-only.

Remember, Excel 2003 has an upper row limit of 65,535 rows, so you'll be out
of its league pretty quickly anyway.

 

[Brian Cook]

The TransferText option sounds doable. I will give them a sample of that to
work with and see how it falls out.

Table row limit is also known. We are migrating in the next few months to
Office 2007.


Thanks,