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-docmd-outputto-method/

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

[cleteh]

19406

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