using GetDrive method and FileSystemObject

[cmmrfrds]

I am trying to use the GetDrive method to see if I have a network drive attached. Access doesn't seem to recognize the GetDrive method. Do I need any particular reference libraries checked?

Jerry

 

[GolfNut]

You need to include "Microsoft Scripting Runtime" in your References.

 

[MoGryph]

That probably worked for you, but you might like to check this out too...

Declare Function GetLogicalDriveStrings Lib "kernel32.dll" Alias "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long
Declare Function GetDriveType Lib "kernel32.dll" Alias _
"GetDriveTypeA" (ByVal nDrive As String) As Long

Const DRIVE_CDROM = 5 ' CD-ROM drive.
Const DRIVE_FIXED = 3 ' Local hard drive
Const DRIVE_RAMDISK = 6 'A RAM disk.
Const DRIVE_REMOTE = 4 ' Network Drive
Const DRIVE_REMOVABLE = 2 ' Floppy disk, or other removable media

Sub EnumDrives()
Dim AllDrives As String
Dim DriveStrLen As Long
Dim SpecificDrive As String
Dim c As Long
Dim DriveType As Long
AllDrives = Space(255)
DriveStrLen = GetLogicalDriveStrings(255, AllDrives)
For c = 1 To DriveStrLen Step 4
SpecificDrive = Mid(AllDrives, c, 3)
Select Case GetDriveType(SpecificDrive)
Case DRIVE_CDROM
Debug.Print SpecificDrive & " is a CD-ROM"
Case DRIVE_FIXED
Debug.Print SpecificDrive & " is a Local (Fixed) Drive"
Case DRIVE_RAMDISK
Debug.Print SpecificDrive & " is a Virtual Drive (Ram Disk)"
Case DRIVE_REMOTE
Debug.Print SpecificDrive & " is a Network Drive"
Case DRIVE_REMOVABLE
Debug.Print SpecificDrive & " is a Removable Drive"
End Select
Next c
End Sub

 

[cmmrfrds]

Thank you, putting in the scripting reference worked. What I am trying to do is find an easy way to determine whether the PC is connected to the LAN. If it is not on the LAN, I start a Access project that is connected to the local MSDE server. If it is on the LAN, I start a project that is connected to Sql Server. The next thing I need to test is replication between Sql Server and the local MSDE server since they use the same database. Any experience with replication since I could use some pointers especially with the local MSDE server subscription to the Sql Server publication..

Jerry

 

[MoGryph]

This might not be elegant, but- I'd try to query the SQL server, and if I get an error, telling me that I can't connect, then I'd assume (whether I was connected to the LAN or not) that I just can't connect to the SQL Server (it's a given). Knowing that, I'd go on to connecting to the local MSDE server.

As for replicating, sorry, can't help you there (yet).
I'm taking the System Administration for MS SQL Server 7.0 right now, and tomorrow (12/01) is the last day.
We'll be going over Replication, and I'll be sure to pay CLOSE attention (especially, since I'm planning on taking the exam before the end of the year, but also, for your benefeit too.) There IS a section on "Creating a Merge Publication", which I believe is your case. I'm just too tired tonight to read ahead. hehe. I'll try to get back to you sometime this weekend.

What version of SQL Server are you using BTW?

 

[jeronimo1]

Thanks MoGryph. I have a copy of Sql Server 7.0 on the server and MSDE on the client (windows 98) from the Office 2000 Premium CD. On checking for the server, I've tried to connect to the server in my program through ADO and checking for the network drive. The key is I need the fastest response from the server since much of the time the user will be disconnected and I don't want to have a big wait time every time they get into the application. I now have some options on determining the connection, but I need to finish my proof of concept on the replication.

I think, I may have run into a licensing issue. On the server, when I try to register my client (msde server) it does not see the sql server on the pc. I have read that I will need CAL (client access license) for msde. Does this keep the server from seeing the client, or is this another issue??? I also read, that desktop edition of sql does not need separate licensing and maybe could replace msde on the client?? I just don't know these answers now.

I am under the gun to get this proof of concept done on replication, since I believe it is the way to go. Until I get it working I don't know what the pitfalls are going to be.

Any help is always appreciated.
Jerry