|  | Error Msg if No C Drive | | |
| | | Bob Vance |  |
|  Posted: Sat Aug 30, 2008 8:42 pm Post subject: Error Msg if No C Drive |  |
can I add an error just in case there is no C Drive!
Private Sub cmdInvoices3M_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryInvoiceReport3M", "C:\Temp\Invoices3Months.xls"
End Sub
--
Thanks in advance for any help with this......Bob
WindowsXP..MS Access 2007 |
| |
| | | Larry Linson | |
| | Posted: Sat Aug 30, 2008 9:03 pm Post subject: Re: Error Msg if No C Drive | |
| |  | |
"Bob Vance" <rjvance@ihug.co.nz> wrote in message
news:uME3jGvCJHA.1224@TK2MSFTNGP02.phx.gbl...
| Quote: |
can I add an error just in case there is no C Drive!
Private Sub cmdInvoices3M_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryInvoiceReport3M", "C:\Temp\Invoices3Months.xls"
End Sub
|
Here is the structure/template I use in most of my VBA procedures:
'---------------------------------------------------------------------------------------
' Procedure : SampleSub
' DateTime : 8/30/2008 17:54
' Author : LARRY LINSON
' Purpose :
'---------------------------------------------------------------------------------------
'
On Error GoTo PROC_Error
PROC_Exit:
Exit Sub
PROC_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
SampleSub of Module MiscAndEtc"
Resume PROC_Exit:
For your information, I have that template set up in a freely downloadable
package called MZTools, available from LINK You can do
something similar with a (not free) product from FMS, Inc.
LINK Both packages provide other functionality, too. This
is very basic error reporting; error _handling_ would include specific
actions and/or specific messages for particular errors. That, often, is the
result of including something like this to determine the error reported for
a particular condition -- there are so many error reports, numbers, and
description that you'd have to have a photographic memory, and there's no
detailed documentation... although a search in the archives could yield code
to print error code and descriptions. I never found that particularly
productive, because sometimes, just in a list, out-of-context, the
descriptions can sometimes (er, mostly?) be rather cryptic. You better try
code on some other drive letter that you know is not there -- I'm sure I
have never worked on a PC or PC-compatible that did not have a C drive.
<SMILE>
Larry Linson
Microsoft Office Access MVP |
| |
| | | Bob Vance | |
| | Posted: Sat Aug 30, 2008 9:21 pm Post subject: Re: Error Msg if No C Drive | |
| | | |
I thought that to Larry but a friend Ive mine said his drive was H
<confused>
"Larry Linson" <bouncer@localhost.not> wrote in message
news:g9cjjp$fb7$1@aioe.org...
| Quote: |
"Bob Vance" <rjvance@ihug.co.nz> wrote in message
news:uME3jGvCJHA.1224@TK2MSFTNGP02.phx.gbl...
can I add an error just in case there is no C Drive!
Private Sub cmdInvoices3M_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryInvoiceReport3M", "C:\Temp\Invoices3Months.xls"
End Sub
Here is the structure/template I use in most of my VBA procedures:
'---------------------------------------------------------------------------------------
' Procedure : SampleSub
' DateTime : 8/30/2008 17:54
' Author : LARRY LINSON
' Purpose :
'---------------------------------------------------------------------------------------
'
On Error GoTo PROC_Error
PROC_Exit:
Exit Sub
PROC_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
SampleSub of Module MiscAndEtc"
Resume PROC_Exit:
For your information, I have that template set up in a freely downloadable
package called MZTools, available from LINK You can do
something similar with a (not free) product from FMS, Inc.
LINK Both packages provide other functionality, too.
This is very basic error reporting; error _handling_ would include
specific actions and/or specific messages for particular errors. That,
often, is the result of including something like this to determine the
error reported for a particular condition -- there are so many error
reports, numbers, and description that you'd have to have a photographic
memory, and there's no detailed documentation... although a search in the
archives could yield code to print error code and descriptions. I never
found that particularly productive, because sometimes, just in a list,
out-of-context, the descriptions can sometimes (er, mostly?) be rather
cryptic. You better try code on some other drive letter that you know is
not there -- I'm sure I have never worked on a PC or PC-compatible that
did not have a C drive. <SMILE
Larry Linson
Microsoft Office Access MVP
|
|
| |
| | | Tom Wickerath | |
| | Posted: Sat Aug 30, 2008 9:44 pm Post subject: RE: Error Msg if No C Drive | |
| | | |
Hi Bob,
As Larry indicates, it would be very rare, indeed, to find a PC that does
not have a C: drive. On the other hand, I would expect that it is more common
to find a system that does not already have a Temp folder created, child to
the root drive (ie. C:\temp). You can trap for this error, and use VBA code
to create the temp folder if it is missing.
Here is code that I use in a startup form of one of my databases, to ensure
that the user is not attempting to run it from a file server. Instead, I want
them to only run it from their local hard drive:
Option Compare Database
Option Explicit
Private Sub Form_Load()
On Error GoTo ProcError
Dim strDriveType As String
' Ensure that the user is running this application from their own local hard
drive
strDriveType = DriveType(Left$(CurrentProject.FullName, 1))
If strDriveType <> "Hard Disk" Then
MsgBox "Please copy this application to a folder on your" & vbCrLf _
& "local hard drive before attempting to run it.", vbCritical, _
"Cannot Run From This Location..."
DoCmd.Quit
End If
' Populate global variable gstrUserName with user's NT Login ID
fOSUserName
' Set Explorer option to the value last selected by user
Me.fraOpenExplorer.Value = Nz(DLookup("blnOpenExplorer", "tblDefault"), 0)
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Load..."
Resume ExitProc
End Sub
The above procedure call the DriveType function, which I have in a
stand-alone module. This function is a slight adaptation from a KB article.
As you can see, it includes a constant for DRIVE_ROOT_NOT_EXIST.
Module Name: basDetermineDriveType
Option Compare Database
Option Explicit
' How To Determine the Type of Drive Using Win32 (Modified version)
' LINK
Private Declare Function GetDriveType Lib "kernel32" Alias _
"GetDriveTypeA" (ByVal sDrive As String) As Long
Function DriveType(sDrive As String) As String
Dim sDriveName As String
Const DRIVE_TYPE_UNDETERMINED = 0
Const DRIVE_ROOT_NOT_EXIST = 1
Const DRIVE_REMOVABLE = 2
Const DRIVE_FIXED = 3 '<---This is the only type we want to
allow.
Const DRIVE_REMOTE = 4
Const DRIVE_CDROM = 5
Const DRIVE_RAMDISK = 6
sDriveName = GetDriveType(sDrive & ":\")
Select Case sDriveName
Case DRIVE_TYPE_UNDETERMINED
DriveType = "has not been recognized"
Case DRIVE_ROOT_NOT_EXIST
DriveType = "Specified drive doesn't exist"
Case DRIVE_CDROM
DriveType = "CD-ROM drive."
Case DRIVE_FIXED
DriveType = "Hard Disk"
Case DRIVE_RAMDISK
DriveType = "is a RAM disk."
Case DRIVE_REMOTE
DriveType = "Network drive."
Case DRIVE_REMOVABLE
DriveType = "Removable Disk."
End Select
End Function
------------------------------------
Here is an abbreviated form of an error-handler in another procedure, where
I create an output folder if it is not already present. There are several
possible Err.Numbers that I test for, but error 76 is the important one in
this case:
ProcError:
Select Case Err.Number
Case 76 ' Path not found
MkDir CurrentProject.Path & "\" & conOutputFolder2
Resume
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in RearrangePFA procedure..."
End Select
Resume ExitProc
End Sub
In my case, the constant "conOutputFolder2" was defined at the beginning of
this procedure as follows:
Const conOutputFolder2 As String = "Processed PFA Files"
In your case, you could define a similar constant as:
Const conOutputFolder As String = "C:\temp"
(after you had first run code to ensure that the C drive was present). If
you defined it as above, then the make directory statement would be modified
as follows:
Select Case Err.Number
Case 76 ' Path not found
MkDir conOutputFolder
I have a download available where you can see all of this in action. It is
available on the Seattle Access web site.
LINK
Look for the following download:
Compilation of Tools by Tom Wickerath, October 2006 --
Download (656 kb) slides
Note: One of the Word documents in the downloadable zip file, "Find your
data.doc", was simply an announcement that I made at the start of my
presentation regarding the availability of a free article on a normally
subscription-only web service.
Hope this is helpful to you.
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Bob Vance" wrote:
| Quote: |
can I add an error just in case there is no C Drive!
Private Sub cmdInvoices3M_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryInvoiceReport3M", "C:\Temp\Invoices3Months.xls"
End Sub
--
Thanks in advance for any help with this......Bob
WindowsXP..MS Access 2007 |
|
| |
| | | Arvin Meyer [MVP] | |
| | Posted: Sun Aug 31, 2008 1:11 am Post subject: Re: Error Msg if No C Drive | |
"Bob Vance" <rjvance@ihug.co.nz> wrote in message
news:eysPucvCJHA.4932@TK2MSFTNGP03.phx.gbl...
| Quote: | I thought that to Larry but a friend Ive mine said his drive was H
confused
I'm sure I have never worked on a PC or PC-compatible that did not have a
C drive. <SMILE
Larry Linson
Microsoft Office Access MVP
|
I don't have a C:\ Drive on one of my machines. If one dual (or more) boots,
it is very easy not to have the systemroot on C:\. Also, sloppily written
viruses will look for the C:\ drive instead of the root drive, so one can
slightly increase security.
The %systemroot% drive is how one refers to the drive that has the system
files on it.
--
Arvin Meyer, MCP, MVP
LINK
LINK
LINK |
| |
| | | bcap | |
| | Posted: Sun Aug 31, 2008 7:12 am Post subject: Re: Error Msg if No C Drive | |
Why not put the file in the user's own Temp folder?
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryInvoiceReport3M", Environ("userprofile") & "\Temp\Invoices3Months.xls"
"Bob Vance" <rjvance@ihug.co.nz> wrote in message
news:uME3jGvCJHA.1224@TK2MSFTNGP02.phx.gbl...
| Quote: |
can I add an error just in case there is no C Drive!
Private Sub cmdInvoices3M_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryInvoiceReport3M", "C:\Temp\Invoices3Months.xls"
End Sub
--
Thanks in advance for any help with this......Bob
WindowsXP..MS Access 2007
|
|
| |
| | | David W. Fenton | |
| | Posted: Sun Aug 31, 2008 7:44 pm Post subject: RE: Error Msg if No C Drive | |
| | | |
=?Utf-8?B?VG9tIFdpY2tlcmF0aA==?= <AOS168b AT comcast DOT net> wrote
in news:1093F7DD-8B76-44EA-9D60-92A6351CA6CE@microsoft.com:
| Quote: | Const conOutputFolder As String = "C:\temp"
|
This is just bloody stupid code.
The root of the C: drive has not be writable for user-level logons
since the release of Windows 2000. The root of C: is absolutely the
wrong place to put any files belonging to a user.
If you're going to create a temp folder, you need to do it somewhere
that is writable. On the other hand, there is no such thing as an
instance of Windows that doesn't have a temp folder already created
for the user logon (it's part of the default user profile creation).
*That* is the right location to put temp files and no other. Ever.
And, again, you should NEVER create folders in the root of C:,
period, end of statement. Recommending such a thing shows that you
are completely out of date in regard to how Windows works, as well
as being someone accustomed to programming for users who are running
in dangerous administrative-level user logons.
--
David W. Fenton LINK
usenet at dfenton dot com LINK |
| |
| | | Tom Wickerath | |
| | Posted: Sun Aug 31, 2008 10:07 pm Post subject: RE: Error Msg if No C Drive | |
| | | |
David,
| Quote: | The root of the C: drive has not be writable for user-level logons
since the release of Windows 2000.
|
Whatever. At my place of work, which is a Fortune 100 Company, I do not have
administrative privileges on my PC. Yet, I can write to the root of the C:
drive without any problem! Yeah, okay, so Mr. David Fenton knows more about
PC security than the folks hired to administer this job at The Boeing
Company. Ya, sure, you-betcha.
I was simply providing an answer to the question that the OP, Bob Vance,
asked. If he wants to use a temp folder off the root drive, then it's not up
to me to come down on him like you have just done to me. I think you can
stick your comments where the light doesn't shine.
| Quote: | On the other hand, there is no such thing as an
instance of Windows that doesn't have a temp folder already created
for the user logon (it's part of the default user profile creation).
*That* is the right location to put temp files and no other. Ever.
|
You're apparently unaware of the fact that the Vista operating system no
longer allows one to write to this folder programatically, using VBA code
within Access.
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"David W. Fenton" wrote:
| Quote: | =?Utf-8?B?VG9tIFdpY2tlcmF0aA==?= <AOS168b AT comcast DOT net> wrote
in news:1093F7DD-8B76-44EA-9D60-92A6351CA6CE@microsoft.com:
Const conOutputFolder As String = "C:\temp"
This is just bloody stupid code.
The root of the C: drive has not be writable for user-level logons
since the release of Windows 2000. The root of C: is absolutely the
wrong place to put any files belonging to a user.
If you're going to create a temp folder, you need to do it somewhere
that is writable. On the other hand, there is no such thing as an
instance of Windows that doesn't have a temp folder already created
for the user logon (it's part of the default user profile creation).
*That* is the right location to put temp files and no other. Ever.
And, again, you should NEVER create folders in the root of C:,
period, end of statement. Recommending such a thing shows that you
are completely out of date in regard to how Windows works, as well
as being someone accustomed to programming for users who are running
in dangerous administrative-level user logons.
--
David W. Fenton LINK
usenet at dfenton dot com LINK
|
|
| |
| | | Tom Wickerath | |
| | Posted: Sun Aug 31, 2008 10:11 pm Post subject: RE: Error Msg if No C Drive | |
| Quote: | You're apparently unaware of the fact that the Vista operating system no
longer allows one to write to this folder programatically, using VBA code
within Access.
|
Clarification: And have this temp. folder treated as a trusted location by
Access.
Tom Wickerath
Microsoft Access MVP
LINK
LINK |
| |
| | | Bob Vance | |
| | Posted: Sun Aug 31, 2008 10:46 pm Post subject: Re: Error Msg if No C Drive | |
This is what I created seems to be OK! .......Thanks Bob
Private Sub cmdInvoices3M_Click()
On Error GoTo PROC_Error
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryInvoiceReport3M", "C:\temp\Invoices_3_Months.xls"
PROC_Exit:
Exit Sub
PROC_Error:
MsgBox "There Should be a temp File in C Drive, C:\temp "
Resume PROC_Exit:
End Sub
"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:AB51DCC7-C3F6-415D-8A2E-DE51085BD5D5@microsoft.com...
| Quote: | You're apparently unaware of the fact that the Vista operating system no
longer allows one to write to this folder programatically, using VBA code
within Access.
Clarification: And have this temp. folder treated as a trusted location by
Access.
Tom Wickerath
Microsoft Access MVP
LINK
LINK |
|
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
