Upgraded to Access 2013 Run-Time Error 3270

[cleteh]

Hi recently upgraded to Access 2013 and now I get run-time error 3270 when I click a command button with the following code that produced a report just fine in Access 2010. The error happens on the line that I highlighted red:

DoCmd.OutputTo acOutputServerView, "dbo_Issue Open 3", "ExcelWorkbook(*.xlsx)", "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint

Private Sub OpenIssues_Click()
Dim strFilePath As String
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim LastRow As Long
Dim i As Integer
'Export the open issues query results (created view in SQL server)
DoCmd.OutputTo acOutputServerView, "dbo_Issue Open 3", "ExcelWorkbook(*.xlsx)", "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint

'Sets strFilePath equal to the location of the excel file
strFilePath = "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx"
'Opens Excel
Set objXLApp = CreateObject("Excel.Application")
'Opens Workbook
Set objXLBook = GetObject(strFilePath)
'Opens the sheet
Set objXLSheet = objXLBook.Worksheets(1)
'for testing, make the application visible
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
'autofit and format the columns
objXLSheet.Activate
objXLSheet.Range("A1", "L1").Select
objXLSheet.Cells.EntireColumn.AutoFit

[werafa]

What did you upgrade from?

[werafa]

The error code is for "property not found "

[OBP]

The first thing you need to do is ensure that the VBA Library References have been reset to Access 2013 if they are different to Access 2010.

[HiTechCoach]

Hi recently upgraded to Access 2013 and now I get run-time error 3270 when I click a command button with the following code that produced a report just fine in Access 2010. The error happens on the line that I highlighted red:

DoCmd.OutputTo acOutputServerView, "dbo_Issue Open 3", "ExcelWorkbook(*.xlsx)", "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint

Private Sub OpenIssues_Click()
Dim strFilePath As String
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim LastRow As Long
Dim i As Integer
'Export the open issues query results (created view in SQL server)
DoCmd.OutputTo acOutputServerView, "dbo_Issue Open 3", "ExcelWorkbook(*.xlsx)", "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint

'Sets strFilePath equal to the location of the excel file
strFilePath = "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx"
'Opens Excel
Set objXLApp = CreateObject("Excel.Application")
'Opens Workbook
Set objXLBook = GetObject(strFilePath)
'Opens the sheet
Set objXLSheet = objXLBook.Worksheets(1)
'for testing, make the application visible
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
'autofit and format the columns
objXLSheet.Activate
objXLSheet.Range("A1", "L1").Select
objXLSheet.Cells.EntireColumn.AutoFit

First try this:

change "ExcelWorkbook(*.xlsx)" to acFormatXLSX

' Export the open issues query results (created view in SQL server)
DoCmd.OutputTo acOutputServerView, "dbo_Issue Open 3", acFormatXLSX, "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint

Also, is "I:\Groups\ccuser\Finance\Collection Control\" a trusted location?

[werafa]

I think Boyd might have it
http://access-excel.tips/access-vba-...tputto-method/

if not, try outputting to a text file, and set the parameters via string variables

[cleteh]

Libraries.jpg

I tried High Tech coach's suggestion but got the same error. The database was converted from .adp to .accdb Access 2013 won't run a .adp file. We were using Access 2010 to run the .adp file. At ione point during the conversion I was getting a similar error before I selected Microsoft Excel 15.0 Object Library once I saw Microsoft Excel 14.0 was missing.

When I looked at the libraries I currently had selected the only thing I saw that had a newer version was the Micorsoft ActiveX Data Objects 6.1 Library so I selected that and it still errors on the same line. I inserted an image of the libraries I have selected.

[werafa]

can you try the docmd.transferspreadsheet command?
ie, do the same thing a different way?

[cleteh]

I still get the error, I think it has something to do with my tables being linked instead of direct access to SQL Server

[werafa]

ah, yes, well.....

I hit the same snag. had to make a 'firewall', and copy essential linked-table data to a local table.
This code let me do it behind the scenes.

Public Sub CloseTables()
Dim myTable As AccessObject


    With CurrentData
        For Each myTable In .AllTables
            If myTable.IsLoaded Then
                DoCmd.Close acTable, myTable.Name, acSaveYes
            End If
        Next
    End With
End Sub


Function DeleteTable(myTable As Table) As Boolean
Dim myFlag As Boolean
    
    myFlag = True
    On Error GoTo DeleteTable_Err
    DoCmd.Close acTable, myTable, acSaveYes
    DoCmd.DeleteObject acTable = acDefault, myTable


    DeleteTable = myFlag
    
Exit Function
DeleteTable_Err:
    If Err = 7874 Then
    Resume Next
    Else
    MsgBox Error, Err
    myFlag = False
    End If
End Function


Sub DelRel(PriTable As String)
Dim dB As DAO.Database
Dim rel As DAO.Relation
Dim myFlag As Boolean


Set dB = CurrentDb()


    Do
        myFlag = True
        For Each rel In dB.Relations
            If rel.Table = PriTable Then
                'Debug.Print vbLf & "delete " & rel.Name
                dB.Relations.Delete rel.Name
                myFlag = False
            End If
        Next
    Loop Until myFlag = True
End Sub


Public Sub SetRelationship(primTable As String, secTable As String, primField As String, secField As String)
'set relationship between two tables
' one to many:
' with Referential integrity (on primary key field?)
' cascade update and delete are disabled


Dim dB As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim myName As String


Set dB = CurrentDb()
myName = primTable & secTable
Set rel = dB.CreateRelation(myName) 'Create a new relation.


    'Define its properties.
    With rel
        .Table = primTable                      'Specify the primary table.
        .ForeignTable = secTable                'Specify the related table.
        
        'Specify attributes for cascading updates and deletes.
        '.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
        


        'Add the fields to the relation.
        Set fld = .CreateField(primField)       'Field name in primary table.
        fld.ForeignName = secField              'Field name in related table.
        .Fields.Append fld                      'Append the field.
    End With


    'Save the newly defined relation to the Relations collection.
    dB.Relations.Append rel
    
End Sub


Public Function UpdateSalesStaffTable()


    'close all open tables
    Call TableManagement.CloseTables
    
    'delete relationships
    Call TableManagement.DelRel("tblSalesStaff")
    
    'refresh table
    Call TableManagement.UpdateTable
    
    'Reset relationships
    Call TableManagement.SetRelationship("tblSalesStaff", "WeeklyKPIs", "StaffID", "StaffName")
    Call TableManagement.SetRelationship("tblSalesStaff", "TeamMembers", "StaffID", "TeamMember")
    Call TableManagement.SetRelationship("tblSalesStaff", "MonthlyKPIs", "StaffID", "StaffID")
 
End Function


Public Sub UpdateTable()
'make local, stand-alone table of sales staff
Dim myDB As DAO.Database
Dim mySQL As String


    Set myDB = CurrentDb()
    
    'create SQL string
    mySQL = "SELECT Staff.ID AS StaffID, Staff.FirstName, Staff.Surname, " _
        & "[Firstname] & "" "" & [Surname] AS cName, Offices.Location, Organisations.OrgCode " _
        & "INTO tblSalesStaff " _
        & "FROM Organisations INNER JOIN (Offices INNER JOIN Staff ON Offices.ID = Staff.Office) " _
        & "ON (Organisations.ID = Staff.Org) AND (Organisations.ID = Offices.lOrg) " _
        & "WHERE (((Staff.HasSalesReporting)=True));"


    'run update
    If DeleteTable("tblSalesStaff") = False Then
        'delete failed
        MsgBox ("Warning: Update tblSalesStaff failed")
        Exit Sub
    End If
    
    myDB.Execute mySQL


    'set primary key column
    myDB.Execute "CREATE INDEX NewIndex ON tblSalesStaff(StaffID) With PRIMARY"


End Sub