make table query

O

OD

I'm building a maintenance program, it's on the network using Access 2007
natave database engine. The problem is I need to allow some user to select
the printer to print a report to. No problem getting the installed printer
from windows and entering them into a table. So the user can select the
printer to use. Ok now the problem I need to make this table private for each
user.

Any help

OD
 
O

OD

Lord Kelvan said:
what do you mean private for each user.
I need the table so each user can only see thier installed printers. I know I could do this wit an array or dyna array. Tables use less memory.
Click to expand...

OD
 
J

John W. Vinson

I'm building a maintenance program, it's on the network using Access 2007
natave database engine. The problem is I need to allow some user to select
the printer to print a report to. No problem getting the installed printer
from windows and entering them into a table. So the user can select the
printer to use. Ok now the problem I need to make this table private for each
user.

Any help

OD
Click to expand...

Use a split database architecture: a "backend" containing just the shared
tables, and a "frontend" on each user's desk, with links to the backend tables
(and all the forms, queries, reports, etc.). Put this private table in the
frontend.
 
O

OD

NO. I scan each users windows to get thier currently installed printers, the
fill the table with the installed printers. After the user select the printer
I empty the table.

OD
 
L

Lord Kelvan

attach a user id to the table so it says printera belongs to user1
then just filter based on the user id

this means you dont have to create many tables.

as a note deleting all records in the table is not good if two users
are accessing it at the same time
but if you attach a user id you can delete * from table where userid =
theuseridoftheusinguser

you shouldnt have to create tables if your database is built right. i
like your idea and would be very interested in the vba code you have
used to get the installed printers. but you should note which user
the printers belong to and then filter on it. because for the make
table query you will still have to differencate which table belongs to
which user dont you think it is eaiser to just note which record in a
table belongs to which user.

Regards
Kelvan
 
O

OD

Kelvan here is the code attched to a form

'***************************************************************************************

'This form allows the user to select a printer for a report. It return the
printer number and
'sets var varTempPrtNum to the selected printer number or sets varTempPrtNum
to 99 if the user
'closes without selecting a printer.

'***************************************************************************************

Private Sub Form_Load()
Dim prtLoop As Printer
Dim varNum As Integer

DoCmd.MoveSize 3000, 2000, 6000, 9000
varNum = 0 '[..Sets public var to 0.]..

For Each prtLoop In Application.Printers
With prtLoop
[Printer Name] = UCase(.DeviceName)
[Printer Num] = varNum
varNum = varNum + 1
End With
DoCmd.GoToRecord , "", acNewRec
Next prtLoop
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
Me.DataEntry = False

End Sub

Private Sub CloseButton_Click()
DoCmd.Close
DoCmd.OpenQuery "DeletePrinters" '[..Remove this users installed printer
from the database.]..
varTempPrtNum = 99 '[..I will be able to use this to see if the user
closed without selecting
'a printer.]..

End Sub

Private Sub Printer_Name_DblClick(Cancel As Integer)
Dim varPrtTemp As Printer

varTempPrtNum = [Printer Num]
DoCmd.Close
DoCmd.OpenQuery "DeletePrinters" '[..Removes this users installed
printer from the database.]..
Set Application.Printer = Application.Printers(varTempPrtNum) '[..Sets
application printer to the
'selected
printer number/]..
Set varPrtTemp = Application.Printer
With varPrtTemp
MsgBox ("You selected " & .DeviceName)
End With

End Sub
 
O

OD

John,

I thought about creating a dir on the user local drive for thier private
data. I came from the Paradox environment, but Paradox handles network files
differently.
You could create a dir for the users private files that only that user could
see. Such as C:\username\privdir. One way that I could do this is on the fly
make a table
UserName+"Printers" NOTE: I get the user name when the user logs in, I'm
having some problem doing this. MakeTable query does not allow var or modules
in he destination table name.

Thanks
OD
 
L

Lord Kelvan

so if you are storing which printer belongs to which user in the
printer table why are you trying to make something private just have a
query

select *
from tblprinter
where printeruser = logedinuser

Regards
Kelvan
 
O

OD

I did a work around. I added user name to the printer table, so I can build
the installed for the current computer then query the printer table for the
logged on user. When they logout I can delete their printers. I do not want
to keep their installed printers by user name, they may use another computer
to log on, and will not have the same printer to use. That's why I need to
delete the installed printers for the user after they log off. I hope this
makes sence to you.

I still would like to know how to make a table private to a user, or if it
can be done in access.

Thanks
OD
 
L

Lord Kelvan

o you thought i ment permentally keeping users no i ment deleting
records but filtering based on the user name.

basically you would need to use DDL language or make table queries to
create the table with a table name that contains the user name and
then to search for it you woudl haev to use the system table
msysobjects
to find the table name with the user name then make a select query in
vba to use that.

what you have done adding the user name to the printer table as i said
is the right method BECAUSE YOU SHOULDNT TOUCH THE SYSTEM TABLE. nor
should end users ever touch a table directally they should always use
forms or at least queries but never the table.

Regards
Kelvan
 
O

OD

Kelvan

Thanks. I never let a user see a table directly only though forms. If I need
to let the
user query a table I either do it in the background or I will setup a query
by form.

RULE # 1 NERVER LET THE USER SEE A TABLE.
RULE #2 CONTROL WHAT THE USER CAN DO.
RULE #3 IF IT BRAKES IT'S YOUR FAULT FOR NOT TRAPPING IT.

THANKS
OD
 
J

John W. Vinson

John,

I thought about creating a dir on the user local drive for thier private
data. I came from the Paradox environment, but Paradox handles network files
differently.
You could create a dir for the users private files that only that user could
see. Such as C:\username\privdir. One way that I could do this is on the fly
make a table
UserName+"Printers" NOTE: I get the user name when the user logs in, I'm
having some problem doing this. MakeTable query does not allow var or modules
in he destination table name.
Click to expand...

STOP.

Access is *not a flawed implementation of Paradox*. It works *DIFFERENTLY*.

You are evidently thinking in terms of Paradox, where a table is indeed a
separate operating system file. That is *not* true in the Access environment.
An Access database is a container for multiple tables, forms, reports and
other objects; tables have no independent existance outside a database.

For details about "split" database applications (which are highly recommended
in any case, for performance and stability) see Tony's website:
http://www.granite.ab.ca/access/splitapp.htm
 
O

OD

Thanks John

I know about containership.

I will look at the web site. "I don't think Access is flawed" it is
different. I know when
other programers tried to work with paradox they had a hard time.

I'm still learning the Access way.

Thanks
OD
 
Top