Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352

    Question Adding something to a backend, do I re-link?

    I was having a lot of problems with people accessing a back end database in a folder on a shared drive (even though I distributed the front end and was explicit in saying do not use the one in the share drive), nonetheless this persists. I didn't want to leave anything to chance with whomever is doing this so I added a form to open automatically which has an on load event displaying a message box. The msgbox tells them they shouldn't be on this database and to use the copy that was given to them. click ok and the database closes.



    Since I added this form do I have to do any re-linking of front ends? I wouldn't think so since I did not edit any tables and this is really only for the people who try and access this be on the drive.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If you edit table names or add tables, you might have to change the FE files. Otherwise, no.

    I would add some code to that form that opens to log the user name from the client machine. Then, you can go talk to them and ask them what functionality is missing from the FE file that is nagging them to open the BE.

  3. #3
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    It sounds like you don't have to relink, If all you did was add a form, you will be fine.

    The suggestion ItsMe gave is a great idea.

  4. #4
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    That is a great idea. Possible to receive an email each time someone attempts to open it with that info? (machine #, or log in ID)

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by nick404 View Post
    ...Possible to receive an email each time someone attempts to open it with that info? (machine #, or log in ID)
    Yes, you could use the Environ() function and email the string literal via CDO. Here is a sample of CDO and it looks like it goes after info in a table.
    https://www.accessforums.net/access/...tml#post201919

    Here is some standalone CDO
    https://www.accessforums.net/access/...tml#post192785

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, email can be part of the form open code. If you don't want them to be aware of the message, try:
    Code:
    Dim appOutLook As outlook.Application
    Dim MailOutLook As outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
        .BodyFormat = olFormatRichText
        .To = "your email address"
        ''.cc = ""
        ''.bcc = ""
        .Subject = "Backend Access"
        .HTMLBody = Environ("UserName") & " opened backend from computer " & Environ("ComputerName") & " at " & Now
        .DeleteAfterSubmit = True
        ''.Display
        .Send
    End With
    Or use CDO email. http://www.freevbcode.com/ShowCode.asp?ID=7504

    Do users know about opening db with shift key bypass?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Many thanks to you both.

    They do not know about the shift bypass.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by nick404 View Post
    ...

    They do not know about the shift bypass.
    They will later on. Be tactful when approaching the users. They just want to get the job done. It is up to you, the developer, to provide them with the correct tools. If you force their hand, you will wind up with a huge cat and mouse game and nobody will be productive. /2 cents message

  9. #9
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Quote Originally Posted by June7 View Post
    Yes, email can be part of the form open code. If you don't want them to be aware of the message, try:
    Code:
    Dim appOutLook As outlook.Application
    Dim MailOutLook As outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
    ...
    June I am having errors with outlook.Application, outlook.MailItem, and .CreateItem
    I made some changes but I can't find a suitable substitute for .CreateItem, since I do not know exactly what this is creating.
    Code:
    Dim appOutLook As Application
    Dim MailOutLook As AcSendObjectType   (not entirely sure on this one)
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
        .BodyFormat = olFormatRichText
        .To = "njcook@rocklineind.com"
        .Subject = "User Backend Access"
        .HTMLBody = Environ("UserName") & " opened backend file from computer " & Environ("ComputerName") & " at " & Now
        .DeleteAfterSubmit = True
        .Send
    End With
    End Sub

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It is creating an email message object.

    What errors?

    Did you set VBA reference to Microsoft Outlook Object library?

    Might have to use CDO because user's Outlook would have to be set to allow programmatic sending of messages.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    For the first two dim's I was getting an error saying 'user-defined type not found' or similar.
    The .CreateItem is "compile error: method or data member not found"

    How do I set that reference? The reference option is grayed out, should I put this in a module?

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Library reference set from the VBA editor menu bar: Tools > References

    Should not have to put code in general module.

    If CDO will work for you, don't need the Outlook Library reference. However, some networks are set not to allow programmatic initiation of SMTP.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Ah I had to start with empty code to change the references and it works. I was able to get it to work with an easy .SendObject
    I do not know which would be the best route to take.. I have only used SendObject about two times so far- and do not know much about the other two methods described. I do not think SMTP is a good choice since the outlook we use doe snot use that server, it uses Microsoft Exchange.

    Is there a method that won't initiate with a message box saying "Access is trying to send an email on your behalf. Allow, Deny, Cancel"? Ideally the user won't know I am getting this email hah.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't think possible with SendObject method.

    My Outlook has option setting:

    File > Trust Center > Trust Center Settings > Programmatic Access > Warn me about suspicious activity when my antivirus is inactive or out-of-date (recommended)

    I never get the message box with the suggested code. The CDO method should also not trigger message box because it isn't using Outlook.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Using the code you provided it works well. I think I will just stick with that.

    I tried using the CDO method but it comes up with an error saying it cannot connect to the server, and I checked - our outlook doesn't use an SMTP server. It uses msoft exchange.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. button to link to specific backend file
    By quicova in forum Import/Export Data
    Replies: 5
    Last Post: 11-04-2013, 01:39 PM
  2. Replies: 7
    Last Post: 08-28-2013, 02:30 AM
  3. break the link to a backend table
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 06-28-2013, 10:49 PM
  4. Replies: 2
    Last Post: 05-21-2013, 02:25 PM
  5. Replies: 5
    Last Post: 11-13-2012, 12:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums