Open a new mdb from code

cleaned up a little:


Dim ShellRtn As Double

ShellRtn = Shell(Chr(34) & "msaccess.exe" & Chr(34) & " " & Chr(34) & PathNameofDatabaseToOpen & Chr(34), vbMaximizedFocus)

AppActivate ShellRtn

Note: Dos cannot accept apostrophes instead of Quotes so the Chr(34) is used.

Thanks Thenelson
The problem with the solution is that I don't want to require the user to enter the password a second time.  I would know the user name (currentuser) but would not know the password.

Thanks
LJG

> I can do what I want with send keys <

How would you pass the password with send keys?

Rereading your question:
>I normally design databases that often have temp tables in the frontend mdb to put data in before I run a report. <

Why not use a query instead of a temp table?

thenelson
Again, thanks for your comment.  In your situation you have one copy of Access open, then open up a second copy of Access.

In my situation, I have one copy of Access open with a mdb.  I then close the mdb and open up another mdb in the same copy using the send keys.  If you want to see how it works, the following will give you an example.

Create a new mdb
Create one form with one button

Put the following in the code behind the button.  Change the path and db name to one on your machine.

SendKeys "^o" & " H:\GreatApp\MyApp.mdb" & "~", False

When you click the button, the new database will close, and your db will open.

Hope this helps
LJG

And when you open the second db from the open file dialog, the second db doesn't ask for the password?

"And when you open the second db from the open file dialog, the second db doesn't ask for the password?"

Correct!

So the first db (with the sendkeys) was opened with a password? Or is your second db capturing the password from Windows? If the later, it will work the same with the shell command.  You can take a look at http://www.thenelson.name, Updated Front End.  I use this to automatically update the FE. It works fine when the second db is secured with ULS.

Thenelson

I'm using workgroup security - So they need a name and password to login.  We don't use the Windows password - Just Access's workgroup.

LPurvis
Thanks for the information.  The problem is taht all 3 of your solutions require the user to give the password OR to know the password and put it in code.  There is no problem with knowing the user's name (currentuser) but the password is something I don't want to have the user to have to put in a second time.

thanks for the work
LJG

Thanks all for your effort - The only way I found to do this is with send keys - see attached code.

Note: I WOULD NEVER RECOMMEND DOING THIS!  The only reason I am doing this is because the company wanted to lock down the C drive and I need to us temporary tables in the inside the database and also output Excel spread sheets.  This will allow everyone to have their own copy on the network.

So if you read my first post and read the above and still want to know how to do it - keep reading.  If you find a better way to do it, please post it to this question and I would love to get rid of the send keys.

1) We are using workgroup security - it doesn't work unless you are using workgroup security
2) I put a copy of the frontend db in the same folder as the below database
3) I have a database called OpenDB.mdb with only two objects a macro and one function
       *  With an AutoExec macro that call the below code --> Auto_Exec()
       *  If you go into the code --> Auto_Exec() and change the below line to your front end db (note must be in the same folder as OpenDB.mdb)- it should work for you.

Hope this helps someone
                     Dim strDatabaseName As String: strDatabaseName = "MYApp.mdb"

Public Function Auto_Exec()
 
'----
' Purpose:  Start UnityTPM_App.mdb in the user's Folder
 
' Example:  Auto_Exec()
 
'----
'Hide Database Window
    DoCmd.RunCommand acCmdWindowHide
 
'Turn System Toolbars off
    Application.SetOption "Built-In Toolbars Available", False
 
 
'Turn off Menu
        Application.MenuBar = "NoMenu"
    
'Wait for system to catch up
    Dim i As Integer
    For i = 1 To 10
        DoEvents
    Next i
'----
 
Dim strMain_Path As String
Dim strTemp As String
Dim strNew_Path As String
Dim strDatabaseName As String: strDatabaseName = "MYApp.mdb"
 
Dim strMain_Path_DB As String
Dim strNew_Path_DB As String
 
'----
On Error GoTo HandleError
'If Programming_Mode Then On Error GoTo 0
'--------
 
 
'Get the Current Path
    strMain_Path = CurrentDb.Name
    strTemp = Dir(strMain_Path)
    strMain_Path = Left(strMain_Path, Len(strMain_Path) - Len(strTemp))
    
'Path and database that we copy if we need to
    strMain_Path_DB = strMain_Path & strDatabaseName
        
        
'Add current User to the path we start from
    strNew_Path = strMain_Path & CurrentUser & "\"
    strNew_Path_DB = strNew_Path & strDatabaseName
   
'If the file doesn't exist copy it
    On Error Resume Next
    If FileLen(strNew_Path_DB) = 0 Then
        MkDir strNew_Path
        FileCopy strMain_Path_DB, strNew_Path_DB
    End If
 
'Open the file - this will automatically close this database
    SendKeys "^o" & strNew_Path_DB & "~", False
'--------
 
ProcedureDone:
    Exit Function
 
HandleError:
    Select Case Err.Number
        Case Else
            MsgBox Err.Description & vbCrLf & "in: mod_Local.Auto_Exec" & vbCrLf & CurrentDb.Name
    End Select
    Resume ProcedureDone
    
End Function
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:

Thanks for the help, but I decided to stay with my Send Key solution - See below.
LJG