ACCESS FEED: Re: [MS_AccessPros] Report based on Crosstab query

Jumat, 27 Februari 2015

Re: [MS_AccessPros] Report based on Crosstab query

Khalid-

If your boss is smart enough to get the database window open, then he's probably smart enough to troll the web and find your postings here!

When you give your boss an MDE file, he can see your tables, queries, forms, and reports, but he can't get to any VBA code. When you create an mde file, all VBA code gets removed.

Two things you can do:

1) In Tools / Startup, clear the check box "Use Access Special Keys". This prevents the user from pressing F11 to get the database window.

2) Define the AllowBypassKey property and then set it in your MDE. If you do that, then your boss cannot press Shift when he opens the file to bypass your Startup properties. Put this code in a Standard module:

Sub ClearBypassProperty()

Const DB_Boolean As Long = 1

ChangeProperty "AllowBypassKey", DB_Boolean, True

End Sub

Sub SetBypassPropertyMDE()
Dim db As DAO.Database

' Open the MDE file

' *** CHANGE the file location below to match

' the location of your MDE after you create it.

Set db = DBEngine(0).OpenDatabase("C:\MyDatabases\PCTL.mde")

db.Properties("AllowByPassKey") = False

db.Close

Set db = Nothing
End Sub

Function ChangeProperty(strPropName As String, _
varPropType As Variant, _
varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

Note that you have to fix the reference to "C:\MyDatabases\PCTL.mde" to use the path where you store your mde after you create it.

Compile and save the above code in PCTL.mdb. Go to the Immediate Window (CTRL+G) and type:

ClearBypassProperty

… and press Enter. That will define the property in your database, but set it to True to allow you to continue to use the Shift key.

Then create your mde, go back to your mdb and to the Immediate Window, and type:

SetBypassPropertyMDE

… and press Enter. That will set the property to FALSE in the mde that you give your boss.

He won't be able to hold down SHIFT when opening the database, so your Startup properties are protected. With Use Access Special Keys cleared, he won't be able to use F11, either.

ztbl must remain in the front end because it's a "working" table that my code uses for each report. If you share that table and two people run the report at the same time, one will overwrite the other, and one of the users will get the wrong data. Designing a "working" table like this is quite common for complex reports, but it shouldn't be shared - each user must have his or her own copy. Notice that there are no relationships defined for this table.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 28, 2015, at 5:56 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

This indicates clearly to me that in future i don't have to give him back end but only the front end and .mde if i create a new one.

But here i have a concern, as once by mistake i hide my Database Window of front end and you told me the way how to re-open Database Window. Now if i am going to give him front end with Database Window hidden and by some means he knows how to open Database Window he will have the access to my all coding.

Honestly saying i don't want to give my coding to him because unfortunately we have different culture and approach. If he gets the code then at any time he may fire me and hire some other guy on low package. Is there any other security and safe side for me ? And hope you can understand my point and position.

Coming to your comment:

-> you have to give him a way in the app to open the form that drives the report.

I have a form Main Menu in my app which opens all forms and reports and ultimately returning to Main Menu.

One last question that why you insisted to put ztbl in front end, as my all other tables are in back end and linked in front end. Also the Relationships are defined in back end.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :