Test if the database connection path really exists

[xinyin]

Hi all,
I have a common dialog allowing the user to choose which access database (.mdb) to connect to. After a database file is selected the program will update the current connection by ADO:

Set Connecting = New ADODB.Connection
Connecting.Provider = "Microsoft.Jet.OLEDB.4.0"
Connecting.ConnectionString = "Data Source=" & DatabasePath
Connecting.Open

Sometimes the user may type the database name in the common dialog instead of picking by mouse, but if they spell wrong, the connection fails and the program crashs. So I do this coding to test if the database exists:

Private Sub Connecting_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
If adStatus = adStatusErrorsOccurred Then
MsgBox "Cannot connect this database.", vbOKOnly + vbCritical
(Reopen the common dialog for the user to choose a correct database)
......
ElseIf adStatus = adStatusOK Then
MsgBox "Connection success.", vbOKOnly
......
End If
End Sub

I wonder if this is an "official" way to test the existence of a database. The flaw of this design is no matter the database path is working or not the connectionstring is changed/ruined (Since this is "ConnectComplete"). I want to know if ADO can just "test" the path but not doing any actual connection (tried using "willconnect" but it has no response). Any ideas?

 

[dglienna]

Just use the Dir() command to see if the file exists.

If Dir("c:\temp\mydb.mdb") <> "" then
msgbox ("File exists.")
else
msgbx ("Select location again")
endif

David

 

[strongm]

Alternatively, set the cdlOFNFileMustExist flag for the common dialog. That way they can never choose (even if they type) a file that doen't exist. Of course, this doesn't stop them from choosing a file that isn't a JET database ...

 

[mattKnight]

or choosing a valid database and then deleting it



Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[xinyin]

Hi all,
I tried the Dir() command. It is better than "connectcomplete" since it won't affect the connection string. The only thing I have to beware is if the database path is "" then it will pass the dir() command and cause error. But this problem can be filtered easily by puting a 'if <connection string> = "" then exit sub' before doing Dir().

The "cdlOFNFileMustExist" sounds very useful. But I don't find this in the common dialog's property window. Also I tried doing <commondialog>.cdlOFNFileMustExist = true in the coding but there was error. Please let me know more about its syntax. Thank you.

 

[johnwm]

You're looking for the Flags Property of the CommonDialog Control. The enumerations are listed in VBHelp.

cdl1.Flags = cdlOFNFileMustExist

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[strongm]

Tum ti tum ;-)

 

[johnwm]

...sometimes a [F1] is indicated

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts