Access - large number of fields to export into excel

I have an excel template given to me that requires 340 fields to be filled. I have just realized in the many years of working with access that there is a field limit to an access query. So I cannot do a single query. I then broke it into 2 queries and when I tried to call both the same thing happened it wont combine the results saying to many fields defined.
I am at a loss as to how to grab these fields and export them together. Can it be done doing it in sql  code ??? And please before I have someone tell me again about bad construction these are not my planning's. I work between a Healthcare org and Gov't, neither hears no, and usually idiots build this stuff, so I am forced to do what they want if possible. LOL so if there is a way to export from access 340 fields into excel I would be greatly appreciative for the help if possible.

I also thought of doing 2 exports but my fear is that after the first export the second could be missing a rec and the rest would be mismatched and no one could tell.
Stephen RoesnerAnalysisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<LOL so if there is a way to export from access 340 fields into excel I would be greatly appreciative for the help if possible.>>

 No real work a round that I'm aware of except to:

1. Write the data directly to a file (as a CSV).

2. Open Excel as an automation object and poke the data into the cells directly (Excel must be installed for this).

255 is the field limit in any query output no matter how you approach it and it is not modifiable.

Jim.
Stephen RoesnerAnalysisAuthor Commented:
Thanks Jim I was afraid of that

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
Jim's comment #2 is the way to go.

As long as your two queries return the records in the same sequence, you could use automation and the copyfromrecordset method top "poke" the results into the right cells.

You will have to write code to create the column headers from the query column names as well.  With the copyfromrecordset method, you can specify the starting cell where you want the recordset to go, so for the first recordset, you might indicate cell "A2", and for the 2nd recordset (the second set of 170 columns), you would indicate cell "FO2"
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

aikimarkCommented:
As long as the recordset (query results) rows will align, the Dale Fye solution is the best way to populate Excel (2007+) from Access.  You can put the code into either Access or Excel - push versus pull implementation.
PatHartmanCommented:
I feel your pain.  I too have the burden of trying to work with a state run IT department.  I have steeled myself to perpetually flying without a net since there is never any way to test anything, plus most of their requirement are intended to make their work easy and they don't care what it costs my non-profit agency.  Also, the people coming up with the designs are not in the US, don't speak English as their native language, and have no idea how business works in the US so their solutions leave much to be desired.

I would also start with creating a .csv file.  That will be more difficult than it should be because you will have to work with two open queries at once.  Make sure that there is a unique identifier and that the queries are both sorted on it.  Then, you will read a record from one query, concatenate the fields.  Then read the matching record from the other query and concatenate those fields.  Then write the record.  I've attached code that builds a non-conforming .csv file because in their inability to follow any standards, the state dictated a format that is not "standard" based on what Access thinks as standard and so I couldn't use TransferText to write it.  You'll need to build in the part that reads the second query.  Make sure the uniqueIDs match and stop the process if the two get out of sync.  I pass in one recordset.  You can pass in two.  This code is not format specific and I use it to create three different .csv files.
Then, all the automation for Excel that you need is to open the .csv with Excel and then say save as - a spreadsheet.  I don't have code handy but I have actually done this too for a different reason and I'll get it tomorrow if you still need it.  But, I figured out how to do it the same way I always figure out how to automate Excel.  Open Excel, turn on the macro recorder and perform the action.  Then take the code and pull it into Access.

Public Function ExportToCSV(rs As DAO.Recordset, strFile As String, Headers As Boolean)
    Dim fldLoop As DAO.Fields
    Dim fld As DAO.Field
    Dim strRec As String
    Dim FSO As FileSystemObject
    Dim fsoFile As TextStream
    Dim ExportCount As Long
    Dim RecLen As Integer
    Dim HoldEMS As String
    Dim holdAssessDate As String    
    
   On Error GoTo Err_Proc
    
'''fso stuff
    Set FSO = New FileSystemObject
    Set fsoFile = FSO.CreateTextFile(strFile, True)
    strRec = ""
    
'''initialize fields collection
    Set fldLoop = rs.Fields
    
    'write headers if headers flag = true
    If Headers = True Then
        For Each fld In fldLoop
            strRec = strRec & """" & fld.name & ""","
        Next fld
        RecLen = Len(strRec)
        strRec = Left(strRec, RecLen - 1)       'chop off last comma
        fsoFile.WriteLine strRec
    End If
    
    'write data records
    Do While Not rs.EOF
        ExportCount = ExportCount + 1
        strRec = ""
        For Each fld In fldLoop
            strRec = strRec & """" & fld.Value & ""","
            If fld.name = "ems" Then
                HoldEMS = fld.Value
            End If
            If fld.name = "AssessDate" Then
                holdAssessDate = fld.Value
            End If
        Next fld
        RecLen = Len(strRec)
        strRec = Left(strRec, RecLen - 1)       'chop off last comma
        fsoFile.WriteLine strRec
        rs.MoveNext
    Loop
'''fso stuff
    fsoFile.Close
    Debug.Print ExportCount
Exit_Proc:
   On Error GoTo 0
   Exit Function

Err_Proc:

    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportToCSV of Module mExportToText"
            MsgBox "EMS = " & HoldEMS & " AND AssessDate = " & holdAssessDate, vbOKOnly
    End Select
    Resume Exit_Proc
    Resume
End Function

Open in new window

Stephen RoesnerAnalysisAuthor Commented:
I am in mexico on vacation will respond when i return
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Enjoy the vacation!

Jim.
Stephen RoesnerAnalysisAuthor Commented:
i understand Jims comments nand also will try pats solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.