none
Excel 2010 VBA/vbScript problem RRS feed

  • Question

  • Hi, I'm tearing my hair out on this one because it makes little sense.

    I have an Excel workbook designed to take information from SQL Server 2012, manipulate the data and overwrite a table in an Access 2010 database. It does the latter part by Automating Access and deleting the entire contents of the table before Access then pulls the data into the table using DoCmd.TransferSpreadsheet.

    Everything works perfectly when triggered manually but as soon as I run the code via vbScript (I want to trigger this via Windows Task Scheduler) it bombs out at the Delete * from table line. The code will then always fail at that point from then on - even when run manually - unless I delete and recreate the Access database and table.

    Has anyone else come across this kind of behaviour? Code below if it helps...

    Private Sub AccImport()
        Dim acc As Access.Application
        Dim myTimer As New clsTimer
        Dim strSQL As String
        
        On Error GoTo ErrorHandler
    
        myTimer.Starts ("Import to Access")
    
        If Len(Dir(ACC_DB, vbNormal)) > 0 Then
            Set acc = New Access.Application
            acc.OpenCurrentDatabase Filepath:=ACC_DB, Exclusive:=True
    
            ' Clear the existing data first
            strSQL = "DELETE * FROM " & ACC_TBL
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
    
            ' Then transfer in one go (NB. Access is 'pulling' the data from Excel)
            acc.DoCmd.TransferSpreadsheet _
                    TransferType:=acImport, _
                    SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
                    TableName:=ACC_TBL, _
                    Filename:=ThisWorkbook.FullName, _
                    HasFieldNames:=True, _
                    Range:="Export!"
    
            acc.CloseCurrentDatabase
    
            WriteLog aEvent:="AccImport", aResult:="Successful transfer to " & acc.Name
        Else
            WriteLog aEvent:="AccImport", aResult:="*** Transfer to " & acc.Name & " failed - Database not present! ***"
        End If
    
    ErrorExit:
        myTimer.Finishes
        
        On Error Resume Next
    
        acc.Quit
        Set myTimer = Nothing
        Set acc = Nothing
    
        Exit Sub
    
    ErrorHandler:
        Dim errNum As Long
        Dim errDesc As String
    
        errNum = Err.Number
        errDesc = Err.Description
    
        WriteLog aEvent:="AccImport", aResult:="Error: " & errNum & "  Desc: " & errDesc
    
        GoTo ErrorExit
    End Sub
    

    tbh I'd really like to write the data back to SQL Server but despite trying numerous code examples, from various forums, I've not managed to get it to work. I'm a competent VBA coder but writing to databases seems to be my Achilles heel.

    Nick

    Tuesday, August 25, 2015 9:39 PM

Answers

  • Hi Nick,

    Based on my understanding, we can use link table in Access to sync up with SQL server instead import data and update the table manually.

    You can get more detail about this topic from link below:

    Import or link to data in another Access database

    >>Everything works perfectly when triggered manually but as soon as I run the code via vbScript (I want to trigger this via Windows Task Scheduler) it bombs out at the Delete * from table line. The code will then always fail at that point from then on - even when run manually - unless I delete and recreate the Access database and table.<<

    What's the error message and what did you mean run it manually? Since you were using Windows Task Scheduler, I suggest that you use ADO.Net/Ado to manipuldate the data in Access instead of Access automation since it may cause the Access automation failed because the enviroment is not interactive. You can get more detail about this topic from link below:
    Considerations for server-side Automation of Office

    Regards & Fei 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 26, 2015 8:26 AM
    Moderator