Change of Drives - Module

[dr223]

Hallo,

I am working on an old developed Access database. One of the changes I have to make is to change the export from drive a: (disk) to drive d: (CD).

I noticed that on the Export click button it calls the following module (see below). I have tried to change to d: drive by changing the a: - d:. However, when I run the application and click export nothing happens. No error given and no export takes place. Change it back to a: it works fine for the diskette.

Please could anyone tell me what should I change apart from a: - d: in the module below to make the export possible.

Thank you very much

Sub CrtSpSheet()
On Error GoTo cmdSheetERR
Dim strNME As String
Dim Ans As Byte
    ChDir "a:\"
    Select Case SpSheetType
    Case 1
        strNME = "a:\" & SEAGProtocol & Left(ProjectDesc, 10) & "_Patients.xls"
    Case 2
        strNME = "a:\" & SEAGProtocol & Left(ProjectDesc, 10) & "_Practices.xls"
    End Select
    
    Beep
    Ans = MsgBox("DO you wish to continue? Please note the reference of the spreadsheet to be created - " & strNME, vbYesNo, "Export")
        If Ans = 6 Then
        Select Case SpSheetType
        Case 1
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryPatientInfoDUMP", strNME, True
        Case 2
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryPracticeInfoDUMP", strNME, True
        End Select
        Beep
        MsgBox "Export Complete"
        End If
    Exit Sub
    
cmdSheetERR:
    Beep
    DoCmd.CancelEvent
    Select Case Err.Number
    Case 3010
        MsgBox "The file - " & strNME & " - is open, please close it", vbCritical, "File Open"
    Case 75
        MsgBox "Please insert a disk into your 'a:\' drive"
    
    End Select
        
        
End Sub

 

[namliam]

Appart from the GLARING problems of defining your variables and formatting your code, I cannot see why changing A: to D: would not work.

 

[gemma-the-husky]

its very unlikely you can write directly to a CD. You normally have to burn files to CD with special burners. You would be able to use a memory stick.

It may be possible with CD drives formatted for direct write (or whatever its called), but i'm not certain.