ACCESS FEED: Re: [MS_AccessPros] query must be updateable on transferspreadsheet?

Kamis, 15 Oktober 2015

Re: [MS_AccessPros] query must be updateable on transferspreadsheet?

Hi Liz,

you're welcome

what is strName2?

for importing, Instead of "Actual Data!" perhaps try just "Actual Data" ?

Does the Actual Data sheet have external references? or reference to other sheets?

> "marking this one as no-longer-necessary-to-attend-to."

okey dokey then :) ~

warm regards,
crystal

Document Query SQL, Form and Report RecordSources
http://www.rogersaccesslibrary.com/forum/document-query-sql-form-and-report-recordsources_topic606.html

~ have an awesome day ~

On 10/15/2015 11:25 AM, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] wrote:

Thanks Crystal. This is code from somebody who no longer works here.

She has the code in her xlsm file open the database and import from yet a different excel file. It's pretty convoluted and complicated and then disperses out to all kinds of pivot tables and such.

The last report I heard was that "well, it works for the most part, but some of the pivot tables look a little funny" and "the boss is going to change things so we might be doing things differently anyway." I think they'll go with a corporate solution, so I'm marking this one as no-longer-necessary-to-attend-to.

J Thanks for the education.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 15, 2015 10:04 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] query must be updateable on transferspreadsheet?

Hi Liz, instead of using TransferSpreadsheet, I like to use CopyFromRecordset

instead of acImport, shouldn't you be using acExport? are you getting or putting?

here is some code using CopyFromRecordset that was posted by Nate Oliver, who used to be an Excel MVP and now, unfortunately, has passed. His code lives on!

'~~~~~~~~~~~~~~~~~~~
Sub MakePullLists(pPath As String, pQname As String)

   On Error GoTo Proc_Err

   ' originally posted by NateO
   ' modified slightly by crystal

'Declare your ADO Recordset
Dim rs As ADODB.Recordset

'Excel Objects
' Dim xlApp As Excel.Application 'early binding for dveloping
' Dim xlWb As Excel.Workbook
Dim xlApp As Object 'late binding for distribution
Dim xlWb As Object

'Field Names - Stack into Array
Dim fldArr() As String

   'Need some loop counters
Dim j As Long _
   , iQ As Long _
   , i As Long

Dim sFilename As String
sFilename = pPath & pQname

i = 1

'OLE - Create xl Objects
Set xlApp = New Excel.Application

'Add a new Workbook, with one Worksheet, to our Excel Application
Set xlWb = xlApp.Workbooks.Add(1)

'this is commented out because to show you can loop if you want
'For i = LBound(sqlArr) To UBound(sqlArr)

    'New ADO Recordset
    Set rs = New ADODB.Recordset

    'Open the Recordset, Passing the Sql from our Array
    rs.Open CurrentDb.QueryDefs(pQname).SQL, CodeProject.Connection, _
      adOpenStatic, adLockReadOnly

    With rs
      'Stack a String Array with the Field Names
      ReDim fldArr(0 To .Fields.Count - 1)

      For j = LBound(fldArr) To UBound(fldArr)
        Let fldArr(j) = .Fields(j).Name
      Next j

      'Time to Pass some Data to Excel!
      With xlWb.Worksheets
         'Add a Worksheet if we're at 2nd Recordset or Greater
        If i > 1 Then .Add After:=.Item(i - 1)
         'Refer to the Worksheet by Item Number
         'in the Collection of Worksheets (1-based)
        With .Item(1)
           'Pass our dynamic Field String Array to A1,
           'stretched to the Right for number of Elements
           Let .Range("a1").Resize(, UBound(fldArr) + 1).Value = fldArr

           'Copy our Current Recordset to A2
          .Range("a2").CopyFromRecordset rs

          'Rename Individual Worksheet
          .Name = "WorksheetName"

          'however many columns of data you have, if desired
          .Columns("A:G").EntireColumn.AutoFit

        End With
      End With
    End With

   'Moving on, no need to close or terminate our RS, yet,
   ' we're going to recycle in the Loop

'end of optional loop
'Next

'Make Excel visible - (Otherwise Save and Close)
With xlApp
    .Goto xlWb.Worksheets(1).Range("A1")
    'commented but can be activated if desired
    'xlSht.Cells(1, 1).Activate
    '.Visible = True
End With

Save_Workbook:

   'delete file if it already exists
   If Dir(sFilename) <> "" Then
     On Error Resume Next
     Kill sFilename
     DoEvents
     On Error GoTo Proc_Err
   End If

   'commented because there was not code to disable in this case
   'if you are writing to a template with code, you may want
   'to disable events in the beginning
   'xlApp.EnableEvents True

   xlApp.ActiveWorkbook.SaveAs sFilename
   xlApp.ActiveWorkbook.Close False

Proc_Exit:
On Error Resume Next

'Terminate our Excel Object Variables
Set xlWb = Nothing
If TypeName(xlApp) <> "Nothing" Then
   xlApp.Quit
   Set xlApp = Nothing
End If

   'Now close and terminate the ADO Recordset, we're all done!!
rs.Close: Set rs = Nothing

Exit Sub

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " MakePullLists"
Resume Proc_Exit
'press Ctrl-Break to stop code at Msgbox
'set this to be the next statement then F8 to step through code and debug
Resume

End Sub
'~~~~~~~~~~~~~~~~~~~

warm regards,
crystal

~ have an awesome day ~

On 10/15/2015 8:43 AM, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] wrote:

Hi Crystal,

That is what was so weird. It is a table.

I have a theory that I haven't checked out yet though. There is something running when the excel first opens and sometimes takes longer than other times. I suspect this is actually preparing this table and if the user clicks the button that causes the code to run prematurely, I think it locks.

I did get a report from her that the process went through that point yesterday but now we're on to something else --- some other bit that isn't getting updated further along in the code.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, October 14, 2015 6:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] query must be updateable on transferspreadsheet?

Hi Liz,

What is the SQL for "WO Routing Actual vs Standard"?

warm regards,
crystal

~ have an awesome day ~

On 10/14/2015 4:06 PM, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] wrote:

Pros, This is a command from a db that got dumped on me and I don't know why it is breaking.

objAcc.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _

"WO Routing Actual vs Standard", strName2, True, "Actual Data!"

Gives the error query must be updateable. Other than someone having the table open, I don't know why it is crashing.

Respectfully,

Liz Ravenwood

Programmer / Analyst

B/E Aerospace | Super First Class Environments

1851 S Pantano Road | Tucson, Arizona 85710

Office +1.520.239.4808 | Internal 814-4808

beaerospace.com

Passion to Innovate. Power to Deliver

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.