Getting Error with DBEngine.Workspace(0) ! 1

[avenuw]

Hello All,

The code below is producing errors, specifically at the line
Set db= DBEngine.OpenDatabase(dbName)

I keep getting the following error: "You donot have necessary permissions to access \\xsystems\systems\series\yha\...Please contact security adminsistrator to get proper permissions"

I do have the required permissions and I am able to access the path above and open the database, when I click on the db.

Here is the code:

[COLOR=navy]
Sub OpenDB(dbName As String,FileMDW as string,psd as String)
   Dim wks As DAO.Workspace
   Dim db As DAO.Database
   Dim dbApp As Access.Application
    
   'This finds the dir of ms access.
   strCmd = SysCmd(acSysCmdAccessDir) & "\MSAccess.exe " _
      & dbName & " /wrkgrp " & FileMDW _
      & " /user test1  /pwd " & psd
   
   Call Shell(strCmd, vbNormalFocus)
    
    MsgBox "just ran shell"
    Set wks = DBEngine.Workspaces(0)
    MsgBox "workspace created"
    wks.BeginTrans
   
    MsgBox "trans began and about to open db"

        'line below is where error occurs
        Set db = DBEngine.OpenDatabase(dbName)
        
        MsgBox "db opened successfully"
[/color navy]

I should mention that the shell command runs with no problems, so I do have proper permissions but not sure why i am getting this error.

Thanks
AV

 

[PaulBricker]

Are you sure you have the DAO reference library set?


Paul

 

[avenuw]

Paul,

I checked and I do have MS DAO 3.6 Object Library.

I did try to access the dbName (the db.mdb) directly and received a similar message saying
" You don't have permission to run 'Main Menu'. To run this object, you must have Open/Run permission for it."

Although the db opens, it doesn't allow me to run queries/reports/forms,etc.

It looks like when I click on .mdb file directly I get this error message but when I open using a shortcut that includes the mdw file, I am able to open and run objects with no problem.
So I changed the code but it still not working...What do you think?

[COLOR=navy]
Sub OpenDB(dbName As String,FileMDW as string,psd as String)
   Dim wks As DAO.Workspace
   Dim db As DAO.Database
   Dim dbApp As Access.Application
    
   'This finds the dir of ms access.
   strCmd = SysCmd(acSysCmdAccessDir) & "\MSAccess.exe " _
      & dbName & " /wrkgrp " & FileMDW _
      & " /user test1  /pwd " & psd
   
   Call Shell(strCmd, vbNormalFocus)
    
    MsgBox "just ran shell"
    
DBEngine.SystemDB = dbSecure
    Set myWks = DBEngine.CreateWorkspace("MyWKS", "test1", psd)
    Set wks = DBEngine.Workspaces("MyWKS")

    MsgBox "workspace created"
    wks.BeginTrans
   
    MsgBox "trans began and about to open db"

        'line below is where error occurs
        Set db = DBEngine.OpenDatabase(dbName)
        
        MsgBox "db opened successfully"
[/color navy]

Except now I get error invalid username or password. what am i doing wrong?

Thanks,
AV

 

[PaulBricker]

Well, let's take the string apart. First
SysCmd(acSysCmdAccessDir) returns something like this

C:\Program Files\Microsoft Office\OFFICE11\

Then you add "\MSAccess.exe "
which produces

C:\Program Files\Microsoft Office\OFFICE11\\MSAccess.exe

Second when you debug.print the strCmd variable after it's loaded it looks something like this

C:\Program Files\Microsoft Office\OFFICE11\\MSAccess.exe testDb2003 /wrkgrp myFile' /user test1 /pwd hello

Unfortunately I don't work with Workgroups, so I'm not familiar with the whole switch thing but that string doesn't look right to me.

Paul

 

[Remou]

This may help:

http://support.microsoft.com/kb/163002