[AccessD] Extract Data from MDB

Jason,

I am not going to answer your question directly as I have no expertise there and I also think that 
you are taking the long way around the farm.

First of all, a query has properties, and you can make a query read only.  I would suggest that you 
create a query in PrintSaver that pulls only the fields that you want Lab Monitors to see.  Make 
that query read-only, and then have lab monitors only have access to that query.  You could probably 
display the results of that query in a spreadsheet, but it might be easier to do it in Access and 
use an "in" clause in a query to directly reach into the database containing the query.

SELECT * FROM MyQuery IN 'X:\PrintSaver.MDB'

This would be a saved query in your mdb but there would be no linked table back to PrintSaver.

You could of course do the same thing and display the results in a spreadsheet if you are familiar 
with that route.  The fact that the query is read only will probably prevent placing any locks on 
the table, though I am not sure about that.

John W. Colby
www.ColbyConsulting.com


Jason Strickland wrote:
> I rarely post to the list but I have found so much beneficial information
> from being a member. Usually, whenever I encounter a problem, someone on the
> list will post a similar question and I am able to solve my problem.
> Sorry for the long post but I want to lay the groundwork for my situation.
> 
> We have a printing software that controls student printing in the labs. This
> is a "cheap" commercial solution but it works. All of the information is
> contained in an Access database called "printSaver.mdb." Our problem is that
> we want Lab Monitors to be able to access only the UserNames, PINs, and
> Balance from a specific table called Print. We don't want this Lab Monitors
> to have direct access to the database and they not to have access to change
> anything.
> 
> Now, what we have tried doing is creating an Excel Spreadsheet that uses OLE
> to connect to the database. The following is that string.
> 
> Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\Cgsecc18a\cz
> print job tracker\printSaver.mdb;Mode=Share Deny Write;Extended
> Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet
> OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global
> Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New
> Database Password="";Jet OLEDB:Create System Database=False;Jet
> OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on
> Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
> OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False
> 
> The problem occurs in that whenever the spreadsheet is open, it makes the
> database read only so nothing can be added/updated. The database remains
> like this until the spreadsheet is closed. We have tried changing the Mode=
> but that didn't seem to help either.
> 
> Yesterday, I rewrote my Excel spreadsheet using a SQL command and is as
> follows:
> 
> *Connection String:*
> DSN=MS Access Database;DBQ=\\cgsecc18a\CZ Print Job
> Tracker\printSaver.mdb;DefaultDir=\\cgsecc18a\CZ Print Job
> Tracker;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=1;
> 
> *Command Text:*
> SELECT `Print Query`.Owner, `Print Query`.PIN, `Print Query`.Balance
> FROM `\\cgsecc18a\CZ Print Job Tracker\printSaver.mdb`.`Print Query` `Print
> Query`
> 
> This seems to work a lot better than the OLE string in that it sometimes
> holds the database open for 1 sec and then sometimes, it keeps the database
> Read Only around 10 seconds.
> 
> What can I do to extract this information without causing the database to go
> Read Only.
> 
> Thanks so much!!!!
> Jason Strickland
> Network Administrator
> Southeastern Community College