Using Access to understand external devices

[smuthcrmnl777]

When I plug my mp3 player or my memory stick into the usb port there is a specific drive letter assigned to the port. Is there a way for Access to understand which drive letter the external device has been plugged into. I do not want to limit the user to only plug the device into a certain port every time he wishes to view files.

 

[TomCologne]

The sub below works with an external HDD (USB). I don't have a flash drive but I guess you should be able to retrieve a serial no for those, too.

Let us know,

TomCologne

[COLOR=green]
'' ***************************************************************************
'' List of all drives on system 
''
'' Drive types "Removable" and "CD-ROM" reduced info
'' ---------------------------------------------------
'' References:
''
'' Microsoft Scripting Runtime
'' ***************************************************************************[/color green]
Sub DriveList()
On Err GoTo Err_DriveList

Dim fs As Scripting.FileSystemObject, dc As Scripting.Drives, d As Scripting.Drive
Dim strDrType As String, strDrName As String, strDrLetter As String, strDrFileSystem As String
Dim dblTotalSize As Double, dblAvailSpace As Double, dblFreeSpace As Double
Dim lngDrSerialNo As Long, lngDrType As Long    

Set fs = CreateObject("Scripting.FileSystemObject")
Set dc = fs.Drives
    
            For Each d In dc
            
            lngDrType = d.DriveType
            strDrLetter = d.DriveLetter

                        Select Case d.DriveType '
                        
                                Case 0
                                strDrType = "Unknown"
                                strDrName = d.VolumeName
                                
                                Case 1
                                strDrType = "Removable" 'Floppy
                                strDrLetter = d.DriveLetter
                                
                                Case 2
                                strDrType = "Fixed"
                                dblTotalSize = d.TotalSize
                                lngDrSerialNo = d.SerialNumber
                                strDrName = d.VolumeName
                                strDrFileSystem = d.FileSystem
                                dblAvailSpace = d.AvailableSpace
                                dblFreeSpace = d.FreeSpace
                                blnDrSrch = -1
                                
                                Case 3
                                strDrType = "Network"
                                dblTotalSize = d.TotalSize
                                lngDrSerialNo = d.SerialNumber
                                strDrName = d.ShareName
                                strDrFileSystem = d.FileSystem
                                
                                Case 4
                                strDrType = "CD-ROM"
                                dblTotalSize = 0
                                lngDrSerialNo = 0
                                strDrName = ""
                                strDrFileSystem = ""
                                
                                Case 5
                                strDrType = "RAM Disk"
                                dblTotalSize = d.TotalSize
                                lngDrSerialNo = d.SerialNumber
                                strDrName = d.VolumeName
                                strDrFileSystem = d.FileSystem
                            
                        End Select
			
		     Debug.Print strDrLetter, lngDrType, lngDrSerialNo


            Next
    
    
Exit_DriveList:
	
    Set dc = Nothing
    Set fs = Nothing
    
    Exit Sub
    
Err_DriveList:

    Debug.Print Err.Number, Err.Description
    Resume Next
    
End Sub

 

[smuthcrmnl777]

This works great. How can I get it to Insert into a table? Also, id there a way to have the volumen name inserted, too?

 

[TomCologne]

You can use the code here as an example:

Email subjectline to access tables
thread705-1047885

The "rst" parts are basically the same.


Volume name is under "Case 2" in the code above.

Set the "Allow zero length" property of the table field to 'Yes' since a volume name may not exist!

TomCologne

 

[smuthcrmnl777]

I am getting an error "Path not found on this...

Case 3
strDrType = "Network"
[COLOR=red]dblTotalSize = d.TotalSize[/color]
lngDrSerialNo = d.SerialNumber
strDrName = d.ShareName
strDrFileSystem = d.FileSystem

I am still playing around with the first code you sent. I have this at work now and I get this error. At home it works fine. It is able to place a serial number for a flash disk. I just need to do this at work.

Thanks!!