vba Run-time error '3190" Too many fields defined.

I have a table that has 16 fields.  I am trying to export the table out.

I get the Run-time error '3190"   Too many fields defined.


Private Sub SendReports()
'==============================================================================================================
'Append End of Day Tables to EXCEL End of Day Tables----- 09/15/2010
'==============================================================================================================

DoCmd.TransferSpreadsheet acExport, , "EOD_tbl_PairOut_Sophia", "\\XXXXXXX\XXXXX\BuyerEndofDay\EndOfDay.xls", True, "PairOut_XXXXX"
End Sub

Open in new window

ca1358Asked:
Who is Participating?
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.

Helen FeddemaCommented:
I think the problem may be with the Range argument (the last one in your line of code).  This argument is only used when importing from Excel.  Try leaving it out and see what happens.  Also, I recommend using named arguments -- it makes the code much more readable.  Here is a sample:
strPath = Application.CurrentProject.Path
strWorkbook= strPath & "Customers.xls"

DoCmd.TransferSpreadsheet transfertype:=acExport, _
   spreadsheettype:=acSpreadsheetTypeExcel8, _
   tablename:="qryCustomers", _
   FileName:=strWorkbook, _
   hasfieldnames:=True

DoCmd.TransferSpreadsheet transfertype:=acImport, _
   spreadsheettype:=acSpreadsheetTypeExcel9, _
   tablename:=strTable, _
   FileName:=strWorkbook, _ 
   hasfieldnames:=True, _
   Range:=strRange

Open in new window

Helen FeddemaCommented:
If you need the data from Access to be put into a certain range, you may need to use the CopyFromRecordset method of an Excel range, as in the code below:
Dim appExcel As New Excel.Application
   Dim cnn As ADODB.Connection
   Dim wkb As Excel.Workbook
   Dim sht As Excel.Worksheet
   Dim strWorkbook As String
   Dim strRange As String
   Dim lngLastRow As Long
   Dim rst As ADODB.Recordset
   Dim rng As Excel.Range
   Dim strWorkbookName As String
   Dim strDefault As String
   
   DoCmd.SetWarnings False
   strPrompt = "Enter workbook name (no extension)"
   strTitle = "Workbook name"
   strDefault = "New Access Data"
   strWorkbookName = InputBox(strPrompt, strTitle, strDefault)
   
   'Run make-table queries
   DoCmd.OpenQuery "qmakCAContacts"
   
   Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   
   'Create a recordset based on a select query.
   rst.Open Source:="qryContacts", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
      
   'Export first query
   Set wkb = appExcel.Workbooks.Add
   appExcel.Visible = True
   strWorkbook = Application.CurrentProject.Path & "\" & strWorkbookName
   wkb.SaveAs FileName:=strWorkbook
   Set sht = wkb.Sheets(1)
   strRange = "A1"
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close

Open in new window

Rey Obrero (Capricorn1)Commented:
try doing a compact and repair
office button >manage > compact and repair

then run the codes

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 , "EOD_tbl_PairOut_Sophia", "\\XXXXXXX\XXXXX\BuyerEndofDay\EndOfDay.xls", True, "PairOut_XXXXX"
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

GrahamSkanRetiredCommented:
You have specified a Range (PairOut_XXXXX) which should be blank when exporting.
Rey Obrero (Capricorn1)Commented:
try this one, see if you still get the error

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 , "EOD_tbl_PairOut_Sophia", "\\XXXXXXX\XXXXX\BuyerEndofDay\EndOfDay.xls", True, "NameOFSheet"

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
ca1358Author Commented:
Thank you.
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.