Please help! I have an Outlook module opening a connection to Access, writing in some data, and then pulling out some data. But for some reason the code only works the first time and after that you have to close outlook and reopen it for it to work. After you run the code you can not even open the Access database until you close Outlook. I suspected it was a problem with the connection not being closed, or closed correctily, but I have tried every thing I can find on how to clean up at the end with no luck. Here is the code.
The code in my error handler also fires every run if I have it active, but with no error description or anaything. that is not my big concern though.
-
Sub GetTickets()
-
'On Error GoTo ErrHand:
-
-
Dim StrTmp As String
-
Dim Filez() As String
-
Dim EmlAdd() As String
-
Dim TckNum() As String
-
Dim TicDir As String
-
Dim NumFiles As Long
-
Dim i As Long
-
Const acAppendData = 2
-
Dim AccApp As Access.Application
-
-
TicDir = "C:\Documents and Settings\g43692\Desktop\Jeff's Stuff\Ticketing System\"
-
-
Set AccApp = CreateObject("Access.Application")
-
AccApp.OpenCurrentDatabase TicDir & "Tickets.mdb"
-
StrTmp = Dir$(TicDir & "Tickets\*.xml")
-
-
Do While Len(StrTmp) 'Get all file names
-
NumFiles = NumFiles + 1
-
ReDim Preserve Filez(1 To NumFiles)
-
Filez(NumFiles) = StrTmp
-
StrTmp = Dir$()
-
'MsgBox Filez(NumFiles) & NumFiles
-
Loop
-
-
If (NumFiles > 0) Then
-
ReDim EmlAdd(1 To NumFiles)
-
ReDim TckNum(1 To NumFiles)
-
-
For i = 1 To NumFiles 'Import XML for all files into Access
-
AccApp.ImportXML DataSource:=TicDir & "Tickets\" & Filez(i), ImportOptions:=acAppendData
-
TckNum(i) = DMax("TicketNumber", "Tickets")
-
EmlAdd(i) = DMax("Email", "Tickets", "TicketNumber = " & TckNum(i))
-
MsgBox "Email: " & EmlAdd(i) & ", Ticket #:" & TckNum(i)
-
Next
-
-
'Kill (TicDir & "Tickets\" & "*.xml")
-
End If
-
-
Erase Filez()
-
Erase EmlAdd()
-
Erase TckNum()
-
AccApp.CloseCurrentDatabase
-
Set AccApp = Nothing
-
'ErrHand:
-
'AccApp.CloseCurrentDatabase
-
'MsgBox "ERROR: " & Err.Description
-
'Exit Sub
-
End Sub
-
-
