Automate Access

[hlam]

My excel file is to close itself after access is called, however the access
file is not opened in full window size. How can I modify the code in order
to have the access window in full size?

Sub OpenDatabaseFile()

Dim accessApp As Object

Set accessApp = CreateObject("Access.Application.9")

With accessApp
.OpenCurrentDatabase ("D:\db1.mdb")
.Visible = True
End With

Set accessApp = Nothing

ThisWorkbook.Close

End Sub

 

[immanuel]

Try:

accessApp.WindowState = -4137

/i.

 

[Tom Ogilvy]

Sub OpenDatabaseFile()

Dim accessApp As Object

Set accessApp = CreateObject("Access.Application.9")

With accessApp
.OpenCurrentDatabase ("D:\db1.mdb")
.Visible = True
.DoCmd.RunCommand acCmdAppMaximize
End With

Set accessApp = Nothing

ThisWorkbook.Close

End Sub

 

[Tom Ogilvy]

I believe that is the Excel method, but it is not available in Access:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210090&Product=acc
ACC2000: How to Use Visual Basic for Applications to Minimize, Maximize, and
Restore Access

http://support.microsoft.com/default.aspx?scid=kb;en-us;148834&Product=acc
ACC: How to Minimize, Maximize, and Restore MS Access 95/97

At least not in Acc 95 - 2000

 

[immanuel]

Thanks for the correction, Tom.

In 2003, it seems that you can just call Appliation.DoCmd.Maximize. Is this
not the case previous versions?

Regards,
Immanuel

 

[Tom Ogilvy]

That maximizes the active window within the application window, not the
application window. I understood the requirement to be the application
window. That is the behavior in access 97 and 2000. I don't think it would
change in later versions.

 

[hlam]

I added the line as you recommended and now I get an error as :

Run-timer error '2501'
The run command action was cancelled.

 

[Tom Ogilvy]

Didn't notice you were using late binding. Replace the acCmdAppMaximize
constant with it numerical value:

? acCmdAppMaximize
10

Sub OpenDatabaseFile()

Dim accessApp As Object

Set accessApp = CreateObject("Access.Application.9")

With accessApp
.OpenCurrentDatabase ("D:\db1.mdb")
.Visible = True
.DoCmd.RunCommand 10
End With

Set accessApp = Nothing
ThisWorkbook.Close
End Sub

I tested this modified version of this code to keep the db open

Sub OpenDatabaseFile()

Dim accessApp As Object

Set accessApp = CreateObject("Access.Application")

With accessApp
.OpenCurrentDatabase ("C:\My Documents\db1.mdb")
.Visible = True
.DoCmd.RunCommand 10
.UserControl = True
End With

' Set accessApp = Nothing
' ThisWorkbook.Close
End Sub


worked fine for me. Access was maximized.

 

[hlam]

Thanks for your reply. The access window is now opened in full screen. Now
it's about the startup form that was set to Auto Centre and is now
positioned to the left uppen corner. How can I fix this problem?

 

[Tom Ogilvy]

Try changing the order of things

Sub OpenDatabaseFile()

Dim accessApp As Object

Set accessApp = CreateObject("Access.Application.9")

With accessApp
.Visible = True
.DoCmd.RunCommand 10
.OpenCurrentDatabase ("D:\db1.mdb")
End With

Set accessApp = Nothing
ThisWorkbook.Close
End Sub

I would assume the positioning of the open form is handled by Access, so
maximize it before it makes those decisions.

 

[hlam]

That works exactly what I expected. Thank you.

Try changing the order of things

Sub OpenDatabaseFile()

Dim accessApp As Object

Set accessApp = CreateObject("Access.Application.9")

With accessApp
.Visible = True
.DoCmd.RunCommand 10
.OpenCurrentDatabase ("D:\db1.mdb")
End With

Set accessApp = Nothing
ThisWorkbook.Close
End Sub

I would assume the positioning of the open form is handled by Access, so
maximize it before it makes those decisions.