[AccessD] Extract Data from MDB

Jason,

Would just pulling the data you need in Excel as a fixed recordset work
for you?  I could be updated as often as you wanted and would not
involve locking the .mdb at all.

I have code that can do that if you are interested.  The demo code comes
in two flavours. You can have Excel doing all the pulling, or Access
doing all the pushing. Up to you.

Cheers
Darryl 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jason
Strickland
Sent: Wednesday, 19 August 2009 11:02 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Extract Data from MDB

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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

"This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential,  may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."