Keeping Access Open

[Bre-x]

Hi,
I have this sub on ms outlook.
I saves an attachement then opens access. Why MS Access open then it closes.

Public Sub import_supplypro()
On Error Resume Next

'Declare Varibles
Dim myItems, myItem, myAttachments, myAttachment As Object
Dim myOrt As String
Dim myOlApp As New Outlook.Application
Dim myOlExp As Outlook.Explorer
Dim myOlSel As Outlook.Selection
Dim file_name As String

Responce = MsgBox("This Macro will Modify EvoERP Data." & Chr(10) & " " &
Chr(10) & "Would you like to Continue? ", vbCritical + vbYesNo, " UMCORP")
If Responce = vbNo Then
Exit Sub
End If

file_name = "SUPPLYPROCR.TXT"
myOrt = "M:\Reorder\toolcript\"

Set myOlExp = myOlApp.ActiveExplorer
Set myOlSel = myOlExp.Selection

For Each myItem In myOlSel
Set myAttachments = myItem.Attachments
If myAttachments.Count > 0 Then
For i = 1 To myAttachments.Count
myAttachments(i).SaveAsFile myOrt & file_name
Next i
myItem.Save
End If
Next

'Open MS Access
Dim myaccess As Object
Set myaccess = CreateObject("Access.Application")
myaccess.Visible = True
myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb")

'Free variables
Set myItems = Nothing
Set myItem = Nothing
Set myAttachments = Nothing
Set myAttachment = Nothing
Set myOlApp = Nothing
Set myOlExp = Nothing
Set myOlSel = Nothing
Set myaccess = Nothing
End Sub

 

[Dirk Goldgar]

Hi,
I have this sub on ms outlook.
I saves an attachement then opens access. Why MS Access open then it
closes.

Public Sub import_supplypro()
On Error Resume Next

'Declare Varibles
Dim myItems, myItem, myAttachments, myAttachment As Object
Dim myOrt As String
Dim myOlApp As New Outlook.Application
Dim myOlExp As Outlook.Explorer
Dim myOlSel As Outlook.Selection
Dim file_name As String

Responce = MsgBox("This Macro will Modify EvoERP Data." & Chr(10) & " " &
Chr(10) & "Would you like to Continue? ", vbCritical + vbYesNo, "
UMCORP")
If Responce = vbNo Then
Exit Sub
End If

file_name = "SUPPLYPROCR.TXT"
myOrt = "M:\Reorder\toolcript\"

Set myOlExp = myOlApp.ActiveExplorer
Set myOlSel = myOlExp.Selection

For Each myItem In myOlSel
Set myAttachments = myItem.Attachments
If myAttachments.Count > 0 Then
For i = 1 To myAttachments.Count
myAttachments(i).SaveAsFile myOrt & file_name
Next i
myItem.Save
End If
Next

'Open MS Access
Dim myaccess As Object
Set myaccess = CreateObject("Access.Application")
myaccess.Visible = True
myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb")

'Free variables
Set myItems = Nothing
Set myItem = Nothing
Set myAttachments = Nothing
Set myAttachment = Nothing
Set myOlApp = Nothing
Set myOlExp = Nothing
Set myOlSel = Nothing
Set myaccess = Nothing
End Sub

You need to hand off the application to user control:

myaccess.Visible = True
myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb")

myaccess.UserControl = True

 

[Bre-x]

Thanks!!!!

You need to hand off the application to user control:


myaccess.UserControl = True



--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Does that cause VBA code to stop execution at that point until Access is
closed, then execute the next statement once it is closed? I noticed he's
setting the myAccess variable to Nothing right after it's opened - didn't
know if that would cause a problem or not.

No, the VBA in the application that created the Access application object
will continue after that object is set to UserControl. But setting the
object variable to Nothing will no longer terminate the Access application,
because it has been turned over to user control.