By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,422 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,422 IT Pros & Developers. It's quick & easy.

Help with some VB Code

P: n/a
Here is what I have so far, it loops while the PW is incorrect, or
until cancel is selected. I want it to lock the tables for adds,
deletes, and edits when cancel is selected, and if the PW is correct.

I want this to run when the DB is opened, I would also like the text
box to be starred (*) out when text is input.

Thanks for your help.

==============================
Dim Show_Box As Boolean
Dim Response As String

' Set the Show_Dialog variable to True.
Show_Box = True
InputBox
' Begin While loop.
While Show_Box = True

' Show the input box.
Response = InputBox("Enter the Password to Unlock Read-Only
Mode.", _
"Read-Only Unlocking")
' See if Cancel was pressed.
If Response = "" Then

' If Cancel was pressed,
' break out of the loop.
Show_Box = False
Else
' Test Entry to find out if it is the correct password.
If "safety" = Response = True Then
'Application.CloseCurrentDatabase
'Application.DBEngine.OpenDatabase "P:\ACTION REGISTER
\TESTERIncidet Action Register Database.mdb", , True
'Application.OpenCurrentDatabase "P:\ACTION REGISTER
\TESTERIncidet Action Register Database.mdb", False
'Access.AcOpenDataMode.acReadOnly
Show_Box = False
Else
' If the entry was wrong, show an error message.
MsgBox "Wrong Password, Try again."
End If
End If
' End the While loop.
Wend
==============================================
Jun 27 '08 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Hello,

The functionality you are trying to achieve with your code is not
supported in VBA because it requires a multithreaded environment which
would involve programming in an Object Oriented Programming platform
(OOP -- C#, VB.Net, Java...). VBA is not Object Oriented -- it is
Interpreted. The VBA compiler interprets the code as it runs thus not
requiring to compile the code into an exe which makes programming and
debugging as easy as can be. The limitation of Interpreted programming
code is that you can't perform OOP operations. It is either one or the
other. For the functionality you desire -- I would try VB.Net -- it is
the easiest OOP language to use.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #2

P: n/a
"Rich P" <rp*****@aol.comwrote in message
news:12*************@news.newsfeeds.com...
Hello,

The functionality you are trying to achieve with your code is not
supported in VBA because it requires a multithreaded environment
No, the simple code posted does not need threading. The looping is ONLY
there for re-prompting the user, not the requiremnt to span a new thread.
which
would involve programming in an Object Oriented Programming platform
No, opp and threading are separate issues. The idea, concpet and
implementation of a threaded language does not require that it be an object
oriented platform at all. The idea of having threaded code does not require
you to have a opp lanaguge.
(OOP -- C#, VB.Net, Java...). VBA is not Object Oriented -- it is
Interpreted.
I hate to burst your bubble but c#, and the .net actually now use a runtime
p-code system, and is in fact compiled down into a intermediate language
that is interpreted by the CLR (common language runtime). So in this case
C#, vb.net, and VBA actually are now all compiled to an intermediate
language, or what is called a p-code system. What this means is none of
these languages actually now compile down to into true .exe files that the
intel Processor can consume. This means that your file extension of .exe, or
mdb, or whatever you choose is in fact a moot point at this point in time
now.

It is interesting that us VBA developers for years and years have been using
a p-code interpreted system, and now both c# and vb.net use a similar
architecture.
The VBA compiler interprets the code as it runs thus not
requiring to compile the code into an exe which makes programming and
debugging as easy as can be.
Once again this is complete wrong. In fact debugging systems tend to work
better when you have an intermediate p-code language. While the dot net
compilers appear to produce a .exe file, this is actually not a true
compiled file, and is NOT a native executable file. In fact the.net
debugging systems use the p-code intermediate language to their benefit, and
not their detriment as you're suggesting.

VBA does have a compiler, and it does compile it down to a p-code lanague
that is then interpreted by runtime system. So, for all of the syntax error
checking, compiling, and removal of comments from the source code, this
process is much the same in VBA as it is now in .net.

The resulting compiled file in vb.net, or in ms-access results in a p-code
(intermediate language) that then gets executed an runtime system

.. c#, and vb.net (or any other language you use that converts into the
common runtime language) STILL requires you have the.net library or runtime
system installed to interpret that code. It is not natively compiled, and
therefore this also explains why you can do x-copy development in vb.net (or
in ms-access) after you've installed the appropriate libraries that can
interpret the code that gets created by these systems.

I should point out that c++, or the even the last version of visual basic
6.0 did have a native compiler, and these two systems could in fact produce
true .exe files that could be interpreted by the Intel processor direclty
(this is TRUE native .exe's now). These resulting files of course did
require access to the windows api, but there was not an intermediate
interpreter (p-code) system like you have with .net.

The limitation of Interpreted programming
code is that you can't perform OOP operations.
Once again this is wrong, because .net is *like* an interpreted language.
Java is considered a oop language, and it does not produce native
executables in most implementations either Once you've compiled to the
p-code system, then the idea here is that supposedly your code is platform
more portable. As long as you write a p-code interpreter for that platform,
all of your code should work.

I should point out this is also why it for example you can use visual studio
to produce .net code for the mobile edition of windows (which by the way,
does not have an Intel compatible processor), or the XNA for the xbox.
It is either one or the
other.
No, it is not one choice or the other when you choose an interpreted versus
that of a compiled language. OOP operations are not exclusive to compiled
languages only.

As I pointed out the .net system is in fact interpreted language system. I
should point of course that the CLR has its own compiler that is independent
of what language you program in. The CLR interpreter does in fact have a JIT
(just in time compiler) that'll eventually take that p-code and compiles it
into the native language of your machine (in this case the Intel processor),
So I will admit that how the CLR functions is not a true p-code architecture
in the classic sense of Java, or VBA.

I'm open to being corrected on this architecture issue, but it is my
assumption that your .exe files created by the .net library in is fact a CLR
p-code .exe, and is not a native true compiled language until the JIT gets
its hands on this .exe file. You can however pre compiled.net applications
using the ngen utility.

The issue of having a full opp language, or having a language that supports
threading, has no relationship to the fact if the language is a native
compiled language, or a p-code interpreted language at all.

**Both** both of those types of languages can well implement opp features,
and also that of threading.

I should point out the MS access does allow you to create and build your own
class objects. However, the vba in access is not considered a full opp
language because it's missing features like inheritance, and a number of
other things what would give it a full oop badge.

You can read my article on building and using class objects in MS access
here:
http://www.members.shaw.ca/AlbertKal.../WhyClass.html

Keep in mind that MS access shares the same compiler as vb 6.0 but simply
does not allow us to produce a native.exe like vb 6.0 did (vb 6 allowed the
creation of both native .exe executables, or the choice of using p-code --
Once again an issue that's been dependent of oop, or threading).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Jun 27 '08 #3

P: n/a
On May 5, 3:07*pm, colemanj4 <colema...@gmail.comwrote:
Here is what I have so far, it loops while the PW is incorrect, or
until cancel is selected. I want it to lock the tables for adds,
deletes, and edits when cancel is selected, and if the PW is correct.

I want this to run when the DB is opened, I would also like the text
box to be starred (*) out when text is input.

Thanks for your help.

==============================
*Dim Show_Box As Boolean
* * Dim Response As String

* * ' Set the Show_Dialog variable to True.
* * Show_Box = True
InputBox
* * ' Begin While loop.
* * While Show_Box = True

* * * * ' Show the input box.
* * * * Response = InputBox("Enter the Password to Unlock Read-Only
Mode.", _
* * * * * * "Read-Only Unlocking")
* * * * ' See if Cancel was pressed.
* * * * If Response = "" Then

* * * * * * ' If Cancel was pressed,
* * * * * * ' break out of the loop.
* * * * * * Show_Box = False

* * * * Else
* * * * * * ' Test Entry to find out if it is the correct password.
* * * * * * If "safety" = Response = True Then
* * * * * * * * 'Application.CloseCurrentDatabase
* * * * * * * * 'Application.DBEngine.OpenDatabase "P:\ACTION REGISTER
\TESTERIncidet Action Register Database.mdb", , True
* * * * * * * * 'Application.OpenCurrentDatabase "P:\ACTION REGISTER
\TESTERIncidet Action Register Database.mdb", False
* * * * * * * * 'Access.AcOpenDataMode.acReadOnly
* * * * * * * * Show_Box = False
* * * * * * Else
* * * * * * * * ' If the entry was wrong, show an error message.
* * * * * * * * MsgBox "Wrong Password, Try again."
* * * * * * End If
* * * * End If
* * * ' End the While loop.
* * * Wend
==============================================
OK, since what I want to do doesn't seem possible, is there a way I
could set the current user to user level access instead of admin? I
don't want ot get into doing all of the security wizard access has, as
I have tried it multiple times and it is not working. I believe that
is because of the way my corporate IT has the permissions set up for
the whole network, and they are a pain to get involved with.

So... say doej is the user, he opens up the DB, and is asked for a
password, he hits cancel and I then want to delegate him to the "User
group" which only has readonly access. Can I do this with my VBA
code?

Thanks
Jun 27 '08 #4

P: n/a

Excellent response, Albert. I can't think of anything to add except a
resounding expression of agreement.
On May 6, 2:00*am, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
"Rich P" <rpng...@aol.comwrote in message
I hate to burst your bubble but c#...
Jun 27 '08 #5

P: n/a
On Tue, 06 May 2008 06:00:43 GMT, "Albert D. Kallal"
<Pl*******************@msn.comwrote:
In fact, you *can* do multithreading with VBA, same as with VB6. For
example see here: http://www.freevbcode.com/ShowCode.Asp?ID=1287
and heed that warning not to do it unless you are an expert
programmer!

-Tom.

>"Rich P" <rp*****@aol.comwrote in message
news:12*************@news.newsfeeds.com...
>Hello,

The functionality you are trying to achieve with your code is not
supported in VBA because it requires a multithreaded environment

No, the simple code posted does not need threading. The looping is ONLY
there for re-prompting the user, not the requiremnt to span a new thread.
>which
would involve programming in an Object Oriented Programming platform

No, opp and threading are separate issues. The idea, concpet and
implementation of a threaded language does not require that it be an object
oriented platform at all. The idea of having threaded code does not require
you to have a opp lanaguge.
>(OOP -- C#, VB.Net, Java...). VBA is not Object Oriented -- it is
Interpreted.
<clip>
Jun 27 '08 #6

P: n/a
I have writtens lots of multi threaded programs. The only way you can
do it in VBA is like this:

"It can't be done!"

I have been posting in this newsgroup for several years. I have
probably not been on planet earth as long as a lot of you, but I have
taken the coding thing a little bit further. If what I say was not true
and correct, I would have disappeared a long time ago - from attrition -
as in no one would need my services. You can't do multi threading in
VBA - I have tried. The closest I came was to create to separate exe's
in VB6 where one exe monitored the operations of the other. But that is
not VBA, and that was in the previous century.

It is one thing to be an Access coder, and if that is the extent of your
horizons, fine. But for those people who need functionality that is
either not provided in this environment - or not provided easily - it is
time to step up to something a little more sophisticated. My goal is to
share how to achieve desired results with as little spaghetti code as
possible. If an operation requires tons of spaghettie code (API code,
etc) then it is time to start investigating other technologies.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #7

P: n/a
Silly me! I am tooting my horn about how VBA doesn't support
multithreading and that you can only do it in OOP. I forgot to mention
that multithreading requires the use of delegates - here is some sample
code of a program I wrote which does something similar to what this post
is about - being able to control the execution of an operation -
start/stop:
Public Delegate Sub StartSlideShowDelegate()
Private Delegate Sub ShowPicsDelegate(ByVal PicCount As
Integer, ByVal str2 As String)
...

btnStopStart.Text = "Stop SlideShow"
btnSinglePicBack.Enabled = False
btnSinglePicFwd.Enabled = False
bRunPics = True
Dim dlgt As StartSlideShowDelegate = New
StartSlideShowDelegate(AddressOf StartLoadSlideShow)
Dim state As Integer = 0
Dim ar As IAsyncResult = dlgt.BeginInvoke(New AsyncCallback
(AddressOf DoneCallback), state)
...

Try
If Me.InvokeRequired Then
' if operating on a thread, invoke a delegate
' on the UI thread.
Dim omd As ShowPicsDelegate = New ShowPicsDelegate
(AddressOf ShowPics)
Dim arx As IAsyncResult = Me.BeginInvoke(omd, New
Object() {PicCount, str2})
Me.EndInvoke(arx)
Return
End If
...

--you can't do this in VBA. You can only do this in OOP.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #8

P: n/a
"Rich P" <rp*****@aol.comwrote in message
news:12**************@news.newsfeeds.com...
>I have writtens lots of multi threaded programs. The only way you can
do it in VBA is like this:

"It can't be done!"

I for the most part 100% agree with the above.
If what I say was not true
and correct, I would have disappeared a long time ago - from attrition -
Your claim about VBA and threading is 100% correct. however, you claim of to
quote:
>The functionality you are trying to achieve with your code is not
supported in VBA because it requires a multithreaded environment which
would involve programming in an Object Oriented Programming platform

You DO NOT need to adopt a OOP platform to get threading. Without question
*most* modern development systems that supprot oop *useally* also support
threading, but, your above claim says that you **have** to use a OOP system
to get multithread enviroment is wrong. I was simply pointing out that these
are two mutually exclusive issues, and are not related to each other. Once
again, you do not necessarily have to adopt a oop language to get
multithreading capability.
>The limitation of Interpreted programming
code is that you can't perform OOP operations.

Once again I said the above is incorrect, and there are many interpreted
languages is today that are full oop. And, I pointed out that in fact the
..net environment relies on a p-code runtime system, very similar to that of
the vba. (and I believe on windows mobile, and also the XNA system for xbox,
there is no JIT either).

I was never questioning you're correct suggestion that threading in VBA is
silly, and the wrong tool and one is barking up the wrong tree to attempt
threading in VBA. Once again, I wholeheartedly agree with you on this issue.
It is silly and simply not the realm of VBA to write and build threaded
applications.

I should further point out that if I'm reading the original poster's
question correctly, he does not need threading as a solution anyway...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Jun 27 '08 #9

P: n/a
Actually, I have to run right now...but, let me take a look at this later
tonight....

I do think something can be cooked up here.....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Jun 27 '08 #10

P: n/a
Application.CloseCurrentDatabase
Application.DBEngine.OpenDatabase "P:\ACTION REGISTER
\TESTERIncidet Action Register Database.mdb", , True

This code runs within the current database.
The first line closes the current database.
How can the second line run when the its application is closed?

!!!!!Perhaps!!!!! this is what Rich P is talking about when he
suggests that multi-threading is required to make this work.

In any case this reminds me of the problem around compacting the
current db from code in earlier versions of Access. We must close the
db and then compact. Once we close the db, the rest of the code, the
compacting, never runs. Hacks for this, my own included, create and
open, in code, a second db which is populated with the information
required to compact and then reopen the first and close itself; that
db is coded to do exactly that on open; all that is required is a few
seconds sleep. I suspect that if it were worthwhile to do so,
something like that could be cobbled up for this situation.

But why would one do this? It's been my experience that application-
level security is no security at all. If one wants to restrict the
editing of forms one can run a standard procedure on opening all forms
taking the form as a parameter, which checks some database property or
global variable established on opening and sets the allow edits,
insets properties appropriately. With an MDE and some window hiding
this can prevent casual unauthorized editing.

There are various levels of JET security that can be achieved. Almost
all can be beaten, and it's a trivial task to beat most.


Jun 27 '08 #11

P: n/a
lyle fairfield <ly************@gmail.comshould have written in
news:78**********************************@m36g2000 hse.googlegroups.com:
Application.CloseCurrentDatabase
Application.DBEngine.OpenDatabase "P:\ACTION REGISTER
\TESTERIncidet Action Register Database.mdb", , True

This code runs within the current database.
The first line closes the current database.
How can the second line run when the database where the code (pcode)
exists, is closed?
Jun 27 '08 #12

P: n/a
On May 7, 7:01*am, lyle fairfield <lylef...@yah00.cawrote:
lyle fairfield <lyle.fairfi...@gmail.comshould have written innews:78**********************************@m36g20 00hse.googlegroups.com:
Application.CloseCurrentDatabase
Application.DBEngine.OpenDatabase "P:\ACTION REGISTER
\TESTERIncidet Action Register Database.mdb", , True
This code runs within the current database.
The first line closes the current database.

How can the second line run when the database where the code (pcode)
exists, is closed?
it can't, I tried it, realized I was retarded, and commented it out to
make sure I don't try it again.
Jun 27 '08 #13

P: n/a
colemanj4 <co*******@gmail.comwrote in
news:05**********************************@m44g2000 hsc.googlegroups.com:
On May 7, 7:01*am, lyle fairfield <lylef...@yah00.cawrote:
>lyle fairfield <lyle.fairfi...@gmail.comshould have written
innews:78809
1d*****************************@m36g...le groups.com:
>>
Application.CloseCurrentDatabase
Application.DBEngine.OpenDatabase "P:\ACTION REGISTER
\TESTERIncidet Action Register Database.mdb", , True
This code runs within the current database.
The first line closes the current database.

How can the second line run when the database where the code (pcode)
exists, is closed?

it can't, I tried it, realized I was retarded, and commented it out to
make sure I don't try it again.
Did it only take once? I tried similar hundreds of times in the late
nineties trying to compact with code.

Jun 27 '08 #14

P: n/a
>>
>The limitation of Interpreted programming
code is that you can't perform OOP operations.

Once again I said the above is incorrect, and there are many interpreted
languages is today that are full oop. And, I pointed out that in fact
the ..net environment relies on a p-code runtime system, very similar to
that of the vba. (and I believe on windows mobile, and also the XNA
system for xbox, there is no JIT either).
<<

Your point is precisely my point --
>>
there are many interpreted languages is today that are full oop
<<

When something cannot be achieved in VBA - or achieved easily
>>there are many interpreted languages is today that are ..
it is time to investigate other technologies. I believe Ruby On Rails
is one of these OOP interpreted languages (OK, I see your point on my
comment about interpreted languages - so I meant just VBA). I don't
happen to be familiar with Ruby except that I don't think it would be as
easy to integrate with Access as a .Net solution. I am fairly familiar
with .Net because I have been using it for the last 6+ years. The
suggestions I provide are all Microsoft based since Access is Microsoft
and just about all the computer technology I use is created by
Microsoft.

Just an aside: sql server 2005/.Net (and sql 2008/.Net more so) is
taking the Access paradigm to the next level where - again - you have an
integrated database development environment - except fully OOP/server
based and supports multiple languages (C# and VB.Net being the most
popular).

Access is kind of like the grandfather of the new(er) database paradigms
Microsoft is developing. And like grandpa, Access is encountering
various limitations. And Microft, I read, is outselling Oracle with its
newer paradigms.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #15

P: n/a
On May 6, 9:56*pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
Actually, I have to run right now...but, let me take a look at this later
tonight....

I do think something can be cooked up here.....

--
Albert D. Kallal * *(Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKal...@msn.com

Below is what I have now, I have defined the function
'NetworkUserName' to return the login name of the user. I want (where
the comments are) to change their user type (as defined in the *.ldb)
to be User instead of Admin, as User only has read only access to the
DB.

Been searching how to do that and can't find it, any help you could
provide would be awesome.
Sub LockDB()

Dim Show_Box As Boolean
Dim Response As String
Dim userName As String

userName = NetworkUserName()

Show_Box = True

While Show_Box = True

Response = InputBox("Enter the Password for Admin. Cancel to
Enter as Read-Only", _
"Read-Only Unlocking")

'See if Cancel was pressed.
If Response = "" Then

'Set userName to the User group HERE
MsgBox "You can look at anything, but change nothing"
Show_Box = False
Else
'Test Entry to find out if it is the correct password.
If ("safety" = Response) Then

MsgBox "You are granted Admin rights"
'set userName to the Admin group HERE
Show_Box = False

Else

' If the entry was wrong, show an error message.
MsgBox "Wrong Password, Try again."

End If
End If
Wend

End Sub

Thanks
Jun 27 '08 #16

This discussion thread is closed

Replies have been disabled for this discussion.