423,688 Members | 1,889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

Blank screen when opening a database

BHo15
100+
P: 132
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...
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Form_Close()
  4.     Call SpecialKeys(False)
  5. End Sub
  6.  
  7. Private Sub Form_Load()
  8.     On Error GoTo Ooops
  9.     Dim tdf As DAO.TableDef
  10.     Dim strP As String
  11.         strP = DLookup("P", "tblP")
  12.     Dim strPath As String
  13.     DoCmd.OpenForm "frmLogin"
  14.     If DLookup("LinkedTablesYN", "tblLinkedTablesYN") = True Then
  15.         DoCmd.OpenForm "frmLogin"
  16.     ElseIf DLookup("LinkedTablesYN", "tblLinkedTablesYN") = False Then
  17.         DoCmd.OpenForm "frmLogin"
  18.         MsgBox ("It appears that you need to relink the tables.")
  19.         Call ReLinkTables
  20.         DoCmd.SetWarnings False
  21.         DoCmd.RunSQL "UPDATE tblLinkedTablesYN SET LinkedTablesYN = True"
  22.     Else
  23.         DoCmd.OpenForm "frmLogin"
  24.         MsgBox ("You may have issues with linked tables." & Chr(10) & "Check with your database administrator.")
  25.     End If
  26.     Set tdf = Nothing
  27.     Exit Sub
  28.  
  29. Ooops:
  30.     Forms!frmLogin.txtErrNumb = Err.Number
  31.     Forms!frmLogin.txtErrDescrip = Err.Description
  32.     Forms!frmLogin.txtErrLoc = "Form_Load - frmHangout"
  33.     MsgBox ("There has been an error. " & Chr(10) & "If it continues, let your manager know.")
  34.     DoCmd.SetWarnings False
  35.     DoCmd.OpenQuery "qryAppendErrors"
  36.     DoCmd.SetWarnings True
  37.     Forms!frmLogin.txtErrNumb = ""
  38.     Forms!frmLogin.txtErrDescrip = ""
  39.     Forms!frmLogin.txtErrLoc = ""
  40.     DoCmd.OpenForm "frmLogin"
  41.     Exit Sub
  42. End Sub
Here is the code for ReLinkTables...
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Sub ReLinkTables()
  4.     On Error GoTo Ooops
  5.     Dim dbs As DAO.Database
  6.         Set dbs = CurrentDb()
  7.     Dim tdf As DAO.TableDef
  8.     Dim T As TableDef
  9.     Dim strPath As String
  10.  
  11.     MsgBox ("You will find the backend database in this folder search.")
  12.     strPath = FindFile
  13.  
  14.     For Each T In dbs.TableDefs
  15.         If T.Attributes = 0 Then GoTo NextT 'This makes it skip unlinked tables
  16.         If LCase(Left(T.Name, 4)) = "msys" Or Left(T.Name, 1) = "~" Then GoTo NextT
  17.         Set tdf = dbs.TableDefs(T.Name)
  18.         tdf.Connect = ";DATABASE=" & strPath & ";PWD={MyPassword}"
  19.         tdf.RefreshLink
  20.  
  21. NextT:
  22.     Next T
  23.         Set tdf = Nothing
  24.         Set dbs = Nothing
  25.  
  26.     MsgBox ("The tables have been relinked")
  27.     Exit Sub
  28. Ooops:
  29.     Forms!frmLogin.txtErrNumb = Err.Number
  30.     Forms!frmLogin.txtErrDescrip = Err.Description
  31.     Forms!frmLogin.txtErrLoc = "ReLinkTables - mdlReLinkTables"
  32.     MsgBox ("There has been an error. " & Chr(10) & "If it continues, let your manager know.")
  33.     DoCmd.SetWarnings False
  34.     DoCmd.OpenQuery "qryAppendErrors"
  35.     DoCmd.SetWarnings True
  36.     Forms!frmLogin.txtErrNumb = ""
  37.     Forms!frmLogin.txtErrDescrip = ""
  38.     Forms!frmLogin.txtErrLoc = ""
  39.     Exit Sub
  40. End Sub
And finally... Here is the code for SpecialKeys...
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Sub SpecialKeys(blStatus As Boolean)
  4.     On Error GoTo PropError
  5.     Dim prp As DAO.Property
  6.     Dim db As DAO.Database
  7.         Set db = CurrentDb()
  8.     Dim strPrp As String
  9.  
  10.     strPrp = "StartUpShowDBWindow"
  11.     db.Properties(strPrp) = blStatus
  12.     strPrp = "AllowBreakIntoCode"
  13.     db.Properties(strPrp) = blStatus
  14.     strPrp = "AllowSpecialKeys"
  15.     db.Properties(strPrp) = blStatus
  16.     strPrp = "AllowToolbarChanges"
  17.     db.Properties(strPrp) = blStatus
  18.     strPrp = "AllowFullMenus"
  19.     db.Properties(strPrp) = blStatus
  20.     strPrp = "AllowBuiltInToolbars"
  21.     db.Properties(strPrp) = blStatus
  22.     strPrp = "AllowByPassKey"
  23.     db.Properties(strPrp) = blStatus
  24.     strPrp = "AllowShortcutMenus"
  25.     db.Properties(strPrp) = blStatus
  26.     Exit Sub
  27.  
  28. PropError:
  29.     Set prp = db.CreateProperty(strPrp, dbBoolean, blStatus)
  30.     db.Properties.Append prp
  31.     Exit Sub
  32.  
  33. 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.
Apr 5 '18 #1
Share this Question
Share on Google+
25 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,667
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.
Apr 5 '18 #2

BHo15
100+
P: 132
Good thoughts twinnyfo. There is no reason that I could not create and send an ACCDE. I'll give that a try.
Apr 5 '18 #3

BHo15
100+
P: 132
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?
Apr 5 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 2,667
File

Save As

Make ACCDE

That should do it....
Apr 5 '18 #5

BHo15
100+
P: 132
That did it. Thanks (It's been a while since I have had a need to create an ACCDE... Could you tell? :)
Apr 5 '18 #6

NeoPa
Expert Mod 15k+
P: 31,084
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 :
    Expand|Select|Wrap|Line Numbers
    1. PropError:
    2.     Set prp = db.CreateProperty(strPrp, dbBoolean, blStatus)
    3.     Call db.Properties.Append(prp)
    4.     Resume Next
Apr 5 '18 #7

BHo15
100+
P: 132
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.
Apr 5 '18 #8

NeoPa
Expert Mod 15k+
P: 31,084
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.
Apr 5 '18 #9

BHo15
100+
P: 132
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?
Apr 5 '18 #10

twinnyfo
Expert Mod 2.5K+
P: 2,667
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.
Apr 5 '18 #11

BHo15
100+
P: 132
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.
Apr 5 '18 #12

NeoPa
Expert Mod 15k+
P: 31,084
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.
Apr 5 '18 #13

BHo15
100+
P: 132
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.
Apr 5 '18 #14

NeoPa
Expert Mod 15k+
P: 31,084
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 ;-)
Apr 6 '18 #15

NeoPa
Expert Mod 15k+
P: 31,084
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.
Apr 6 '18 #16

twinnyfo
Expert Mod 2.5K+
P: 2,667
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!
Apr 6 '18 #17

BHo15
100+
P: 132
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...
Expand|Select|Wrap|Line Numbers
  1.         If Not ((Left(tdf.Name, 1) = "~") Or _
  2.             (Right(tdf.SourceTableName, 1) = "$") Or _
  3.             tdf.Connect = "") Then
  4.             strDBName = GetDBName(tdf.Connect)
  5.             RelinkTable dbACC, tdf.Name, strLink & strDBName
  6.         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?
Apr 6 '18 #18

BHo15
100+
P: 132
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.
Apr 6 '18 #19

twinnyfo
Expert Mod 2.5K+
P: 2,667
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!
Apr 6 '18 #20

BHo15
100+
P: 132
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?
Apr 6 '18 #21

NeoPa
Expert Mod 15k+
P: 31,084
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?
Apr 6 '18 #22

twinnyfo
Expert Mod 2.5K+
P: 2,667
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.
Apr 6 '18 #23

BHo15
100+
P: 132
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.
Apr 6 '18 #24

BHo15
100+
P: 132
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.
Apr 6 '18 #25

twinnyfo
Expert Mod 2.5K+
P: 2,667
Apparently an easy solution for a very unique situation. Thanks for posting the details!
Apr 6 '18 #26

Post your reply

Sign in to post your reply or Sign up for a free account.