Sabtu, 28 Februari 2015

Re: [MS_AccessPros] General Table structure

 

Hi John,

I keep Contacts together.  I then have separate tables for specific Customer info, Vendor info, etc.  The PK for Customers can be the same as the PK for Contacts.  Tables related to customers don't have direct relationships to Contacts but can go there of course.  Contacts then relates to Addresses, Phones, and other contact tables.  In this way, all contact information is in one place making it easier to enter it and get reports ~

Just a different way to do it ...

Warm Regards,
Crystal

 *
   (: have an awesome day :)
 *


On Saturday, February 28, 2015 12:07 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:




Crystal-

If Bill is also keeping track of Orders and Purchase Orders in the database, it would be better to have separate tables because otherwise you would need to link Contacts to both tables - ugly.

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 
(Paris, France)




On Feb 28, 2015, at 4:27 AM, Crystal strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Bill,

To add another idea:

keep contacts together and then create a cross-reference table for the contact category (ie: Customer, Vendor) -- that way, the information needs only be entered once and is easier to keep up to date if a contact changes.  This is what is done here in my Contact template:



Warm Regards,
Crystal

 *
   (: have an awesome day :)
 *


On Saturday, February 21, 2015 1:36 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:




Bill-

Separate tables would be better.

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 
(Paris, France)




On Feb 20, 2015, at 11:37 PM, 'Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am putting a database together to assist my son in his small business.
He have vendors that he buys from and he has customers that he sells to.   In general the information in these tables are similar but not exactly the same.  
 
Would it be best to put them in separate tables or the same table with a key field that distinguishes them from each other, such as 1 = Vendors, 2 = Customers.
 
I just thought I would ask before I get too far into this.
 
Thanks
Bill
Minnesota.
 
 










__._,_.___

Posted by: Crystal <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

.

__,_._,___

Re: [MS_AccessPros] Report based on Crosstab query

 

Khalid-


See answers below.

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 
(Paris, France)




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

John,
This is very informative and opening new doors of knowledge to me.
Still i have to get more information from me as i am doing these actions for the first time and not convenient with them.

You wrote:
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:

For action No.1, this i have to do in the copy of .mdb which i am giving to my boss ?

==> I thought you were giving him an mde.  I would do it in your mdb just before you create the mde, then change it back after creation.  If you forget, you can always hold down the Shift key when you open your database to bypass Startup.

For action No.2, Shall this code to put in a Standard module in my .mdb OR in the copy to give to my boss?

==> Put the code in your mdb.

You wrote:
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.

I save my .mde in "D:\PCTL\PCTL.mde", so in the code "C:\MyDatabases\PCTL.mde" should remain same or  it is to be modified as "D:\PCTL\PCTL.mde".

==> Yes.

You wrote:
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.

How do i set it to True again?

==> It gets defined and set to True when you run ClearBypassProperty in your MDB.  (This defines the property but still allows you to use Shift when you open your database if necessary.)  You want it set to False only in the MDE.  That's why after you create the MDE you must run the SetBypassPropertyMDE code.

After doing all steps you mentioned i think there would be no need for the following code in my form MainMenu
Private Sub Form_Load()
    Dim CheatDate As Date
    Dim Dte As Date
    Dte = Now()
    'If Dte >= (#6/30/2011#) Then
    If Year(Dte) >= 2016 Then
        CheatDate = Dte
        DoCmd.OpenForm "CheckDate", , , , acFormEdit
        Forms!CheckDate!CheckDate = CheatDate
        
        MsgBox "An unexpected error has occoured..." _
        , vbCritical, "Microsoft Access"
       DoCmd.Quit
    End If
    
    DoCmd.OpenForm "CheckDate"
    If Forms!CheckDate!CheckDate > Dte Then
        MsgBox "An unexpected error has occoured..." _
        , vbCritical, "Microsoft Access"
       DoCmd.Quit
    End If
    DoCmd.Close
End Sub

==> It's not clear what that code is doing.  It doesn't matter whether you keep it or not.

Waiting for your kind guidance to proceed further and sorry for bothering you again & again.

Regards,
Khalid


 


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

By the way, NEVER set AllowBypassKey to False in your mdb - you will lock yourself out!

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 
(Paris, France)




On Feb 28, 2015, at 8:30 AM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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 
(Paris, France)




On Feb 28, 2015, at 5:56 AM, khalidtanweerburrah@... [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 :

Khalid-

You're going to have to give him the front end to be able to use the new report, so yes, you should create a new .mde to give to him - that includes the ztbl for the report.  If you hide the Database Window, you have to give him a way in the app to open the form that drives the report.

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 
(Paris, France)




On Feb 27, 2015, at 1:36 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
As i mentioned you earlier after updation / new entries particularly in Collection table i give PCTL_be to my boss to view updated fresh reports, PCTL.mde file is already on his computer. If there is any change in program i create new .mde and overwrite on my boss's computer.

Now as you are insisting me to keep "That table must remain in the front end - it is not sharable."   Would i have to give him front end. Also after making .mde from Tools->Start up-> Display Database Window i un-check this box.

Then i future when i give front end to my boss do i uncheck Display Database Window for his copy ?

OR there is some other way/rule?
Please guide this is a new situation for me.

Note: now the report is perfect and OK i have no problems.

Regards,
Khalid
 


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

Khalid-

That table must remain in the front end - it is not sharable.

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 
(Paris, France)




On Feb 27, 2015, at 6:12 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Thank you for writing down the list. I had already imported these items in PCTL.mdb

One thing may be i am wrong, should the ztblKhalidReport  be imported in PCTL_be and then from PCTL.mdb it should be linked?

Regards,
Khalid
 


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

Here's the list again:

ztblKhalidReport
qryKhalid
qryKhalid_DH
frmRunCrosstab
frmRunCrosstabJV
rptConsignmentCrosstab
rptKhalidMT
srptConsignmentNumberHeadings
Module1

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 
(Paris, France)




On Feb 26, 2015, at 8:08 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
I did as you said.

(By the way where are the items listed "and import the 9 objects I listed below from the database you downloaded from our group.")

It's late night here i will do my necessary formatting tomorrow morning and let you know the progress.

In the mean while take care & thanks.

Regards,
Khalid







__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (66)

.

__,_._,___

Re: [MS_AccessPros] Report based on Crosstab query

 

John,

This is very informative and opening new doors of knowledge to me.
Still i have to get more information from me as i am doing these actions for the first time and not convenient with them.

You wrote:
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:

For action No.1, this i have to do in the copy of .mdb which i am giving to my boss ?

For action No.2, Shall this code to put in a Standard module in my .mdb OR in the copy to give to my boss?

You wrote:
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.

I save my .mde in "D:\PCTL\PCTL.mde", so in the code "C:\MyDatabases\PCTL.mde" should remain same or  it is to be modified as "D:\PCTL\PCTL.mde".

You wrote:
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.

How do i set it to True again?

After doing all steps you mentioned i think there would be no need for the following code in my form MainMenu
Private Sub Form_Load()
    Dim CheatDate As Date
    Dim Dte As Date
    Dte = Now()
    'If Dte >= (#6/30/2011#) Then
    If Year(Dte) >= 2016 Then
        CheatDate = Dte
        DoCmd.OpenForm "CheckDate", , , , acFormEdit
        Forms!CheckDate!CheckDate = CheatDate
        
        MsgBox "An unexpected error has occoured..." _
        , vbCritical, "Microsoft Access"
       DoCmd.Quit
    End If
    
    DoCmd.OpenForm "CheckDate"
    If Forms!CheckDate!CheckDate > Dte Then
        MsgBox "An unexpected error has occoured..." _
        , vbCritical, "Microsoft Access"
       DoCmd.Quit
    End If
    DoCmd.Close
End Sub

Waiting for your kind guidance to proceed further and sorry for bothering you again & again.

Regards,
Khalid


 


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

By the way, NEVER set AllowBypassKey to False in your mdb - you will lock yourself out!

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 
(Paris, France)




On Feb 28, 2015, at 8:30 AM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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 
(Paris, France)




On Feb 28, 2015, at 5:56 AM, khalidtanweerburrah@... [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 :

Khalid-

You're going to have to give him the front end to be able to use the new report, so yes, you should create a new .mde to give to him - that includes the ztbl for the report.  If you hide the Database Window, you have to give him a way in the app to open the form that drives the report.

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 
(Paris, France)




On Feb 27, 2015, at 1:36 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
As i mentioned you earlier after updation / new entries particularly in Collection table i give PCTL_be to my boss to view updated fresh reports, PCTL.mde file is already on his computer. If there is any change in program i create new .mde and overwrite on my boss's computer.

Now as you are insisting me to keep "That table must remain in the front end - it is not sharable."   Would i have to give him front end. Also after making .mde from Tools->Start up-> Display Database Window i un-check this box.

Then i future when i give front end to my boss do i uncheck Display Database Window for his copy ?

OR there is some other way/rule?
Please guide this is a new situation for me.

Note: now the report is perfect and OK i have no problems.

Regards,
Khalid
 


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

Khalid-

That table must remain in the front end - it is not sharable.

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 
(Paris, France)




On Feb 27, 2015, at 6:12 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Thank you for writing down the list. I had already imported these items in PCTL.mdb

One thing may be i am wrong, should the ztblKhalidReport  be imported in PCTL_be and then from PCTL.mdb it should be linked?

Regards,
Khalid
 


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

Here's the list again:

ztblKhalidReport
qryKhalid
qryKhalid_DH
frmRunCrosstab
frmRunCrosstabJV
rptConsignmentCrosstab
rptKhalidMT
srptConsignmentNumberHeadings
Module1

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 
(Paris, France)




On Feb 26, 2015, at 8:08 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
I did as you said.

(By the way where are the items listed "and import the 9 objects I listed below from the database you downloaded from our group.")

It's late night here i will do my necessary formatting tomorrow morning and let you know the progress.

In the mean while take care & thanks.

Regards,
Khalid






__._,_.___

Posted by: khalidtanweerburrah@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (65)

.

__,_._,___