Outlook/Access connection help

[jrworsham]

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
 
 

[Dököll]

Have you tried using Debug, and step through it?

[jrworsham]

Thanks for the tip I actually figured it out. I needed to add a line to refresh the current database before DMax would work the second time. Don't know why this would be needed. DMax seems to have a lot of quirks.