Hi Sean: Thank you so much for this video!! It is exactly what I am looking for. Not sure if you are still answering questions about this, but I am having an issue when compiling the LockDB code as below and I can't quite figure out what I am doing wrong. (I am quite new to VBA programming.) The error is: "User-defined type not defined." The error is occurring the Dim db As database as below: Sub LockDb() On Error GoTo Sub_Error Dim db As database
@@seanmackenziedataengineering Hi Sean. Thanks for the quick reply!! That didn't work, but I think the issue may be that I am creating the Module in an existing database I created a while back as I created the module in a brand-new database with just a single table and it complied fine, so the issue is probably somewhere in my database.
Thanks for all your tutorials, they are very helpful, but I'm also getting run-time error '13' Type mismatch. Set prp = db.CreateProperty("AllowBypassKey", dbBoolean, True) and Dim prp as Property.
It seems like my comment got deleted for some reason... I just have a quick question, because I found the Command technique very interesting. I have a VBS file that launches an Access database with the Shell Run function. Is it possible to pass a command into that kinda like the way you do it in this video? I want to avoid the user opening the Access file without going through the VBS. Thank you!
Actually, this is a very good strategy! Using Command, you can pass any kind of instruction to your application on how it should open, what features should be exposed, etc. Good topic for a video.
This procedure worked for one database but doesn't work for others. Can you advise why? Sub LockDb() On Error GoTo MyErr Dim Db As Database Dim Prp As Property Set Db = CurrentDb Db.Properties("AllowBypassKey") = False Exit Sub MyErr: If Err.Number = 3270 Then Set Prp = Db.CreateProperty("AllowBypassKey", dbBoolean, True) Db.Properties.Append Prp Db.Properties("AllowBypassKey") = False Exit Sub End If End Sub
Shortcut files usually just point to a particular file or program. However, they also have the capability to have "switches" or arguments, so that a program or file will open a certain way. In this case, receiving the command to unlock itself when it opens.
When is sub lockDb run? Nevermind, wasn't watching closely enough, I see it is just manually run. Not something I have ever done. I think I will do it using a button.
I copied the code exactly as you have it. But it keeps failing on the db.Properties("AllowBypassKey") line. I print the error message in the immediate window, and I get 3270, but it's not falling through to the error code. I'm perplexed as I am a pretty experienced programmer in MSAccess.
@@seanmackenziedataengineering Sub LockDB() On Error GoTo Sub_Error Dim db As Database Dim prp As Property
Set db = CurrentDb db.Properties("AllowBypassKey") = False MsgBox "Lock Confirmed.", vbInformation, "Bypass" Exit Sub Sub_Error: Select Case Err Case 3270 Set prp = db.CreateProperty("AllowBypassKey", dbBoolean, True) db.Properties.Append prp db.Properties("AllowBypassKey") = False MsgBox "The bypass property was not found, so it was created.", vbInformation, "Bypass" Case Else MsgBox "There was an error. (" & Err & ") " & Error End Select End Sub
It has use cases where it works well. Great for fast prototypes or workgroup databases, but can be a pain to put into production with proper security etc. At a certain point you start looking for browser-based front-ends. I find that where Access excels in in data cleaning, wrangling, transformation, and joining many disparate data sources for analysis. For those in the know, it is much faster than using Python or other methods with desktop data. Here is a quick web front-end: ua-cam.com/video/TbAECDT7FFU/v-deo.html
Great job!! Full of good hints for the bypass key control. A must to add security to your code.
Thanks!
Thank you for your effort. I always following your all release. I am waiting for the topics on TAG and Tree Menu. Thank you once again.
Great idea for a video! I have a good technique for the treeview control.
Love all your videos! Commenting for the algorithm.
You're the best!
Great work Thank you!
Glad you liked it!
Hi Sean: Thank you so much for this video!! It is exactly what I am looking for. Not sure if you are still answering questions about this, but I am having an issue when compiling the LockDB code as below and I can't quite figure out what I am doing wrong. (I am quite new to VBA programming.) The error is: "User-defined type not defined." The error is occurring the Dim db As database as below:
Sub LockDb()
On Error GoTo Sub_Error
Dim db As database
Glad you're enjoying the channel! Try using DAO.Database instead of just Database. Sometimes that can cause an error.
@@seanmackenziedataengineering Hi Sean. Thanks for the quick reply!! That didn't work, but I think the issue may be that I am creating the Module in an existing database I created a while back as I created the module in a brand-new database with just a single table and it complied fine, so the issue is probably somewhere in my database.
Thank you🎉
Welcome!
Thanks for all your tutorials, they are very helpful, but I'm also getting run-time error '13' Type mismatch.
Set prp = db.CreateProperty("AllowBypassKey", dbBoolean, True) and Dim prp as Property.
I found the issue. I changed both Dim db and Dim prp to DAO...
I see you found the issue! Thanks for sharing the solution.
I was following your tutorial. How to make or where to get the Argument ? Back end?
Where can i see the access front-end file?
I have this simple access file that i need to protect the table and forms that i have built.
The argument is usually something you'll store as an admin. Maybe in a shortcut file or a text file, so you can use it to enable the bypass key again.
Take a look at this video too: ua-cam.com/video/M6VxBY9mt_s/v-deo.html
It seems like my comment got deleted for some reason... I just have a quick question, because I found the Command technique very interesting. I have a VBS file that launches an Access database with the Shell Run function. Is it possible to pass a command into that kinda like the way you do it in this video? I want to avoid the user opening the Access file without going through the VBS. Thank you!
Actually, this is a very good strategy! Using Command, you can pass any kind of instruction to your application on how it should open, what features should be exposed, etc. Good topic for a video.
@@seanmackenziedataengineering Thank you for the response! I'm glad you think it's a good strategy. A video on this would be awesome!
This procedure worked for one database but doesn't work for others. Can you advise why?
Sub LockDb()
On Error GoTo MyErr
Dim Db As Database
Dim Prp As Property
Set Db = CurrentDb
Db.Properties("AllowBypassKey") = False
Exit Sub
MyErr:
If Err.Number = 3270 Then
Set Prp = Db.CreateProperty("AllowBypassKey", dbBoolean, True)
Db.Properties.Append Prp
Db.Properties("AllowBypassKey") = False
Exit Sub
End If
End Sub
Try putting
*Else
MsgBox Err & ", " & Error*
just before your last End If
You may get an error on the other computers that you don't see now.
is it possible to apply the procedure to en external database... allowbypass to an external database
Yes you can, by opening that database from the current instance: ua-cam.com/video/hhPrBMLNiDs/v-deo.html
I didn't understand about the shortcut file. Can u understand me by going through browse option.
Shortcut files usually just point to a particular file or program. However, they also have the capability to have "switches" or arguments, so that a program or file will open a certain way. In this case, receiving the command to unlock itself when it opens.
Errors out for me on:
Set prp = db.CreateProperty(“AllowBypassKey”), dbBoolean, True)
Message: Run-time error ‘13’: Type mismatch
Looks like you have one extra ) in there. Try removing the one right after "AllowBypassKey"
You might Dim prp as Properties. Please Dim prp as Property.
It is interesting. Thanks
Glad you enjoyed it
How can I hide modules and vb code in front end please 🤔
The best way is to convert it to a .accde. Great idea for a video!! Thx
When is sub lockDb run?
Nevermind, wasn't watching closely enough, I see it is just manually run. Not something I have ever done.
I think I will do it using a button.
Got it - nice idea to use a button!
I copied the code exactly as you have it. But it keeps failing on the db.Properties("AllowBypassKey") line. I print the error message in the immediate window, and I get 3270, but it's not falling through to the error code. I'm perplexed as I am a pretty experienced programmer in MSAccess.
Interesting.. can you post your procedure? Maybe I can take a look.
@@seanmackenziedataengineering
Sub LockDB()
On Error GoTo Sub_Error
Dim db As Database
Dim prp As Property
Set db = CurrentDb
db.Properties("AllowBypassKey") = False
MsgBox "Lock Confirmed.", vbInformation, "Bypass"
Exit Sub
Sub_Error:
Select Case Err
Case 3270
Set prp = db.CreateProperty("AllowBypassKey", dbBoolean, True)
db.Properties.Append prp
db.Properties("AllowBypassKey") = False
MsgBox "The bypass property was not found, so it was created.", vbInformation, "Bypass"
Case Else
MsgBox "There was an error. (" & Err & ") " & Error
End Select
End Sub
I *think* I just figured it out. Had to use DAO.Database and DAO.Property
@@valerietopp3161 Nice!
Wow, the famous 3270 of the 80’s
lol yeah! wasn't that a terminal?
@@seanmackenziedataengineering Yes, it was back-n-da-day
Say it with me -- Access isn't meant to be a front end!
It has use cases where it works well. Great for fast prototypes or workgroup databases, but can be a pain to put into production with proper security etc. At a certain point you start looking for browser-based front-ends. I find that where Access excels in in data cleaning, wrangling, transformation, and joining many disparate data sources for analysis. For those in the know, it is much faster than using Python or other methods with desktop data. Here is a quick web front-end: ua-cam.com/video/TbAECDT7FFU/v-deo.html