Save query on the local drive before outputting .

**NeoPa** · Feb 3 '12, 12:56 PM

This is a Windows question really Mandy, as ChDir doesn't handle UNCs. Type ChDir /? on the command line to see the full spec (and you'll understand what I mean).

**Rabbit** · Feb 3 '12, 04:53 PM

The solution is to map a network drive and use that.

**NeoPa** · Feb 3 '12, 05:02 PM

Indeed. If that needs to be done in code then you will need to use the NET USE command from a Shell() function call.

If this is unfamiliar to you then let us know. To assist more specifically we would need to know the drive letter you intend to use. We already have the server and share required from your code. I would assume, from the existing code, that the requisite rights are already held by the accounts of the people intending to run this project for the shares to be used. If not then you'll have problems.

If this proves to be required then I'll move the thread across to Windows for you.

**ADezii** · Feb 3 '12, 11:18 PM

I created some Code for you that should be a work around the UNC restriction. It has been tested and is fully operational, given the considerations in NeoPa's Post. It will:

Dynamically Map two Drive letters (W: and X:) to UNC Paths.
Output the same Report (rptPayroll) to these Paths.
Disconnect both Mapped Drives.
Any questions, feel free to ask.

Code:

Dim lngProcessID As Long

lngProcessID = Shell(Environ$("Comspec") & " /c" & " Net Use W:" & _
               " \\rpacap01\ungerboeck\Finance", vbHide)
  DoCmd.OutputTo acOutputReport, "rptPayroll", acFormatXLS, "W:\Payroll.xls", False
  
  
lngProcessID = Shell(Environ$("Comspec") & " /c" & " Net Use X:" & _
               " \\rpacgc01\Public", vbHide)
  DoCmd.OutputTo acOutputReport, "rptPayroll", acFormatXLS, "X:\Payroll.xls", False
  
  
'Disconnect both Drives (W: and X:)
lngProcessID = Shell(Environ$("Comspec") & " /c" & " Net Use W: /delete", vbHide)
lngProcessID = Shell(Environ$("Comspec") & " /c" & " Net Use X: /delete", vbHide)

**NeoPa** · Feb 4 '12, 01:52 PM

@ADezii - NET.EXE is a file, so doesn't need to be invoked from the command line. As such can be done as :

Code:

Call Shell("Net Use X: \\rpacgc01\Public", vbHide)

**ADezii** · Feb 4 '12, 03:11 PM

@NeoPa:
How in the world did I miss that one! (LOL).
Thanks.

**NeoPa** · Feb 4 '12, 03:14 PM

It's good example code for using the inbuilt commands like REN and MD etc etc though (LOL), which are difficult for a lot of people. Nice code too ;-)

**Mandy Medcraft** · Feb 5 '12, 10:34 AM

Hi

Thanks for your help. Fairly new to forums, so hoping all that contributed can see this reply?

I probably should have given more history, but didn't want to bore! I have several Branches on each server \\NPFS3,5,6 and Head Office on \\NPFS2. We have a P:\\ drive mapped from each server to NPFS2\\Payroll, this is the default directory for the application (I'm still using 2003 because of the custom toolbar issue). We have been using the process of just outputting batches to mapped P:\\ for years without too many problems. However since the new network structure has been put in place the mapped drives from each Server to P:\\ have become more unreliable.

I thought it would be useful to Outputto Query NP_WKB to C:\Branches\Bra nchName\CopyPR before outputting to P:\\Payroll so that if there was a problem with the export, the payroll staff could simply retrieve the missing file from the relevant Branch folder. Outputto, Output File 'blank' works well because we need a unique name and reference for each .xls file. The NP_WKB acts as an input guide for the name _ being Branch Prefix, WK being week number and B being Batch, so a well named file (and they don't always come like this) would be NPB42A.xls.

So
a) I wanted to change the 'default drive and folder' temporarily because I can't rely on the Users to select the correct destination themselves, and send a copy of NP_WKB to C:\Branches\Bra nchName\CopyPR

b) I thought by using the UNC path \\NPFS2\Payroll it would not matter if the mapped drive to P:\Payroll had dropped?

Thanks very much for the code to dynamically map a drive, that could well be the obivous solution to use this to just ensure the mapping is in place before they output anything.

In the meantime I have got this to work, by adding the Path as a field "SavePR" in the Offices Table and then calling this field to use as the file destination. And I'm now confused because the UNC path in my code appears to be working fine, I know it's not a very sophisticated piece of code to you experts... but this is what I've ended up with and would be grateful if anyone can see any potential disasters.

Code:

Function PAYROLLREP()
On Error GoTo PAYROLLREP_Err
Dim RS As Recordset
Dim SavePR As Variant
Dim MyPath As String
Set DB = CurrentDb()

    Call SetPortrait("PAYROLL REPORT", False)
    DoCmd.CLOSE acReport, "PAYROLL REPORT"
    DoCmd.Hourglass True
    Set RS = DB.OpenRecordset("select [SavePR] from offices where [Branch ID]=-1") 'Selects the temporary destination path
    If (RS.RecordCount = 0) Then
            MsgBox "Unknown Office Code", vbCritical, "Save Copy Payroll Routine"
  Else
    MyPath = RS("SavePR")
    ChDir MyPath
  End If
    
    If (MsgBox("First, save a copy of the Payroll Batch to the local Drive in CopyPR", vbOKCancel, "Payroll Copy") = vbOK) Then
    DoCmd.OutputTo acQuery, "NP_WKB", "MicrosoftExcel(*.xls)", "", False, "" 'Outputs to MyPath as C:\Branches\BranchOffice\CopyPR
  Else
    DoCmd.CancelEvent
  End If
    ChDir "\\NPFS2\PAYROLL" 'Revert to default folder
    If (MsgBox("Now send the Payroll Batch to Payroll", vbOKCancel, "Payroll Copy") = vbOK) Then
    DoCmd.OutputTo acQuery, "NP_WKB", "MicrosoftExcel(*.xls)", "", False, "" 'Outputs to HO
  Else
    DoCmd.CancelEvent
  End If
    DoCmd.RunMacro "REC TO TRAN", , ""
    DoCmd.OutputTo acReport, "PAYROLL REPORT", "RichTextFormat(*.rtf)", "", False, "", 0  'Outputs to HO
    DoCmd.RunMacro "Plus Points Update Macro", , ""
    DoCmd.OpenReport "PAYROLL REPORT", acViewPreview, "", ""
    DoCmd.Hourglass False
    DoCmd.CLOSE acForm, "Process Payroll Warning"
    DoCmd.SetWarnings True


PAYROLLREP_Exit:
    Exit Function

PAYROLLREP_Err:
    MsgBox Error$
    Resume PAYROLLREP_Exit

End Function

**NeoPa** · Feb 5 '12, 03:04 PM

Originally posted by Mandy

Mandy:
Thanks for your help. Fairly new to forums, so hoping all that contributed can see this reply?

All that contribute to a thread will be able to see that it's been replied to if they look in their Subscriptions folder. Any that have their account settings set to enable it, will also receive an email notification of the post.

Originally posted by Mandy

Mandy:
I probably should have given more history, but didn't want to bore!

More information rather than less is usually preferable, but we all learn this from experience ;-)

Originally posted by Mandy

Mandy:
... if anyone can see any potential disasters.

The first thing to draw your attention to is Before Posting (VBA or SQL) Code. The Option Explicit point is well worth your while and pertains to your use of an undeclared variable DB.
Whenever using Database and/or Recordset type variables, it's always a good idea to disambiguate them. There are two types - DAO and ADODB. Unless foreign (Non-Jet/Access) datasets are being used I would recommend use of DAO. So, it should start with :
Code:
```
Dim DB As DAO.Database
Dim RS As DAO.Recordset
```
Line #4 - Using Variant type variables should only be considered if none of the basic types is appropriate for any reason. I don't see that you'd need it for SavePR.
## Actually, SavePR is never even used so can (and should) go. ##
Line #12 - Checking for an empty recordset is better done by checking for .EOF once it's opened. .RecordCount will work in most situations, but in some situations it won't be set immediately and will give a false-positive. Check Help for details of when and why.
Lines #22 & #28 - I doubt this makes sense. Which event are you expecting to cancel if the OutputTo() call is bypassed anyway?
Line #24 - This won't work as discussed earlier in the thread.
As a general piece of advice, if you're after a project that's properly designed and flexibly controlled with code, including macros is not a good idea. Macros are fundamentally clumsy, limited, and hard to share with others. In this case they leave gaps in the routine that we cannot follow.

**Mandy Medcraft** · Feb 8 '12, 09:00 AM

Thanks NeoPa

I'm very new to writing code as you've probably realised, is this looking better? The only thing in your advise that I can't get my head round is your comment on line #24 re ChDir, when I remove line ~24 the following 2 outputs go to folder\CopyPR called by the previous ChDir and not \\NPFS2\Payroll ? If I leave it in it works ok, is there something else going on here which is forcing it to revert to the default directory, I can't think of a way of testing that without leaving it in and temporarily changing the default folder then running the code to see what actually happens... best done at a weekend when there are not so many files moving around.

Code:

Option Compare Database
Option Explicit

'------------------------------------------------------------
' PRINT_PAYROLL_REP2
'------------------------------------------------------------

Function PAYROLLREP()
On Error GoTo PAYROLLREP_Err
Dim RS As DAO.Recordset
Dim Db As DAO.Database
Dim MyPath As String
Set Db = CurrentDb()

    Call SetPortrait("PAYROLL REPORT", False)
    DoCmd.CLOSE acReport, "PAYROLL REPORT"
    DoCmd.Hourglass False
    Set RS = Db.OpenRecordset("select [SavePR] from offices where [Branch ID]=-1") 'Selects the temporary destination path

    If RS.EOF Then
        MsgBox "Unknown Office Code, Contact System Administrator", vbCritical, "Save Copy Payroll Routine"
        Exit Function
    Else
        MyPath = RS("SavePR")
        ChDir MyPath
    End If
        MsgBox "First, save a copy of the Payroll Batch to the local Drive in CopyPR", vbOKCancel, "Payroll Copy"
    DoCmd.OutputTo acQuery, "NP_WKB", "MicrosoftExcel(*.xls)", "", False, ""   'Outputs to MyPath as C:\Branches\BranchOffice\CopyPR
        MsgBox "Now send the Payroll Batch to Payroll", vbOKCancel, "Payroll Copy"
    ChDir "\\NPFS2\PAYROLL"
    DoCmd.OutputTo acQuery, "NP_WKB", "MicrosoftExcel(*.xls)", "", False, ""  'Outputs to HO
               ' Delete "Plus Points>1 Year and update current week Plus Points earned"
    DoCmd.OpenQuery "PP DELETE>1 YEAR", acViewNormal, acEdit
    DoCmd.OpenQuery "PP APPEND THIS WEEK", acViewNormal, acEdit
                ' Updates status of booking from REC to TRAN, record disabled
    DoCmd.OpenQuery "REC TO TRAN", acViewNormal, acEdit
                ' Sends copy of payroll report to HO
    DoCmd.OutputTo acReport, "PAYROLL REPORT", "RichTextFormat(*.rtf)", "", False, "", 0  'Outputs to HO
                'Open Payroll Report Preview
    DoCmd.OpenReport "PAYROLL REPORT", acViewPreview, "", ""
    DoCmd.Hourglass False
    DoCmd.CLOSE acForm, "Process Payroll Warning"
    DoCmd.SetWarnings True
        If (MsgBox("Did the Payroll Report Export properly? If you answer YES Timesheets status will update to SENT", vbYesNo, "Commit Payroll Report") = vbYes) Then
            ' Update Timesheet Status from TRAN to SENT
            DoCmd.OpenQuery "TRAN TO SENT", acViewNormal, acEdit
            DoCmd.CLOSE acForm, "Payroll Invoicing"
        Else
        MsgBox "Check for valid Path in TblOffices and try again!", vbOKOnly, ""
        Exit Function
        End If
        
PAYROLLREP_Exit:
    Exit Function

PAYROLLREP_Err:
    MsgBox Error$
    Resume PAYROLLREP_Exit

End Function

Ever grateful for feedback. I am ashamed to say I have a database full of 'macros' but I will endeavour to rectify, maybe I should just 'surrender' and hand it over to an expert?

Thanks again
M

**NeoPa** · Feb 8 '12, 08:30 PM

In my system Mandy, and I'm also using 2003 but from XP, ChDir with a UNC parameter executes without any error message, but has no effect whatsoever on the value of CurDir (IE. It doesn't actually do anything). If this is different for you then good for you. From what you describe it sounds that way.

Comments on code :

I'm guessing you have declared something, somewhere, as CLOSE (All upper-case). This would explain why line #16 has DoCmd.CLOSE. Not a drastic problem, but worth avoiding if convenient.

RS and Db are only used to get the [SavePR] value for MyPath. A more straightforward approach would be simply (To replace your lines #18 to #26 - Lines #10 to #13 could also be replaced with the single current line #12.) :

Code:

    MyPath = Nz(DLookup("[SavePR]", "[Offices]", "[Branch ID]=-1"), "None")
    If MyPath = "None" Then
        MsgBox "Unknown Office Code, Contact System Administrator", vbCritical, "Save Copy Payroll Routine"
        Exit Function
    End If
    ChDir MyPath

I notice line #18 refers to a table called [offices], yet the text in line #49 indicates the table may be named [TblOffices] instead. I'll leave that with you to sort out.

Everything else is either ok or flew under my radar.

Originally posted by Mandy

Mandy:
I am ashamed to say I have a database full of 'macros' but I will endeavour to rectify, maybe I should just 'surrender' and hand it over to an expert?

We all have to start somewhere. If you're serious about this idea then I also work as a consultant and can be approached to discuss.

**Mandy Medcraft** · Feb 9 '12, 06:35 PM

Thanks, I swapped that piece of code and disposed of lines 10,11,13and all's working well. Also sorted the incorrect text reference to TblOffices, but can't find where on earth I have something declared as CLOSE? I noticed that's returning uppercase when selected, I'll keep searching.

On the other matter, yes would really like to discuss... how do I go about that?

**NeoPa** · Feb 9 '12, 07:11 PM

There is an option in find to search the whole project, which should help with that ;-)

Originally posted by Mandy

Mandy:
On the other matter, yes would really like to discuss... how do I go about that?

If you send me a Private Message (Send a private message to NeoPa) then we can swap some contact details. Anything you can tell me about what you are looking for in the PM would be helpful, but we can go into more detail once contact is made.

I'm out tomorrow, so I may not get back to you immediately, but if I miss it tomorrow I'll respond on Saturday at least.