By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,960 Members | 987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,960 IT Pros & Developers. It's quick & easy.

Limitation of DoCmd.OutputTo acOutputQuery

ChaseCox
100+
P: 294
I am trying to output the results of my query to an excell file, with the user not having to go through the standard controls. In other words, I want the query results to get output to an excell file, if they click the appropriate button on a form. Is there a limit on how many records you can output other than the one set by excell, of only allowing around 65,000. I can output 10 thousand records, but not 24,000, any thoughts?
Mar 17 '07 #1
Share this Question
Share on Google+
23 Replies


ChaseCox
100+
P: 294
Anyone have any thoughts about this?
Mar 20 '07 #2

Denburt
Expert 100+
P: 1,356
Maybe you should try using TransferSpreadsheet method of DoCmd instead.
Mar 20 '07 #3

ChaseCox
100+
P: 294
Maybe you should try using TransferSpreadsheet method of DoCmd instead.
Thanks, but it still size output to many rows based on limit set by command do you know how to control this limit?
Mar 20 '07 #4

Denburt
Expert 100+
P: 1,356
???

Your size constraint using the transferspreadsheat method is 65,536

I just ran this on a table with over 880,000 records and no error it just stopped exporting data when Excel filled up the one sheet.

What is the exact wording of your error and what is the code you are using?

Expand|Select|Wrap|Line Numbers
  1. Public Function DoME()
  2. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "WeatherData", "MyFile1", True
  3. End Function
  4.  
Mar 20 '07 #5

ChaseCox
100+
P: 294
Expand|Select|Wrap|Line Numbers
  1. Public Function DoME()
  2. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "WeatherData", "MyFile1", True
  3. End Function
  4.  
[/quote]

Ok, I was using the wrong syntax. Now I get an error that says type mismatch. Can you explain what each part of your code string represents starting with acSpreadsheetTypeExcel9, and so on? Thanks so much
Mar 20 '07 #6

Denburt
Expert 100+
P: 1,356
acSpreadsheetTypeExcel9 is just your spreadsheet type it is optional and really not needed.

Next is the table name or query I want to export, the next item is what you want the file name to be after it is exported. The true statement I have in there to let access know that I want my field names exported also.

Highlight the word "TransferSpreadsheet" in your VBA window and hit F1 the help file goes into much more detail.
Mar 20 '07 #7

ChaseCox
100+
P: 294
acSpreadsheetTypeExcel9 is just your spreadsheet type it is optional and really not needed.

Next is the table name or query I want to export, the next item is what you want the file name to be after it is exported. The true statement I have in there to let access know that I want my field names exported also.

Highlight the word "TransferSpreadsheet" in your VBA window and hit F1 the help file goes into much more detail.
I got this to work finally, but how would I code it if I wanted the user to be able to choose the destination of the file?
Mar 20 '07 #8

Denburt
Expert 100+
P: 1,356
Found this snipet for you paste the following into a module.

Expand|Select|Wrap|Line Numbers
  1. '************** Code Start **************
  2. 'This code was originally written by Terry Kreft.
  3. 'It is not to be altered or distributed,
  4. 'except as part of an application.
  5. 'You are free to use it in any application,
  6. 'provided the copyright notice is left unchanged.
  7. '
  8. 'Code courtesy of
  9. 'Terry Kreft
  10.  
  11. Private Type BROWSEINFO
  12.   hOwner As Long
  13.   pidlRoot As Long
  14.   pszDisplayName As String
  15.   lpszTitle As String
  16.   ulFlags As Long
  17.   lpfn As Long
  18.   lParam As Long
  19.   iImage As Long
  20. End Type
  21.  
  22. Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
  23.             "SHGetPathFromIDListA" (ByVal pidl As Long, _
  24.             ByVal pszPath As String) As Long
  25.  
  26. Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
  27.             "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
  28.             As Long
  29.  
  30. Private Const BIF_RETURNONLYFSDIRS = &H1
  31. Public Function BrowseFolder(szDialogTitle As String) As String
  32.   Dim X As Long, bi As BROWSEINFO, dwIList As Long
  33.   Dim szPath As String, wPos As Integer
  34.  
  35.     With bi
  36.         .hOwner = hWndAccessApp
  37.         .lpszTitle = szDialogTitle
  38.         .ulFlags = BIF_RETURNONLYFSDIRS
  39.     End With
  40.  
  41.     dwIList = SHBrowseForFolder(bi)
  42.     szPath = Space$(512)
  43.     X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
  44.  
  45.     If X Then
  46.         wPos = InStr(szPath, Chr(0))
  47.         BrowseFolder = Left$(szPath, wPos - 1)
  48.     Else
  49.         BrowseFolder = vbNullString
  50.     End If
  51. End Function
  52. '*********** Code End *****************
  53.  
  54.  
Then place the following where it is needed.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnBrowseForFolder_Click()
  2.  
  3.     Dim strFolderName As String
  4.  
  5.     strFolderName = BrowseFolder("Choose Folder For Import")
  6.  
  7.     If Len(strFolderName) > 0 Then
  8.         ' Insert the transferSpeadsheet here.
  9.         DoCmd.TransferSpreadsheet acExport, , "WeatherData", strFolderName & "MyFile1", True
  10.  
  11.     Else
  12.         'No folder chosen, or user canceled
  13.     End If
  14.  
  15. End Sub
  16.  
This should do what you need.
Mar 20 '07 #9

ChaseCox
100+
P: 294
What is this code suppose to do. The option on the folder selection that opens up says something about importing, but I would like to export my table into an excell spreadsheet. When I click the button to execute the macro, it says that my database or object is read only.
Mar 22 '07 #10

ChaseCox
100+
P: 294
I got it to work, but it still only putputs 16,300 records now, when I should be seeing about 50,000. Any thoughts as to why this is happenening?
Mar 22 '07 #11

Denburt
Expert 100+
P: 1,356
That was merely the title for the folder box simply an oversight on my part the was also a missing "\". Now all you need to do is change the name "YourTableToExport" to the table you want exported and maybe change "\myFile1" to something more meaningful but leave the "\" at the beginning that is necessary.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnBrowseForFolder()
  2.  
  3.     Dim strFolderName As String
  4.  
  5.     strFolderName = BrowseFolder("Choose Folder For Export")
  6.  
  7.     If Len(strFolderName) > 0 Then
  8.         ' Insert the transferSpeadsheet here.
  9.         DoCmd.TransferSpreadsheet acExport, , "YourTableToExport", strFolderName & "\MyFile1", True
  10.  
  11.     Else
  12.         'No folder chosen, or user canceled
  13.     End If
  14.  
  15. End Sub
  16.  
I did test this and it worked so if you have any problems let me know.
Mar 22 '07 #12

ChaseCox
100+
P: 294
It is still only exporting 16,384 records, even though the table has 50,000. Any ideas as to why it does this? I also can not export to an empty folder. Would it matter which edition of Access I was using. I am using 97.
Mar 22 '07 #13

Denburt
Expert 100+
P: 1,356
I couldn't say... I just created a new folder and exported 65,536 records to excel from a database table that has 880,720 records in it.

Are you sure you are exporting directly from your table in question and not a query?
Mar 22 '07 #14

ChaseCox
100+
P: 294
I couldn't say... I just created a new folder and exported 65,536 records to excel from a database table that has 880,720 records in it.

Are you sure you are exporting directly from your table in question and not a query?
Yes, I compared randomly through what was exported and the table, what is exported is the first 19,000 or so, but after that, no more.
Mar 22 '07 #15

Denburt
Expert 100+
P: 1,356
It must be an issue with that table then check the data and make sure that the integrity of the table is sound. Verify all lookUps, and check your indexs. Verify that there are no duplicates in fields designated as such and the fields designated as required actually has data. It is possible that I am missing something but beyond this I could only say that it could be corrupted somehow. Maybe you could try it on a different table or different database....
Mar 22 '07 #16

ChaseCox
100+
P: 294
It must be an issue with that table then check the data and make sure that the integrity of the table is sound. Verify all lookUps, and check your indexs. Verify that there are no duplicates in fields designated as such and the fields designated as required actually has data. It is possible that I am missing something but beyond this I could only say that it could be corrupted somehow. Maybe you could try it on a different table or different database....

thanks for the help. I tried a different in table in the DB, and it exported the exact same number of rows. I will try and figure it out I guess. Thanks for all of your help and time.
Mar 22 '07 #17

ChaseCox
100+
P: 294
UPDATE: I just exported the table in question using saveAs/export command, and I got the same result. So I need to identify the problem being with access or excell.
Mar 22 '07 #18

Denburt
Expert 100+
P: 1,356
Yep solve that and you should be good to go, I wish I had more suggestions for you to look for but at the moment I can't think of anything that may cause this issue. Good luck and when you figure it out let us know I am curious as to why this could be hapening.
Mar 22 '07 #19

ChaseCox
100+
P: 294
Yep solve that and you should be good to go, I wish I had more suggestions for you to look for but at the moment I can't think of anything that may cause this issue. Good luck and when you figure it out let us know I am curious as to why this could be hapening.

HOORAY HOORAY:

I have solved the mystery, I update this line:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, 8, "Voyager2Table", strFolderName & "\MyFile1", True
  2.  
The 8 is what sets the limit to the appropriate 65k limit. I am going from access 97 to excell 2003, which requires that 8 to be there!!!

Is there a way to prompt the user to change the name of the file before it is saved?
Mar 22 '07 #20

Denburt
Expert 100+
P: 1,356
Awesome glad you found it.


Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Version", strFolderName & "\" & InputBox("Pop"), True
  2.  
Mar 22 '07 #21

Denburt
Expert 100+
P: 1,356
Change POP to the message you want displayed. :) Sry
Mar 22 '07 #22

ChaseCox
100+
P: 294
Thanks, Everything is looking tip-top!
Mar 22 '07 #23

Denburt
Expert 100+
P: 1,356
Awesome! Glad to know that everything is working.
Mar 22 '07 #24

Post your reply

Sign in to post your reply or Sign up for a free account.