Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Open Access 2003 Database Mazimized from Excel

Posted on 2011-10-05
12
Medium Priority
?
337 Views
Last Modified: 2012-05-12
I have a macro in Excel 2003 which saves an Excel spreadsheet to a desktop and then opens an Access 2003 database.  Here is my code...

Sub Save()
' Save  Queries as Excel Spreadsheets
' Keyboard Shortcut: Ctrl+m
  Application.WindowState = xlMaximized
    Dim strFile As String
    Dim strFindID As String
    Dim stLinkCriteria As String
    Dim Answer As String
    Dim MyNote As String
    'Place your text here
    MyNote = "Save File Automatically?"
    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")
        'Code for Yes button Press
        If Answer = vbNo Then
'        MsgBox "You indicated this is RBG's Report.  Save File as RBGP or RBGT!"
            strFindID = InputBox("Enter Names")
                strFile = Environ("USERPROFILE") & "\Desktop"
                    stLinkCriteria = strFile & "\" & strFindID
            Else
       'Code for No button Press
        strFindID = Environ("UserName")
            strFile = Environ("USERPROFILE") & "\Desktop"
                stLinkCriteria = strFile & "\" & strFindID
            End If
    ActiveWorkbook.SaveAs Filename:= _
        stLinkCriteria, FileFormat:=xlNormal, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                CreateBackup:=False
    ActiveWorkbook.Close False
            Application.Quit
' Open Access Database
Set MyAccess = CreateObject("Access.Application")
    MyAccess.Visible = True
        MyAccess.OpenCurrentDatabase ("W:\DBFILES\Reports\Reports.mdb")
End Sub

The only thing I can't do is force the Access database to open MAXIMIZED.  I know all about the "DoCmd.Maximize" which I can put in the Access form and this all works fine.  However, when I open the database from Excel it doesn't work so I feel I need to put something here to tell it to open maximizied.

Can anyone help me!

Thanks!
0
Comment
Question by:Senniger1
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36919409

try this instead of  
'Set MyAccess = CreateObject("Access.Application")
'    MyAccess.Visible = True
'        MyAccess.OpenCurrentDatabase ("W:\DBFILES\Reports\Reports.mdb")


Dim dbPath As String

dbPath = """" & SysCmd(acSysCmdAccessDir) & "\MSAccess.exe""  ""W:\DBFILES\Reports\Reports.mdb"""
Shell dbPath, vbMaximizedFocus



 
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 36919662
You can add a DoCmd.Maximize in an AutoExce macro in the Access database.  Then, when opened by the Excel macro, Access will force the window max.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36923908
Can you not use:
MyAccess.DoCmd.Maximize
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Senniger1
ID: 36930469
capricorn1:  
Sorry for the delay.  When I try your solution, the "SysCmd" is highlighted and I'm getting a message "Compile error: Sub or Function not defined".

Any thoughts?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36930572

try this


   dbPath = Chr(34) & SysCmd(acSysCmdAccessDir) & "MSAccess.exe" & Chr(34) & " " & Chr(34) & "W:\DBFILES\Reports\Reports.mdb" & Chr(34)

Shell dbPath, vbMaximizedFocus
0
 

Author Comment

by:Senniger1
ID: 36930628
Same error.  Sub or Function not defined on "SysCmd".
0
 

Author Comment

by:Senniger1
ID: 36930654
Okay I did a little research and then dropped the SysCmd.  So I changed it to the following and it worked.

dbPath = Chr(34) & (acSysCmdAccessDir) & "MSAccess.exe" & Chr(34) & " " & Chr(34) & "W:\DBFILES\CPIReports\CPIReports.mdb" & Chr(34)
Shell dbPath, vbMaximizedFocus

Think this would be okay since it's working?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36930729
try doing a compact and repair
from VBA window
tools > references
  * look if there are references prefixed with "MISSING"
    - uncheck and locate available version of the reference and select

do a DEBUG > Compile
   correct any errors raised
0
 

Author Comment

by:Senniger1
ID: 36930763
I did all that, but there were no issues.  It works when I removed the SysCmd.

Can't I just use your solution without the SysCmd?

Thanks!
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36930824

try this


dbPath = "MSAccess.exe" & " " & Chr(34) & "W:\DBFILES\CPIReports\CPIReports.mdb" & Chr(34)
Shell dbPath, vbMaximizedFocus


0
 

Author Closing Comment

by:Senniger1
ID: 36931184
Works like a charm.  Thank you so much for everything.

I'm giving you all the points since I tried the other solutions offered and they didn't work for this particular instance.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36931259
sorry, just realized your codes are in excel, and i was insisting the codes that are used in access
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question