This is driving me crazy!!! What I am trying to achieve is that when a button is pressed it checks the linked table in the front end to see whether a field exists. If it doesn't it then connects to the back end and uses an alter table sql statement to add the field to the table. The problem is that even though I am closes the form which uses the table for the row source in a combo box AND i delete the linked table it is based on, it always comes back with an error 3262, saying the table is in use by me on my machine!! Any ideas on how to get around this?! He is my code:
Function SavePerms(bytPerm As Byte, strObjName As String)
Dim strSQL As String
Dim strObj As String
Dim objName As String
Dim Exists As Integer
Dim tdfField As Field
Dim tdf As Recordset
Exists = 0
Set tdf = CurrentDb.OpenRecordset("tblGroupPermissions")
For Each tdfField In tdf.Fields
If tdfField.Name = strObjName Then
Exists = 1
Set tdf = Nothing
If Exists = 0 Then
Forms!frmGroupPermissions.cmbGroup.RowSource = ""
DoCmd.Close acForm, "frmGroupPermissions"
Static acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
strDbName = "H:\Jobs Database\Back End\Jobs Database Back End 0-1-1.mdb"
Set acc = New Access.Application
acc.Visible = True
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
strSQL = "ALTER TABLE tblGroupPermissions ADD COLUMN " & strObjName & " TEXT;"
Set db = Nothing
DoCmd.TransferDatabase acLink, "Microsoft Access", "H:\Jobs Datasbase\Back End\Jobs Database Back End 0-1-1.mdb", _
acTable, "tblGroupPermissions", "tblGroupPermissions"
Forms!frmGroupPermissions.cmbGroup.RowSource = "SELECT DISTINCTROW [tblGroupPermissions].[Group] FROM [tblGroupPermissions];"
Ok, since no one knew how to resolve the code above I'm now going to ask whether there is another way of doing it. I have my database split into front and back end,both in MS Access. Firstly I am trying to check to see whether a field exists and if it doesn't it then needs to go to the back end and add the field to the table, since I can't alter the table structure of the linked table in the front end. Surely someone must know how to do this?!
Why would you want to create a column in a table. It sounds as if your basic design is wrong, or you are trying to do something that the original designer hasn't envisaged.
if you want to do this I think you could do it by
1) open the catalog and iterate through the tables collection to find the desired table
2) then iterate through to find if the required column is defined
3) if not create the ddl and run that as an SQL query aganst the table. IE "Alter table xxxxx ......" Presumeably you would need to have exclusive rights, and it is entirely possible that if you had the back end tables opened int he same database as you are trying to run the DDL query then you could have a problem. I can' t remember if youcan use DDL in Access, I think it <should> but I've never used DDL in Access / JET, I have where using Access as a front end.
It is for the custom security system I have talked about in a previous thread. I have a form that contains a drop down box containing the user groups I have, two list boxes; one containing the type of objects in the database (tables, queries, forms.....), then when a user selects the type of object it displays all the objects of that type in the database in the second listbox. Then I also have 4 checkboxs referring to didn't user group permissions. Then when the user clicks on a apply button to save the settings, it checks in the table in which all the settings are stored to see whether a column for that particular object exists. If it doesn't I need it to create the column to store the permissions in. If I just created the permissions table with all the columns in it for each object in my database, what happens when someone wishes to apply permissions to a report, form, etc that was created after the database is deployed?
I have already tried open the back end exclusively and then using an ALTER TABLE sql statement but it says the table is locked by the front end. However if I then stop the code running in the front end and run it from the back end it works fine. Also, I've tried to excute the code when it is stored in the back end from the front end but this generates the same error.
I'd love to know why you need to add a column to the table, after all what your are setting is a permission on an entity. It sounds to me as if the table design is suspect perhaps you need 2 tables? One to hold the entity, one ot hold any / all applicable permissions.
Could you explain in a concise note say 100..200 words what you are trying to achieve (not implement) IE why do you need to add a row, what causes that event?
Ok, for various reasons I have decided to implement my own security, this is going to be extremely similiar to how the built in access secuirty is with workgroups and users only without the need for a workgroup fiile and the shortcut to the database needing the workgroup file switch. So I have now come to the form where the permissions are set on each object in my database. Based on which checkboxs are ticked a value is added up for the object per user group. Now, because I don't intend to be with this company forever, I need it to be dynamic in that if a table is created in the future it can store a value for that table by adding that column to the permissions table in the back end. So what I am basically trying to implment is a security system which stores the perimssions for each user group as a bitmask in a table in the back end, but make the system flexible enough to be able to cope with future developments. If you can think of an alternative way of achieveing this then please tell me because this is becoming quite a challenge (although a challenge I am really enjoying!!). If it would make it easier I will try and post a cut down version of the database with what I have of the security system so far?
why reinvent the access security tool
if you deploy as an MDE (ok required office developer) and use runtime then there is no way you users can see the code, alter tables etc. Even if you don't have access to make MDE's then I'd stiil use this route
the security tool handles most situations easily enough. I have yet to find an application that needs more than say 5..10 usergroups that is not going to be server based (and therefore rely on the server security model). Its open ended and well documented, there is limited risk of some breaching the security. Don't take that as a criticism - I once spent ages devloping an encryption / decryption routine, though it was pretty smart and then came across PGP - I quickly killed off my naive routine.
Ok, I could deploy it an MDE file, which I was planning to do to the front end anyway, and then password protect the back end. The problem is I would still need to create workgroup files and include the shortcut to the workgroup file, but in the office I work in, people often delete shortcuts and mess around with things on there computer, which I have no control over since I am not part of the IT services team. So I thought I would try my hand at developing my own security system which would not only allow me greater control over what happens with my database, but it would also give me an oppotunity to learn a bit more about Access, VBA, security and networkin (the front and back end will be stored on a network, not quite sure yet how that will affect the operating of database).
On a side note, I would like to thank you Healdem for all the advice you've giving/given me.
If you get your it team to deploy a desktop shortcut to the application having specified the workgroup as part of the shortcut
If its deployed in the all user profile then they get it but they can't meddle
the form is
"<path to access>" "<path to application>" /wrkgrp "<path to workgroupfile>"
"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "\\URL\users\ALLUSER\myaccessapp.mde" /wrkgrp "\\URL\users\myworkgroup.mdw
You can be more cunning, and deploy as an MDE, allow the users to do what ever the heck they like but make a security call at the start of each form, report etc requesting has this user got permission in the workgroup file. The twist is that you pick up the userid from the environ function. in a Novell environment its environ("NWUSERNAME"). If they don't have permission chuck 'em out, if you've deployed as an MDE they can't get round it viewing the source or some other kludge workaround. In short there stuffed, invalid user account - tough no entry.
assign user to the required groups,and they can then use the database without having to provide a userid or password, once they are authenticated on the network they can run. you can maintain the use accounts remotely, no problem.
You can get a similar effect running a zenwork application object (again under Novell but its messy)
Just a thought, why don't you move your original security code to the back end Access doesn't care whether a back end has only data, or a front end only forms & reports. Move it to the back end, make any chanegs you require there the front end still stalks to the back end to get the permissions. I stillthink its cockeyed to add a new object (table, formd etc as a column). In the last app I had to use the security on I stuggled to get to 8 disparate users functions, ie groups. It's all to easy to start to create a security model which is fine enough to meet all perceived requirements and easy enough to maintain. Im a frim beliver in KISS - keep it simple, stoopid, although in my case perhaps KIMM is more appropriate kepp it moronic, moron
But my original point was that I didn't want to use a workgroup file, Theey are too easy to either delete the shortcut by accident then the user would have no access to it when the found the actual mdb file (which is the first thing they would try to do), or more sinisterly, it would be too easy just to revert the shortcut back to using the default system one, howver I know its easy to write some code to check on which workgroup file is being used at the startup of the database.
Secondly, I'm not using Novell, my company runs Windows from NT to 2000 to 95 to XP. Although I don't think my database will need to run on anything less than 2000.
In short, do you know how to add the field into a table stored in the back end? The fieldname is just whatever the tablename is that they are setting permissions for. Also could you explain to me in a bit more detail why it is so bad to change table properties on the fly?
Taken from Michael J. Hernandez' book 'Database Design for Mere Mortals', section entitled Objectives on Good Design, "The database lends itself to future growth. the database structure should be easy to modify or expand as the information requirements of the business change and grow".
I don't want to get confrontational on this (or anything else for that matter)
My point is you are expendign a great deal of effort to create a security system which is driving you up the wall. Is it worth the effort when effective security already exists. There are problems with access workgroups. Yes users can attempt to open the db without the workgroup file, yes if users log on as a local workstation they will loose the connection to the workgroup, yes if you network server (or network craps out) which has the workgroup access will reset the workgroup. However if the access the application via an all user profile shortcut its all in there.
If your IT people have tied down the system security correctly (ie mere mortals [users] don't have administrator rights) then they can deploy a shortcut to the "all user" profile, which users can't delete modify or otherwise tinker with. If you application is deployed in this manner then its locked on their screen.
If you IT department has the remote deployment tool (SMS? i forget its name) then they can set the shortcut remotely, otherwise its a long walk all around the block to set each PC). I recognise not all organisations use NOVEL, so investigate the environ and you will find the microsoft equivalents (and presumably any other significant network authentication system)
I don't think adding columns on the fly is per se a bad thing. In some circumstances it may be a great feature. You seem to be trying to use the structure of the table to represent the data IE adding a column for the new object rather than adding a row for the object. maybe you could getaway with the columns being group1,group2,group3...group 254, and have a lookup somwhere which identified group1 as baseusers,group2 "superusers" etc.... You are still going to hit an intenal threshold within access in as much as you cannot have more than 255 columns per table.
What you are outlining to me seems "nasty" and possibly not "right".
I think trying to do it in this case is a waste of talent. I know I tend to waffle but did you investigate the option of putting your current code into the back end, the one that is rejecting any changes because of the lock error and allow that to be accessed by a very limited number of users?