Blank screen when opening a database

[BHo15]

Sigh... This project has been nothing but problems for me.

I finished the database I was building and sent it to the user. The database has an AutoExec macro which opens a form (frmHangout) hidden.

When this form opens, it has code to run. Here it is...

Option Compare Database
 
Private Sub Form_Close()
    Call SpecialKeys(False)
End Sub
 
Private Sub Form_Load()
    On Error GoTo Ooops
    Dim tdf As DAO.TableDef
    Dim strP As String
        strP = DLookup("P", "tblP")
    Dim strPath As String
    DoCmd.OpenForm "frmLogin"
    If DLookup("LinkedTablesYN", "tblLinkedTablesYN") = True Then
        DoCmd.OpenForm "frmLogin"
    ElseIf DLookup("LinkedTablesYN", "tblLinkedTablesYN") = False Then
        DoCmd.OpenForm "frmLogin"
        MsgBox ("It appears that you need to relink the tables.")
        Call ReLinkTables
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tblLinkedTablesYN SET LinkedTablesYN = True"
    Else
        DoCmd.OpenForm "frmLogin"
        MsgBox ("You may have issues with linked tables." & Chr(10) & "Check with your database administrator.")
    End If
    Set tdf = Nothing
    Exit Sub
 
Ooops:
    Forms!frmLogin.txtErrNumb = Err.Number
    Forms!frmLogin.txtErrDescrip = Err.Description
    Forms!frmLogin.txtErrLoc = "Form_Load - frmHangout"
    MsgBox ("There has been an error. " & Chr(10) & "If it continues, let your manager know.")
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryAppendErrors"
    DoCmd.SetWarnings True
    Forms!frmLogin.txtErrNumb = ""
    Forms!frmLogin.txtErrDescrip = ""
    Forms!frmLogin.txtErrLoc = ""
    DoCmd.OpenForm "frmLogin"
    Exit Sub
End Sub

Here is the code for ReLinkTables...

Option Compare Database
 
Public Sub ReLinkTables()
    On Error GoTo Ooops
    Dim dbs As DAO.Database
        Set dbs = CurrentDb()
    Dim tdf As DAO.TableDef
    Dim T As TableDef
    Dim strPath As String
 
    MsgBox ("You will find the backend database in this folder search.")
    strPath = FindFile
 
    For Each T In dbs.TableDefs
        If T.Attributes = 0 Then GoTo NextT 'This makes it skip unlinked tables
        If LCase(Left(T.Name, 4)) = "msys" Or Left(T.Name, 1) = "~" Then GoTo NextT
        Set tdf = dbs.TableDefs(T.Name)
        tdf.Connect = ";DATABASE=" & strPath & ";PWD={MyPassword}"
        tdf.RefreshLink
 
NextT:
    Next T
        Set tdf = Nothing
        Set dbs = Nothing
 
    MsgBox ("The tables have been relinked")
    Exit Sub
Ooops:
    Forms!frmLogin.txtErrNumb = Err.Number
    Forms!frmLogin.txtErrDescrip = Err.Description
    Forms!frmLogin.txtErrLoc = "ReLinkTables - mdlReLinkTables"
    MsgBox ("There has been an error. " & Chr(10) & "If it continues, let your manager know.")
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryAppendErrors"
    DoCmd.SetWarnings True
    Forms!frmLogin.txtErrNumb = ""
    Forms!frmLogin.txtErrDescrip = ""
    Forms!frmLogin.txtErrLoc = ""
    Exit Sub
End Sub

And finally... Here is the code for SpecialKeys...

Option Compare Database
 
Public Sub SpecialKeys(blStatus As Boolean)
    On Error GoTo PropError
    Dim prp As DAO.Property
    Dim db As DAO.Database
        Set db = CurrentDb()
    Dim strPrp As String
 
    strPrp = "StartUpShowDBWindow"
    db.Properties(strPrp) = blStatus
    strPrp = "AllowBreakIntoCode"
    db.Properties(strPrp) = blStatus
    strPrp = "AllowSpecialKeys"
    db.Properties(strPrp) = blStatus
    strPrp = "AllowToolbarChanges"
    db.Properties(strPrp) = blStatus
    strPrp = "AllowFullMenus"
    db.Properties(strPrp) = blStatus
    strPrp = "AllowBuiltInToolbars"
    db.Properties(strPrp) = blStatus
    strPrp = "AllowByPassKey"
    db.Properties(strPrp) = blStatus
    strPrp = "AllowShortcutMenus"
    db.Properties(strPrp) = blStatus
    Exit Sub
 
PropError:
    Set prp = db.CreateProperty(strPrp, dbBoolean, blStatus)
    db.Properties.Append prp
    Exit Sub
 
End Sub

So, Here is the problem. When I open the database, it works like a champ. But when she opens it, it opens to a blank screen. No forms open, and no code runs. I had her set her security options (temporarily) to Allow all macros, but it didn't help the problem.

I then sent her the same database with no code running at the beginning, and she was able to open it, and was able to see and use the Navigation Pane.

What gives? I've never seen this before.

Thanks.

[twinnyfo]

BHo15,

Is this DB compiled into a .accde file or is it still in .accdb?

All your properties in SpecialKeys I set when I compile my Dbs so that they are not negotiable, then I set a specific form to open on startup.

I also avoid Macros, if at all possible.

I wouldn't say this is an answer or solution to your described problem, but sometimes I have seen strange things happen with uncomiled DBs being shared....

Others may have a better idea of what is going wrong.

[BHo15]

Good thoughts twinnyfo. There is no reason that I could not create and send an ACCDE. I'll give that a try.

[BHo15]

An ACCDE button is not visible in Database Tools. I Googled it, and saw that a DB password is required to create an ACCDE. I added one, and I still don't have an ACCDE button. I'm using Office 2016.

Thoughts?

[twinnyfo]

File

Save As

Make ACCDE

That should do it....

[BHo15]

That did it. Thanks (It's been a while since I have had a need to create an ACCDE... Could you tell? :)

[NeoPa]

Hi BHo15.

Twinny's right in that you can simply set a form to open automatically when the database is opened and avoid macros entirely (Highly recommended).

ACCDEs are fine if that's what you want. It can make debugging on the clients' PCs more complicated though. It's certainly good discipline to know that what you're releasing can be made into an ACCDE file.

A couple of other points to make :

1. I'm pretty sure I've made this point (Require Variable Declaration) to you before but there's no immediate sign you've taken it on board, and it's crucially important, so I'll make it again. Never use code without the Option Explicit line. It's asking for trouble and most experts will see that and run a mile.
2. Where possible (It's always possible.) don't include passwords in your code in plain text.
   RC4 Encryption Algorithm for VBA and VBScript can be used to ensure at least some basic encryption and obscuring of your password. I posted my version at post #14 which has all the hard work done for you.
3. Line #8 of the block showing ReLinkTables() has a variable declared simply as TableDef instead of DAO.TableDef.
4. Your error handling in SpecialKeys() will stop processing the list of Properties after the first error. The error routine should say :
   

   PropError:
       Set prp = db.CreateProperty(strPrp, dbBoolean, blStatus)
       Call db.Properties.Append(prp)
       Resume Next

[BHo15]

Good thoughts NeoPa.

1. You have indeed told me about Require Variable Declaration, and I must have overlooked it. The good news is that I always declare variables, but having Option Explicit will catch me if I forget.
2. BUSTED!!! I knew that I had a password hard coded in there (laziness), but didn't realize I sent it to you all with my code. I am using RC4 with the database (user logins), and need to just convert this little string of code to that. My bad.
3. Good catch on Dim T as TableDef. I'll get that fixed.
4. I will make the correction on my SpecialKeys sub. As you can probably tell, I got that code from Google.

[NeoPa]

BHo15:
2. BUSTED!!! I knew that I had a password hard coded in there (laziness), but didn't realize I sent it to you all with my code.

Not any more. I fixed that for you so it just says {MyPassword}. You're looking for line #18 of your second block of code.

[BHo15]

It appears that my problems are due to the front end database not being linked to a backend when the user opens it. My login form relies on DLookups into the user table to make sure that the user is legitimate before it lets them in. If no backend database, no go on the DLookup.

I initially looked for a way to create a linked database through code, but didn't find anything. I only found ways to re-link tables which is what I did. Do you guys know how I could have code create the front end/back end database, so that I can just send her an unlinked database to start with?

[twinnyfo]

I have a method for setting the local links to the shared network location. I will have to wait until I get to work tomorrow. Once I make the accde I run this code which sets up the front end for public deployment. It’s pretty nifty.

[BHo15]

That would be great. Just send it my way when you get it. That will in theory allow me to create an ACCDE that is just a local database, and then give her a button on her switchboard that will create the linked tables onto her network.

[NeoPa]

My idea would be not to handle security with data in a table but to get the basic acceptability of the user from the Domain Account first instead (Function to Return UserName (NT Login) of Current User).

To link a single table first you could have some code to do something very similar to your ReLinkTables() routine but that only works on that one table you need. You'd need some way of knowing where to link it to though. Perhaps a local table in the FE. Only you know enough of the details to know how that could work at this point.

[BHo15]

I like the idea of just grabbing the username of the logged on user. Would I just check the username against the table to see if they are an authorized user? No need to check a password then.

[NeoPa]

BHo15:
Would I just check the username against the table to see if they are an authorized user?

Yes. That's exactly it. Password and authentication already handled for you by the system.

What you do with that ID is exactly parallel to what you'd do to any ID entered by the user. If you have a table indicating what they're allowed to do then you still have such a table and use it the same way.

Make the table local ;-)

[NeoPa]

Actually, if you want to look into it more deeply, there's an article (How To Create User Permissions and Customized Menus in MS Access) by someone you have already come across that goes into it in great depth. It even explores ways of using it more fully. I think you may like it.

[twinnyfo]

Thanks for the Props, NeoPa! I was also going to suggest that article.

However, this article on Working with Front Ends and Back Ends is what I was thinking for the original issue. Please also direct yourself to the additional thread links in Post #2 of that Article.

I am not sure of your level of expertise with Access/VBA, and these articles we've directed you to are a lot to digest, but I have been using these three tricks for a handful of years and the reduction in headaches is noticeable. Now I can focus on glitches, rather than trying to properly set up my DB and publish it.

Hope this (these) hepps!

[BHo15]

Thank you both.

Twinnyfo... Unless I am reading incorrectly, it appears that the code that you sent (Working with Front Ends and Back Ends) is doing what I was doing (although in a much snazzier way). It appears to be relinking tables as opposed to linking tables for the first time. Am I misreading it? I'm getting that from this section...

        If Not ((Left(tdf.Name, 1) = "~") Or _
            (Right(tdf.SourceTableName, 1) = "$") Or _
            tdf.Connect = "") Then
            strDBName = GetDBName(tdf.Connect)
            RelinkTable dbACC, tdf.Name, strLink & strDBName
        End If

My need is to write code that would allow the user to click a button and create a brand new backend database.

Thoughts?

[BHo15]

And NeoPa... I began playing with the code for getting users logged in by looking at their Windows login last night. VERY NICE! It's like single sign-on.

[twinnyfo]

BHo15,

Yes, you are correct. That is exactly what it is doing. From my perspective, you would want to compile the DB, relink the FE tables to the network location, then deploy the FE. Then, when a user click to link to the command script, it download the most recent version of the FE and executes the FE from the local machine. But, because the links have already been established, there is no need for the user to re-link them.

An important principle here is that you should have ONE BE in ONE, shared, network location and you can have many locally-executed FEs that are all linked to that same BE. Make sense?

Hope this hepps!

[BHo15]

Yes, it does make sense. Unfortunately, my fellow IT Security folks at the hospital that I am writing this database for, do not want to let me have the network path that the database will live on. Therefore, I cannot set a link to the backend. I am wanting the user to be able to do this once she receives the database.

Make sense?

[NeoPa]

BHo15:
My need is to write code that would allow the user to click a button and create a brand new backend database.

What did you mean by this statement? It's neither compatible with your latest statement (I cannot set a link to the backend. I am wanting the user to be able to do this once she receives the database.) nor does it make any practical sense.

If, as I suspect, the latest statement is a better reflection of your needs, then you could store the path to the required BE in your [tblUser] table, along with each [UserID], and thereby have a test version for yourself along with a live version for your user. As soon as the FE starts up it authenticates the user and, using that ID, relinks the BE tables depending on which BE is assigned.

Does that all make sense?

[twinnyfo]

my fellow IT Security folks at the hospital that I am writing this database for, do not want to let me have the network path that the database will live on

I love IT sticklers.... I fully understand health information protection--don't get me wrong. But they need to trust their IT folks to allow them to do their jobs.

It's like asking someone to clean all the objects in a museum, but not allowing them access because you think they will steal everything.

[BHo15]

My apologies for being ambiguous NeoPa.

My desire is to send the user a single database that has no linked tables. The database will have a button for the user to click, that will set up a backend for the database on the network.

[BHo15]

I don't WHY I didn't find this in the first go-round, but I just found something that will work.

DoCmd.RunCommand (acCmdDatabaseSplitter)

I can add this to a button on the switchboard, and then she can create a backend database on her network.

Sigh...

Anyway fellas... Thanks for the help.

[twinnyfo]

Apparently an easy solution for a very unique situation. Thanks for posting the details!