[AccessD] Extract Data from MDB

Hi Jason,

Instead of trying to log on to PrintSaver.mdb, how about if the lab monitors
have their own LabMonitor.mdb Access file?

When LabMonitor.mdb opens, and AutoExec macro can trigger code to run which
will just copy the data from PrintSaver.mdb to LabMonitor.mdb.  Then the
data can be displayed as a datasheet or in a form.

You won't need to create a table link back to PrintSaver.mdb.  Instead, you
can use the IN method, like this:


stgSQL = "DELETE * FROM tblLabMonitorData"
docmd.setwarnings = false
docmd.runsql stgSQL
docmd.setwarnings = true

stgPath = "\\cgsecc18a\CZ Print Job Tracker\PrintSaver.mdb"
SELECT Owner, PIN, Balance FROM tblPrintSaverData IN '" & stgPath & "'"
Set rst = CurrentDB.OpenRecordset(stg, dbopensnapshot)
Do While rst.EOF = False

	'Fill tblLabMonitorData here

	rst.movenext
Loop


Be sure to use the single apostrophes around stgPath!

Good Luck!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jason Strickland
Sent: Wednesday, August 19, 2009 8:02 AM
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