Collins, Darryl
Darryl.Collins at anz.com
Wed Aug 19 21:02:34 CDT 2009
Cool, either way works, as I don't have internet access right now I cannot send you the exact link. Have a look at www.excelyourbusiness.com.au and under the excel section for "move data from access to excel" or similar. The website was originally built for me to have somewhere to store all the bits and pieces I had collected over the years, it is not that well organised for the public... :-/ work in progress you see... If you cannot find it, email back and I can the direct link tonight from a different PC with internet access. Cheers darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jason Strickland Sent: Thursday, 20 August 2009 11:55 AM To: Access Developers discussion and problem solving Subject: Re: [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." -- 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."