Jason Strickland
jason at purplecone.com
Wed Aug 19 22:48:29 CDT 2009
Thanks, I see both access->excel and excel->access. Will look at them a little closer in the morning. Gonna bookmark that site :) On Wed, Aug 19, 2009 at 10:02 PM, Collins, Darryl <Darryl.Collins at anz.com>wrote: > > 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." > > -- > 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."