[AccessD] Extract Data from MDB

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