[AccessD] Extract Data from MDB

Thank ya'll so very much. I used a combination of all replies in order to
get this done. The problem is that it was causing the main database file to
become Read Only.
Again! I wouldn't have figured it out without your help. Please keep this
list alive :)

Jason

On Wed, Aug 19, 2009 at 9:47 AM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote:

> Here's one possible solution. Use an Access database to display the info.
>
> Create a new Access MDB.
> Create a table to hold the required information (tblUserInfo with three
> fields
> Username,PIN,Balance)
> Create a continuous form which displays this information (frmUserInfo)
> Create an Autoexec macro which runs a Function on startup.
>
> In the function do something like (air code, made need debugging):
>
> Function Startup()
>
> 'Link to Print Table
> DoCmd.TransferDatabase acLink, "Microsoft Access",  _
> "\\cgsecc18a\CZ Print Job Tracker\printSaver.mdb", acTable, _
>  "Print", "tblPrint", False
>
> 'Clear existing data from local table
> CurrentDb.Execute "Delete * from tblUserInfo"
>
> 'Populate local table
> CurrentDB.Execute "Insert into tblUserInfo (Username,PIN.Balance) _
>  & " SELECT UserName,PIN,Balance FROM tblPrint;"
>
> 'Delete Link
> DoCmd.DeleteObject acTable, tblPrint
>
> 'Show data
> Docmd.Openform("frmUserInfo"),,,,acFormReadOnly
>
> End Function
>
> Now every time you open MDB, it will link, get the current data, close the
> link and display
> the data in the continuous form.
>
> On 19 Aug 2009 at 9:01, 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
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
>
> --
> 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."