Need code to update tables on server database

[Jozef]

Hello,

I'm trying to check for and add a field to a table to a back end database
through code. The problem I've been faced with is changing permissions,
because I have to use administer permissions to change the design, then
remove the delete permissions when the design has been changed.

I have to do it in code because there are several sites that need an
automated update (as opposed to manually adding the field). The platform is
Access XP. The database has a simple password on it and is not using any
particular work group, just the standard System.mdw that is installed with
Access.

I have cut and pasted the code I've written (using DAO 3.6) but I'm
receiving an error that no one has been able to help me out with (Error
3358: Can't open the Microsoft Jet engine workgroup information file.), so
that's the reason why I'm looking for an alternative.

Time is running out on me, so any help or new direction would be greatly
appreciated.

Thanks!
(Here is the code that I was trying to use....)

Private Sub ChangeDB(strDatabase as string)
On Error GoTo ChangeDB_Err
Dim db As DAO.Database
Dim docloop As Document
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim yFound As Boolean

Set db = OpenDatabase(strDatabase), False, False, ";PWD=" & conDbPwd)

With db.Containers!Tables
For Each docloop In .Documents

If docloop.Name = "UserDefaults" Then

docloop.Permissions = 1048319

Set tdf = db.TableDefs("UserDefaults")
For Each fld In tdf.Fields
If fld.Name = "DefaultFolder" Then yFound = True
Next fld

If Not yFound Then
Set fld = tdf.CreateField("DefaultFolder",
dbInteger)
tdf.Fields.Append fld
End If

docloop.Permissions = 196213
End If
Next docloop
End With
Set db = Nothing
ChangeDb_Exit:
cmdFinish.Enabled = True
Exit Sub

ChangeDb_Err:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "ERROR"
Resume ChangeDb_Exit

End Sub

[Salad]

Jozef wrote:

Hello,

I'm trying to check for and add a field to a table to a back end database
through code. The problem I've been faced with is changing permissions,
because I have to use administer permissions to change the design, then
remove the delete permissions when the design has been changed.

I have to do it in code because there are several sites that need an
automated update (as opposed to manually adding the field). The platform is
Access XP. The database has a simple password on it and is not using any
particular work group, just the standard System.mdw that is installed with
Access.

I have cut and pasted the code I've written (using DAO 3.6) but I'm
receiving an error that no one has been able to help me out with (Error
3358: Can't open the Microsoft Jet engine workgroup information file.), so
that's the reason why I'm looking for an alternative.

Time is running out on me, so any help or new direction would be greatly
appreciated.

Thanks!
(Here is the code that I was trying to use....)

Private Sub ChangeDB(strDatabase as string)
On Error GoTo ChangeDB_Err
Dim db As DAO.Database
Dim docloop As Document
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim yFound As Boolean

Set db = OpenDatabase(strDatabase), False, False, ";PWD=" & conDbPwd)

With db.Containers!Tables
For Each docloop In .Documents

If docloop.Name = "UserDefaults" Then

docloop.Permissions = 1048319

Set tdf = db.TableDefs("UserDefaults")
For Each fld In tdf.Fields
If fld.Name = "DefaultFolder" Then yFound = True
Next fld

If Not yFound Then
Set fld = tdf.CreateField("DefaultFolder",
dbInteger)
tdf.Fields.Append fld
End If

docloop.Permissions = 196213
End If
Next docloop
End With
Set db = Nothing
ChangeDb_Exit:
cmdFinish.Enabled = True
Exit Sub

ChangeDb_Err:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "ERROR"
Resume ChangeDb_Exit

End Sub

You might want to add
DBEngine.SystemDB = "system.mdw"
at the top of the sub. It might or might not make a bit of difference.

I did this
? dbSecFullAccess
1048575
but I'm using A97. What is 1048319? Maybe give full access, see if
that makes a diff.

Also, why don't you store the value of the Permissions to a variable and
reset it to that when you leave after updating? What is
.Permissions = 196213

Why not
x = .Permissions
'process
.Permissions = x
It seems to make more sense than whatever you are attempting to do with
a 196213.

I think you'd be better off as a programmer in you used intrinsic
constants names in your code. Less confusing. The code you use is like
"Hey, Jozef! Gimme a 192482" You wouldn't know what a 192482 is from a
hole in the ground.

[david epsom dot com dot au]

Are you running this in Access VBA or as a VB utility?

Put line numbers on the lines so that your error message
can report the line where the error occurs:

Err.Description & vbcrlf & "at ChangeDB." & erl

Use a complete declaration for the openobject method:

application.dbengine.opendatabase

Do you have permission to change the table permission?

use privDBEngine or createObject("dao.dbengine") to
open a different login.

I just use a different login with design permission
on the tables. You can add the owner to the current
workgroup, make the changes, then delete the owner.
Not very secure, but then if you wanted good security
you probably wouldn't use Access.

(david)
"Jozef" <[e-mail]> wrote in message
news:aBSdf.106208$S4.102109@edtnps84...

Hello,

I'm trying to check for and add a field to a table to a back end database
through code. The problem I've been faced with is changing permissions,
because I have to use administer permissions to change the design, then
remove the delete permissions when the design has been changed.

I have to do it in code because there are several sites that need an
automated update (as opposed to manually adding the field). The platform
is Access XP. The database has a simple password on it and is not using
any particular work group, just the standard System.mdw that is installed
with Access.

I have cut and pasted the code I've written (using DAO 3.6) but I'm
receiving an error that no one has been able to help me out with (Error
3358: Can't open the Microsoft Jet engine workgroup information file.), so
that's the reason why I'm looking for an alternative.

Time is running out on me, so any help or new direction would be greatly
appreciated.

Thanks!
(Here is the code that I was trying to use....)

Private Sub ChangeDB(strDatabase as string)
On Error GoTo ChangeDB_Err
Dim db As DAO.Database
Dim docloop As Document
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim yFound As Boolean

Set db = OpenDatabase(strDatabase), False, False, ";PWD=" & conDbPwd)

With db.Containers!Tables
For Each docloop In .Documents

If docloop.Name = "UserDefaults" Then

docloop.Permissions = 1048319

Set tdf = db.TableDefs("UserDefaults")
For Each fld In tdf.Fields
If fld.Name = "DefaultFolder" Then yFound = True
Next fld

If Not yFound Then
Set fld = tdf.CreateField("DefaultFolder",
dbInteger)
tdf.Fields.Append fld
End If

docloop.Permissions = 196213
End If
Next docloop
End With
Set db = Nothing
ChangeDb_Exit:
cmdFinish.Enabled = True
Exit Sub

ChangeDb_Err:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "ERROR"
Resume ChangeDb_Exit

End Sub

[Jozef]

Thanks guys, the DBEngine.SystemDB = "system.mdw" line worked.

The permission 196213 removes delete capability. The reason why I called
this one out specifically is that there are some remote tables that are not
currently set to this permission set. Outside of that, the code I pasted
was the "It's not working so I'm tweaking the hell out of it" version of the
code.

Thanks!
"Jozef" <[e-mail]> wrote in message
news:aBSdf.106208$S4.102109@edtnps84...

Hello,

I'm trying to check for and add a field to a table to a back end database
through code. The problem I've been faced with is changing permissions,
because I have to use administer permissions to change the design, then
remove the delete permissions when the design has been changed.

I have to do it in code because there are several sites that need an
automated update (as opposed to manually adding the field). The platform
is Access XP. The database has a simple password on it and is not using
any particular work group, just the standard System.mdw that is installed
with Access.

I have cut and pasted the code I've written (using DAO 3.6) but I'm
receiving an error that no one has been able to help me out with (Error
3358: Can't open the Microsoft Jet engine workgroup information file.), so
that's the reason why I'm looking for an alternative.

Time is running out on me, so any help or new direction would be greatly
appreciated.

Thanks!
(Here is the code that I was trying to use....)

Private Sub ChangeDB(strDatabase as string)
On Error GoTo ChangeDB_Err
Dim db As DAO.Database
Dim docloop As Document
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim yFound As Boolean

Set db = OpenDatabase(strDatabase), False, False, ";PWD=" & conDbPwd)

With db.Containers!Tables
For Each docloop In .Documents

If docloop.Name = "UserDefaults" Then

docloop.Permissions = 1048319

Set tdf = db.TableDefs("UserDefaults")
For Each fld In tdf.Fields
If fld.Name = "DefaultFolder" Then yFound = True
Next fld

If Not yFound Then
Set fld = tdf.CreateField("DefaultFolder",
dbInteger)
tdf.Fields.Append fld
End If

docloop.Permissions = 196213
End If
Next docloop
End With
Set db = Nothing
ChangeDb_Exit:
cmdFinish.Enabled = True
Exit Sub

ChangeDb_Err:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "ERROR"
Resume ChangeDb_Exit

End Sub

[Salad]

Jozef wrote:

Thanks guys, the DBEngine.SystemDB = "system.mdw" line worked.

The permission 196213 removes delete capability. The reason why I called
this one out specifically is that there are some remote tables that are not
currently set to this permission set. Outside of that, the code I pasted
was the "It's not working so I'm tweaking the hell out of it" version of the
code.

Thanks!

Glad that worked! It was an interesting problem and the solution ended
up being something minor. And I learned something from your code,
haven't worked programmatically with security.

"Jozef" <[e-mail]> wrote in message
news:aBSdf.106208$S4.102109@edtnps84...

Hello,

I'm trying to check for and add a field to a table to a back end database
through code. The problem I've been faced with is changing permissions,
because I have to use administer permissions to change the design, then
remove the delete permissions when the design has been changed.

I have to do it in code because there are several sites that need an
automated update (as opposed to manually adding the field). The platform
is Access XP. The database has a simple password on it and is not using
any particular work group, just the standard System.mdw that is installed
with Access.

I have cut and pasted the code I've written (using DAO 3.6) but I'm
receiving an error that no one has been able to help me out with (Error
3358: Can't open the Microsoft Jet engine workgroup information file.), so
that's the reason why I'm looking for an alternative.

Time is running out on me, so any help or new direction would be greatly
appreciated.

Thanks!
(Here is the code that I was trying to use....)

Private Sub ChangeDB(strDatabase as string)
On Error GoTo ChangeDB_Err
Dim db As DAO.Database
Dim docloop As Document
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim yFound As Boolean

Set db = OpenDatabase(strDatabase), False, False, ";PWD=" & conDbPwd)

With db.Containers!Tables
For Each docloop In .Documents

If docloop.Name = "UserDefaults" Then

docloop.Permissions = 1048319

Set tdf = db.TableDefs("UserDefaults")
For Each fld In tdf.Fields
If fld.Name = "DefaultFolder" Then yFound = True
Next fld

If Not yFound Then
Set fld = tdf.CreateField("DefaultFolder",
dbInteger)
tdf.Fields.Append fld
End If

docloop.Permissions = 196213
End If
Next docloop
End With
Set db = Nothing
ChangeDb_Exit:
cmdFinish.Enabled = True
Exit Sub

ChangeDb_Err:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "ERROR"
Resume ChangeDb_Exit

End Sub