How do I declare a database so can I get mdb startup property

[AccessGuruCarl]

Hello All,

I have a vb app that does several things before rarring an .mdb file that is copied to a web server.

I need to check the dbs.Properties("StartupForm") and make sure it was set to the frm_Register and if not, change the startup property to "frmSecure" then display the database so the operator can secure it(set 'all the' startup properties).

The database uses a secured workgroup file as well!

I can easily do this in VBA....
Tried the following but getting error messages...
1st starts at As Database... Tried replacing with Object but ran into errors later.

Whats the correct way to declare a database?

Dim dbs As Database, prp As Variant, myWorkspace As Workspace
Dim strPropName As String
Dim strUser As String
Dim strPassword As String
  MyUser = "TestUser1"
  MyPassword = "AppleJacks"
  
  DBEngine.SystemDB = "C:\TestFolder\secured.mdw"

 Set myWorkspace = DBEngine.CreateWorkspace("New", "strUser", "strPassword")
 Set dbs = myWorkspace.OpenDatabase("C:\TestFolder\Test.mdb")

    strPropName = "StartupForm"
    prp = dbs.Properties(strPropName)
    
    If prp <> "Form.frm_Register" Then
        MsgBox "Database StartUp Properties Not Set!"
    ' Add code to change db startup property
    'Load db to set startup properties.
    Else
    'Create Setup File
    
    'Rar and copy to website update folder
    
    End If
    
 Set myWorkspace = Nothing
 Set dbs = Nothing

Thanks...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[JerryKlmns]

AccessGuruCarl,

You should have a reference to the DAO library and declare the variables as
Dim dbs As DAO.Database, prp As DAO.Property, myWorkspace As DAO.Workspace

and an extra object of
Dim DBE As DAO.DBEngine

along with
Set DBE = New DAO.DBEngine

 

[AccessGuruCarl]

Hello JerryKlmns

Thanks for the post...

I had a reference to DAO already.
After playing around for awhile, I got most of the error to go away. But using an Access2k db I keep getting this error:
Error: 3343 -- Unrecognized database format 'C:\Test2k.mdb'.

I remarked out every thing above the Set dbs:
Modified to this, and created an Access97 MDB
Set dbs = OpenDatabase("C:\Test97.mdb")

Now when I run it I get an error at:
prp = dbs.Properties(strPropName)

Error: 3270 -- Property Not Found
I know this is a valid property, or at least in 2k it is!

So I have a feeling this method won't work...
For several reasons...
Any other idea's

Thanks....

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[JerryKlmns]

Who dares wins

If the property doesn't exist, then create it yourshelf!

Set prp = dbs.CreateProperty("StartupForm", 10, "StartUp", True)
dbs.Properties.Append prp

There is also the other way to open an new instance of access and play with that object.

Following code opens a user-level secured access mdb, and runs a macro which calls a function to execute code.

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Const SYNCHRONIZE = &H100000
Private Const INFINITE = -1&

Sub DoStuff()
Dim objAccess As Object
Dim myShell As string
myShell = Chr(34) & strAccessExe_Path & Chr(34) & " " & _
          Chr(34) & strMDB_Path & Chr(34) & " /wrkgrp " & _
          Chr(34) & strMDW_Path & Chr(34) & " /user " & _
          Chr(34) & strUser & Chr(34) & " /pwd " & _
          Chr(34) & strPassword & Chr(34) & " /x " & _
          Chr(34) & strMAcroName & Chr(34)
'If access is running, close it and shell
Set objAccess = GetObject(, "Access.Application") 
If Err.Number=0 Then
   objAccess.Quit
   Set objAccess = Nothing
   RunUntilFinished (myShell)
Else
   RunUntilFinished (myShell)
End If
objAccess.CloseCurrentDatabase
objAccess.Quit
Set objAccess = Nothing

End Sub

Public Sub RunUntilFinished(ByVal sApp As String)
'This is from Tek-Tips.com 
    Dim lProcID As Long, hProc As Long

    lProcID = Shell(sApp)
    On Error GoTo 0

    DoEvents
    hProc = OpenProcess(SYNCHRONIZE, 0, lProcID)
    If hProc <> 0 Then
        WaitForSingleObject hProc, INFINITE
        CloseHandle hProc
    End If
End Sub

Your function should do what ever you need...

 

[AccessGuruCarl]

The append feature does me no good... It's Access97 format.
The actual mdb is in Access2k format.

As for the other code:
I use something similiar in the exe file that launches the mdb. But it gets the Access path from the registry classid where I can also test for version.

Reasoning behind what I'm attempting...
This exe is simply a utility that copies and zips files, then runs an installation that packs it into a setup.exe file for distribution.

About every 3 months they run this to create a new setup file, and a rarred mdb file for download updates.

Here is where my issue arises....
To do an update they need to reset the startup property, which then exits the db, when they open it again, it's set to the form to run various updates instead of the Default Registration Form(Off-Site Mode).

After updating, If they don't reset the startup properties for some unknown reason.... Oh, I forgot! It cause's major issues when a new user installs the setup file. Or even worst, someone runs an update and now can't access the correct forms because the db as to modes... On-Site and Off-Site, and if it's still set to On-Site and linked tables aren't found, It exits the database.

So I thought if I could check that before doing anything else...then problem was solved.

Didn't realize this was going to be so hard! You'd think that in VB6 they at least give you an option for setting the default Jet. Instead of resorting to 3.51 Office 2000 must have been released around the same time.

Still hoping for a way to tackle this!!!!
Any Idea's greatly appreciated...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[SBerthold]

>You'd think that in VB6 they at least give you an option for setting the default Jet

Oh, first it's not VB but DAO, and second, there is.

Under Project-References, change from using DAO 3.51 to using DAO 3.6

And/Or, if a DataContol is used, change the Connection property from "Access" to "Access 2000"

DAO 3.6 is JET 4/Access 2000+ compatible (and JET 3+/Access 97 compatible)
DAO 3.51 is not JET 4, because it came with JET 3.

Also, make sure you are using VB6 service pack 6 (because of the Data Control Connection property, including many other reasons.)

 

[SBerthold]

If you would put your cursor on db.Property (db being a dao.database object), and press F1 (if VB Help is installed), you would see how to use it and two possible examples under the example button.

The problem connecting to a JET 4 database with either an earlier version of VB, an older OS, or the wrong references or properties set, has been discussed many many times here, and basically what I have stated in my previous post.

 

[JerryKlmns]

Versions of DAO is a pain. Worst is for ADO (2.1, 2.5, 2.6, 2.7, 2.8 MDAC Road Map )

I'd say SBerthold covered me there.

But a function couldn't run the various updates without any user interference? Or send them a version where the startup form is the "run the various updates", wait for them to press an Ok button to run code and when exiting change the start up form and close?

 

[AccessGuruCarl]

SBerthold

Thanks, Forgot all about changing the reference.

But the problem is trying to connect to a secured workgroup. It just won't let me open it, no matter what I've done!
Have you been able to do this? I've tried hard coding the connection, Data Control, ADO Control but nothing will allow me to open the db. I guess the workgroup file is actually doing what it's suppose to, prevent users from gaining access to viewing or changing objects.

I also received another email from you, but It appears they must have removed the post? I get a page not found error.

I thought another post with a better title description would help. But it appears the admins have removed it.
Could you repost your reply from their to here!

Not sure what SP is installed for VB, if any! When I open the about dialog, All it states is Microsoft Visual Basics 6.0 for 32-bit deployment.

I'll try searching MS for some updates!


Thanks...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[strongm]

>All it states is Microsoft Visual Basics 6.0 for 32-bit deployment

In which case you don't have service packs at all! Go get SP6 right away:

http://www.microsoft.com/downloads/...70-DFE1-42A1-A4C8-39718C7E381D&displaylang=en

 

[SBerthold]

>But the problem is trying to connect to a secured workgroup

I do not remember the exact syntax, but you need add the user name and password to the OpenDatabase:

.OpenDatabase("C:\TestFolder\Test.mdb",False,False,";Username=Admin; pwd=ThePassword")

I think you also need the secure mdw that was used on the original mdb to where the passwords and usernames got set.


>Could you repost your reply from their to here!

Yes. Something like:

"Why have you started another thread on this?"

Then I requested for it to be removed.


Thanks strongm.

I think pretty much has been said as what I feel is needed here, at least for now.

 

[SBerthold]

I guess it's not
Username=Admin
but
Uid=Admin.

>Then I requested for it to be removed.
Actually I felt JerryKlmns (and I) were being [nicely said] dumped upon, and I refused to let that happen.

 

[JerryKlmns]

Actually I felt JerryKlmns (and I) were being [nicely said] dumped upon, and I refused to let that happen.

Oh! I felt something like that, but I saw your post and left it there, since mine would be exactly the same. I didn't expect that from a 3year old member, but sometimes you end up with a strange feeling when you do need despirately a solution but you cant get it to work, although you do get tips, and you react not in your normal way.

Back to the problem

AccessGuruCarl

The reason I used the myShell string is that I couldn't find how to open that user-level secured access as a new instance. So that, makes us two!

Pls don't be offended by my previous comment.

 

[AccessGuruCarl]

Hey Guys,

Thanks for the post... Sorry if I offended anyone.

But, Like I mentioned in an earlier post, It was only to give a better 'title' description, and that was why I linked it back to this post.

Jerry, didn't quite understand the last post.

The reason I used the myShell string is that I couldn't find how to open that user-level secured access as a new instance. So that, makes us two!

Were you able to open a secured db with the myShell string?

Again thanks for all the help with this, It is very frustrating when you think you have an easy solution and it turns out to be more trouble than you imagined.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[AccessGuruCarl]

I modified the code to use the connection string.

Similair to what access creates in the shortcut it places on your desktop when you create a secured db.

But now I have a new error. Error # 3055 - Not a valid filename.

Here is the revised code using the connection string. I tried it both with the access.exe in front and without it.
Both produced the same error.

Dim dbs As DAO.Database
Dim prp As DAO.Property
Dim strPropName As String
Dim strUser As String
Dim strPassword As String
Dim x As String

Dim db As String, workgrp As String
Dim user As String, password As String
' Use the path and name of a secured MDB on your system
    db = "C:\TestSecured.mdb"
' This is the default workgroup and login details
    workgrp = "C:\Secured.mdw"
    user = "FullData"      ' Use a valid username
    password = "FullData"     ' and correct password
' Connection String w/o program executable... 
x = Chr(34) & db & Chr(34) & " /nostartup /user " & Chr(34) & user & Chr(34) & _
    " /pwd " & Chr(34) & password & Chr(34) & " /wrkgrp " & Chr(34) & workgrp & Chr(34)

Debug.Print x

 Set dbs = OpenDatabase(x)

    strPropName = "StartupForm"
    prp = dbs.Properties(strPropName)
    
    If prp <> "Form.frm_Register" Then
        MsgBox "Database StartUp Properties Not Set!"
    ' Add code to change db startup property
    'Load db to set startup properties.
    Else
    'Create Setup File
    
    'Rar and copy to website update folder
    
    End If
    
 dbs.Close
 Set dbs = Nothing

Any other idea's would still be greatly appreciated!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[JerryKlmns]

AccessGuruCarl

1st question : Oh yes!

My attempt was to use the OpenCurrentDatabase method to open an existing Microsoft Access database (.mdb) as the current database.

objAccess.OpenCurrentDatabase

but couldn't define the wourkgroup part to open it! So I switched to myShell approach and runs happily ever after.

2nd question
Error # 3055. Have you checked your x variable? Does it open the database if you replace that in a shortcut for an access?

And how about my 28 Mar 07 7:34 post's suggestion?

 

[AccessGuruCarl]

Thanks all for the help...

Problem solved...

Here is my working code for reference.

Module Code...

Public Function OpenSecureDB(ByVal strDatabasePath As String, ByVal strMDW_Path As String, ByVal strUserName As String, ByVal strPassword As String) As DAO.Database
'Supplied By Ed2020 @ TekTips
'============================================================================
'Useage: Place this code in form event
'Requires a Reference to DAO 3.6
'Dim db As DAO.Database
'Set db = OpenSecureDB("C:\TestSecured.mdb", "C:\Secured.mdw", "MyLogin", "MyPassword")
'============================================================================
Dim MyDBE As DAO.PrivDBEngine
Dim wrkJet As DAO.Workspace

    Set MyDBE = New PrivDBEngine
        MyDBE.SystemDB = strMDW_Path
        MyDBE.DefaultUser = strUserName
        MyDBE.DefaultPassword = strPassword
    Set wrkJet = MyDBE.Workspaces(0)
    Set OpenSecureDB = wrkJet.OpenDatabase(strDatabasePath)
    
End Function


Public Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim db As DAO.Database
Dim prp As DAO.Property

    Set db = OpenSecureDB("C:\TestSecured.mdb", "C:\Secured.mdw", "Carl", "letmein")
    
    Const conPropNotFoundError = 3270

    On Error GoTo Change_Err
    db.Properties(strPropName) = varPropValue
    ChangeProperty = True

Change_Bye:
    Exit Function

Change_Err:
    If Err = conPropNotFoundError Then  ' Property not found.
        Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
        db.Properties.Append prp
        Resume Next
    Else
        ' Unknown error.
        ChangeProperty = False
        Resume Change_Bye
    End If
End Function

Form Code...

Dim db As DAO.Database
Dim strPropName As String
Dim strStartUp As String

    Set db = OpenSecureDB("C:\TestSecured.mdb", "C:\Secured.mdw", "Carl", "letmein")

    With db.Properties
      strPropName = "StartupForm"
      strStartUp = db.Properties(strPropName)
    End With
    
    If strStartUp <> "Form.frm_Register" Then
    MsgBox "Database StartUp Properties Not Set!"
    'Change db startup properties
    With db.Properties
        ChangeProperty "StartupForm", dbText, "frm_Register"
        ChangeProperty "StartupShowDBWindow", dbBoolean, False
        ChangeProperty "StartupShowStatusBar", dbBoolean, False
        ChangeProperty "StartupMenuBar", dbBoolean, False
        ChangeProperty "StartupShortcutMenuBar", dbBoolean, False
        ChangeProperty "AllowFullMenus", dbBoolean, False
        ChangeProperty "AllowShortcutMenus", dbBoolean, False
        ChangeProperty "AllowBuiltinToolbars", dbBoolean, False
        ChangeProperty "AllowToolbarChanges", dbBoolean, False
        ChangeProperty "AllowBreakIntoCode", dbBoolean, False
        ChangeProperty "AllowSpecialKeys", dbBoolean, False
        ChangeProperty "AllowBypassKey", dbBoolean, False
    End With
    MsgBox "Startup Properties Set!", vbInformation, "Database Locked"
    Else
    'Create Setup File
    'Rar and copy to website update folder
    End If
    
db.Close
Set db = Nothing

I modified code slightly in Public Function OpenSecureDB by removing the strings being passed, and hard coded them into the function for my end project!

Thanks again for all the help on this!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.