[AccessD] Extract Data from MDB

Darryl, if you wouldn't sharing, I would look into doing this. We have done
the Database solution right now but it involved changing a few permissions
on the Active Directory share. From my boss' point of view, he'd rather see
Excel pull the data.

On Wed, Aug 19, 2009 at 7:48 PM, Collins, Darryl <Darryl.Collins at anz.com>wrote:

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



-- 
"One reason a dog has so many friends: he wags his tail instead of his
tongue."