Front End/Back End

[Rick Brandt]

No, what I'm saying is that after I design the .MDB to be in User
Interface

This is where you lose me. All of the settings in startup that hide the
normal Access interface from the user are completely bypassable as well as
reversable. When you create custom menus and toolbars "for the user" those
are stored in the file. The toolbars that are built into Access are just
that, built into Access. They are not part of the file so there is no way
to remove them from the file permanently.

Lets start with just the stuff in the Startup settings.

You open your MDB and you set all of those properties so that the normal
Access interface is hidden when you open the file. This is the interface
your users will see when they open their MDE file. Now if you open that
file while holding the shift key you get the "normal" Access interface and
that is what you use when doing development on your MDB.

So each time you need to make design changes you open the form while holding
shift and do whatever you need to do. If you want to test it "as a user"
you close the file and reopen it without holding shift and then you will see
what the user will see. Once you are finished you can make an initial MDE
and on that file run the code that disables the shift key. You make copies
of this file for your users, but YOUR MDB file still has the use of the
Shift key so you can continue to use that for opening the file for future
development.

 

[Garret]

Bingo. The shift key thing was exactly what I was looking for. What I
meant before was that it was impossible to make changes since the MDB
looks exactly like the MDE, therefore having no options to create or
design any forms or reports. I thought I would have had to build the
interface all the way back to normal, do the changes, then build it all
the way back to User Interface, but the shift key saves loads of time.
Thank you so much, you have no idea how much I've learned from you.

 

[Garret]

Bingo. The shift key thing was exactly what I was looking for. What I
meant before was that it was impossible to make changes since the MDB
looks exactly like the MDE, therefore having no options to create or
design any forms or reports. I thought I would have had to build the
interface all the way back to normal, do the changes, then build it all
the way back to User Interface, but the shift key saves loads of time.
Thank you so much, you have no idea how much I've learned from you.
Lastly, how would one go about disabling the shift key?

 

[Garret]

Bingo. The shift key thing was exactly what I was looking for. What I
meant before was that it was impossible to make changes since the MDB
looks exactly like the MDE, therefore having no options to create or
design any forms or reports. I thought I would have had to build the
interface all the way back to normal, do the changes, then build it all
the way back to User Interface, but the shift key saves loads of time.
Thank you so much, you have no idea how much I've learned from you.
Lastly, how would one go about disabling the shift key?

 

[Garret]

How do I disable the shift key on the .MDE? This seems to be the only
problem remaining.

 

[Rick Brandt]

How do I disable the shift key on the .MDE? This seems to be the only
problem remaining.

See code at link...

http://www.mvps.org/access/general/gen0040.htm

 

[Garret]

Ok thanks, now where would I paste this code? (so that it runs at
startup, right?). It seems like an easy problem but I don't know what
to do.

 

[Rick Brandt]

Ok thanks, now where would I paste this code? (so that it runs at
startup, right?). It seems like an easy problem but I don't know what
to do.

Put it in a standard module. It needs to run exactly once, but having it
run on every startup would be harmless.

 

[Garret]

Alright, so if I put this code into the MDB before I make the MDEs, I
should be all set, assuming that I log in as an Admin each time?
Basically this code disables the shift key overall, unless it is an
admin who logs in, correct?

 

[Rick Brandt]

Alright, so if I put this code into the MDB before I make the MDEs, I
should be all set, assuming that I log in as an Admin each time?
Basically this code disables the shift key overall, unless it is an
admin who logs in, correct?

The code sets a property on the database that disables the shift key for all
*future* instances of the file being opened. That is why it only needs to be
run once. If you run the code in startup it actually does nothing to
disable the shift key until the file is opened at least once without using
the shift key. Unless you think someone is actually going to try the shift
key the very first time they open the file then that should work. If you
are worried about that then YOU can open it without the shift key before you
distribute it.

Also, it disables the shift key for ALL users, even those with administrator
permissions. However those users would be allowed to run another code
routine that would re-enable the shift key whereas a user without
administrator authority could not do so. This assumes you are also setting
up User Level Security on the file.

 

[Garret]

I tried testing this on backup copies, and every time I tried to build
the MDE after inserting the code, it gives me a message box that tells
me it was unable to build the MDE. I deleted the module containing the
code, and I could construct the MDE afterward, so it must be the source
of the problem. Am I doing this in the correct order:

- Split original database
- Insert code into MBD
- Create MDE

And this is all done without opening or closing anything.

 

[Rick Brandt]

I tried testing this on backup copies, and every time I tried to build
the MDE after inserting the code, it gives me a message box that tells
me it was unable to build the MDE. I deleted the module containing
the code, and I could construct the MDE afterward, so it must be the
source of the problem. Am I doing this in the correct order:

- Split original database
- Insert code into MBD
- Create MDE

And this is all done without opening or closing anything.

After inserting the code do a compile on the entire project. Access should
find and highlight any rows in the code that won't compile. Those errors
have to be corrected before you will be able to make an MDE.

 

[Garret]

I did a "Compact and Repair" (if thats what you meant by compile) on
the MDB after inserting the code, which happened successfully. Then I
tried to create the MDE and I still got the same message box that said
I was "unable to create an MDE database". Did I insert the code too
early anywhere or should I have run/not run the code?

 

[Rick Brandt]

I did a "Compact and Repair" (if thats what you meant by compile)

No, I mean compile. It's found in the Debug menu of the Menu bar when in a
code module.

 

[Garret]

I did that...sure enough it hit an error on the first line of real code
for the module that says:

Function ChangePropertyDdl(stPropName As String, _
PropType As DAO.DataTypeEnum, vPropVal As Variant) _
As Boolean

It errors me: "User-defined type not defined"

I'm clueless again...I copied and pasted the code in my module from the
link you previously posted, so there couldn't be anything missing.

 

[Rick Brandt]

I did that...sure enough it hit an error on the first line of real
code for the module that says:

Function ChangePropertyDdl(stPropName As String, _
PropType As DAO.DataTypeEnum, vPropVal As Variant) _
As Boolean

It errors me: "User-defined type not defined"

I'm clueless again...I copied and pasted the code in my module from
the link you previously posted, so there couldn't be anything missing.

It looks like that code requires a reference to the DAO library. If you go
to Tools - References is the DAO library one of the "Checked" ones? This
would be checked by Default in Access 97 and Access 2003, but not in Access
2000 or 2002.

 

[Garret]

I couldn't find a DAO on the list of things, but the ones that were
checked were:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Bennet-Tec MetaDraw Control

 

[Rick Brandt]

I couldn't find a DAO on the list of things, but the ones that were
checked were:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Bennet-Tec MetaDraw Control

Well, you have to scroll down the list of unchecked items until you find the
one for DAO 3.6 and check it. Then that code should compile.

 

[Garret]

Oh, it had the word Microsoft before it so I didn't find it right away.
Anyway, that worked and it compiled without any problem. I then made
the MDE, but I can still see the "normal" access interface while
holding the shift key during startup.

 

[Rick Brandt]

Oh, it had the word Microsoft before it so I didn't find it right
away. Anyway, that worked and it compiled without any problem. I
then made the MDE, but I can still see the "normal" access interface
while holding the shift key during startup.

Did you actually "run" the code? It is not enough for it to simply exist.
An again if you set something up to run it in Startup then it will only have
an effect after it has been run once.