News, links, downloads, tips and tricks on Microsoft Access and related
Wednesday, October 29, 2008
Friday, October 10, 2008
GetTempFileName, MAX_PATH and Vista
If you need to create a temporary file name – you normally use GetTempFileNameA API function, like I do also. Here a sample: GetTempFile - Create a temporary file. I noticed that on my Vista PC it sometimes fails with Overflow error message. On other Vista PC it fails all the time. I have no idea why it fails sometimes only, but I found how to fix it - MAX_PATH constant should be set to 32768 - this is because NTFS file path limit is not 255 characters, but 32768. So, do not forget to update your source code if you are using this function.
Update: Recently I found that increasing MAX_PATH does not always help, I am still getting Error #6: “Overflow” sometimes on some PCs. I think the reason that too many files in Temp folder, file is actually generated, even when error occurs. So I just had to use On Error Resume Next for a while…
Thursday, October 09, 2008
Getting Run-time errors descriptions
This is from the post of Dirk Goldgar, Access MVP, I saw the question in newsgroups, but forgot about this AccessError() function. Keeping it here. It only works on English Access, so for localized version you have to adjust it.
Dim strErrDesc As String
For i = 1 To 65535
strErrDesc = Error(i)
If strErrDesc = "Application-defined or object-defined error" Then
strErrDesc = AccessError(i)
Print #1, i & ", " & strErrDesc
Tuesday, October 07, 2008
Export to Excel - There are too many rows to output
I was just hit by Run-time error 2306: “There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access”, produced by this line:
DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLS, , True
Query has about 18000 records, what should not be a problem for Excel. Fortunately I found that replacing acFormatXLS with acSpreadsheetTypeExcel9 did the trick, so this line would work:
DoCmd.OutputTo acOutputQuery, "MyQuery", acSpreadsheetTypeExcel9, , True
Interesting that using acSpreadsheetTypeExcel9 is not documented in online help or object browser, and both constants has quite different values:
Const acSpreadsheetTypeExcel9 = 8
Const acFormatXLS = "Microsoft Excel (*.xls)"
Anyway – it works! If anybody have explanation to this – would be great to learn.
BTW - DoCmd.TransferSpreadsheet will also work in this case.