M
mharfoot07-ggroups
Hello Everyone
Using Access 97.
I'm currently testing the standard installation: BE on the server and
FE - converted to mde - on the workstations.
The method of deployment to the workstations is not decided yet.
Learning Tony Toews AutoFE solution is going to be a severe test, I
barely understood one word in five whilst reading up on it!
Anyway, in the interim I've cobbled together ideas and code from
elsewhere and have a possible method. It's based on:
1.
The deployment solution from http://accesstips.datamanagementsolutions.biz/versions.htm
2.
The custom login db from http://www.access.qbuilt.com/html/custom_login.html
to check versions and replace the FE if applicable, and get the user
name (saved) and password to launch the secured FE. I don't want users
to have to login twice if a more recent version applies.
The mdw for the secured db's is on the server.
Question 1.
From my reading it seems the FE.mde can bloat so the choice is - at
given intervals - should the Login db download a fresh copy of the
latest version FE from the server, or compact the local FE before
launching it?
I don't have a problem with the first option, to replace with a fresh
copy, and you may tell me to stick with it after I ask my next
question.
Question 2.
How do I compact a secured database from an unsecured database?
I've searched everywhere but couldn't a definitive solution. That's an
open invitation isn't it - I wait for the rush of links<s>. In the end
I just bit the bullet and tried it for myself. The code below *seems*
to work but I don't want to implement it without asking the experts if
there are any gotchas in Access 97.
User opens unsecured Login db via the default mdw and it's time to
compact the secured FE on the local drive. (If you wish, see further
information below my signature.) The open event of the startup form
calls the following function:
Function CompactDatabaseOK() As Boolean
On Error GoTo Err_Handler
Dim eng As New DAO.DBEngine, wrk As DAO.Workspace, _
strWrkgrp As String, strUser As String, strPwd As String, _
strSrc As String, strBak As String
Const conStrDir As String = "LocalPathWithLastSlash"
Const conStrExt As String = ".mdb" 'mde prior to conversion to
mde and release
Const conStrDbName As String = "dbNameWithNoExt"
strWrkgrp = "SecuredWorkgroup.mdw"
strUser = "caretaker"
strPwd = "care"
strSrc = conStrDir & conStrDbName & conStrExt
strBak = conStrDir & conStrDbName & ".bak"
'have to declare the next 4 first? seems so...
eng.DefaultType = dbUseJet
eng.SystemDB = strWrkgrp
eng.DefaultUser = strUser
eng.DefaultPassword = strPwd
Set wrk = eng.CreateWorkspace("tmp", strUser, strPwd, dbUseJet)
eng.CompactDatabase strSrc, strBak
eng.Idle
wrk.Close
Set wrk = Nothing
Set eng = Nothing
Kill strSrc
DoEvents
Name strBak As conStrDir & conStrDbName & conStrExt
CompactDatabaseOK = True
Exit_here:
On Error Resume Next
wrk.Close
Set wrk = Nothing
Set eng = Nothing
Exit Function
Err_Handler:
CompactDatabaseOK = False
MsgBox Err.Number & " : " & Err.Description 'comment out when
problems sorted
Resume Exit_here
End Function
Your evaluation, and thoughts on stability, would be appreciated.
Further information below my signature if you can bear to read yet
more stuff.
Many thanks,
Marguerite
There is only one User in the secured workgroup with permission for
any db to Open/Run and Open Exclusive for compacting. They have no
other permissions and Access quits if they open a secured db.
I don't want to change the user 'caretaker' permissions because a
couple of users already know the routine, and compacting the BE
database is still the most important. They have desktop shortcuts with
the password switch for simplicity (no reason to hide it),
nevertheless, it's an established routine that needn't be altered.
They're not fanatical about compacting, they just monitor and act
accordingly. In time we might be using a more recent version of Access
and can use 'Compact on close'.
Perhaps I should also explain that I retired a couple of years ago,
but still maintain the databases, so I can't quickly test network
installations. The plan for the future is for me to have remote access
to the server so I will be able to upload the latest FE with ease.
When I have a couple of options sorted out, I'll go in and test them
for however many days it takes to correct errors, and make a decision
based on the speed of various PCs.
______
Using Access 97.
I'm currently testing the standard installation: BE on the server and
FE - converted to mde - on the workstations.
The method of deployment to the workstations is not decided yet.
Learning Tony Toews AutoFE solution is going to be a severe test, I
barely understood one word in five whilst reading up on it!
Anyway, in the interim I've cobbled together ideas and code from
elsewhere and have a possible method. It's based on:
1.
The deployment solution from http://accesstips.datamanagementsolutions.biz/versions.htm
2.
The custom login db from http://www.access.qbuilt.com/html/custom_login.html
to check versions and replace the FE if applicable, and get the user
name (saved) and password to launch the secured FE. I don't want users
to have to login twice if a more recent version applies.
The mdw for the secured db's is on the server.
Question 1.
From my reading it seems the FE.mde can bloat so the choice is - at
given intervals - should the Login db download a fresh copy of the
latest version FE from the server, or compact the local FE before
launching it?
I don't have a problem with the first option, to replace with a fresh
copy, and you may tell me to stick with it after I ask my next
question.
Question 2.
How do I compact a secured database from an unsecured database?
I've searched everywhere but couldn't a definitive solution. That's an
open invitation isn't it - I wait for the rush of links<s>. In the end
I just bit the bullet and tried it for myself. The code below *seems*
to work but I don't want to implement it without asking the experts if
there are any gotchas in Access 97.
User opens unsecured Login db via the default mdw and it's time to
compact the secured FE on the local drive. (If you wish, see further
information below my signature.) The open event of the startup form
calls the following function:
Function CompactDatabaseOK() As Boolean
On Error GoTo Err_Handler
Dim eng As New DAO.DBEngine, wrk As DAO.Workspace, _
strWrkgrp As String, strUser As String, strPwd As String, _
strSrc As String, strBak As String
Const conStrDir As String = "LocalPathWithLastSlash"
Const conStrExt As String = ".mdb" 'mde prior to conversion to
mde and release
Const conStrDbName As String = "dbNameWithNoExt"
strWrkgrp = "SecuredWorkgroup.mdw"
strUser = "caretaker"
strPwd = "care"
strSrc = conStrDir & conStrDbName & conStrExt
strBak = conStrDir & conStrDbName & ".bak"
'have to declare the next 4 first? seems so...
eng.DefaultType = dbUseJet
eng.SystemDB = strWrkgrp
eng.DefaultUser = strUser
eng.DefaultPassword = strPwd
Set wrk = eng.CreateWorkspace("tmp", strUser, strPwd, dbUseJet)
eng.CompactDatabase strSrc, strBak
eng.Idle
wrk.Close
Set wrk = Nothing
Set eng = Nothing
Kill strSrc
DoEvents
Name strBak As conStrDir & conStrDbName & conStrExt
CompactDatabaseOK = True
Exit_here:
On Error Resume Next
wrk.Close
Set wrk = Nothing
Set eng = Nothing
Exit Function
Err_Handler:
CompactDatabaseOK = False
MsgBox Err.Number & " : " & Err.Description 'comment out when
problems sorted
Resume Exit_here
End Function
Your evaluation, and thoughts on stability, would be appreciated.
Further information below my signature if you can bear to read yet
more stuff.
Many thanks,
Marguerite
There is only one User in the secured workgroup with permission for
any db to Open/Run and Open Exclusive for compacting. They have no
other permissions and Access quits if they open a secured db.
I don't want to change the user 'caretaker' permissions because a
couple of users already know the routine, and compacting the BE
database is still the most important. They have desktop shortcuts with
the password switch for simplicity (no reason to hide it),
nevertheless, it's an established routine that needn't be altered.
They're not fanatical about compacting, they just monitor and act
accordingly. In time we might be using a more recent version of Access
and can use 'Compact on close'.
Perhaps I should also explain that I retired a couple of years ago,
but still maintain the databases, so I can't quickly test network
installations. The plan for the future is for me to have remote access
to the server so I will be able to upload the latest FE with ease.
When I have a couple of options sorted out, I'll go in and test them
for however many days it takes to correct errors, and make a decision
based on the speed of various PCs.
______