Verify ADO Connection String / Populate combo-box with filename data.

[sanders720]

How can I verify if the ADO connection string does not occur? Currently, the application uses the job number from a combo box to define the file it will read. It is entirely possible that a file does not exist.

1. I would like to verify the connection, and provide an error message if the file does not exist (connection string does not occur.

2. Ideally, I'd like to populate the combo boxes in accordance with the file names for databases that exist rather than active job numbers.

Thankyou in advance for any help you can provide in getting me started with this.


Private Sub cmdGetData_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim connString As String
Dim sql As String
Dim C As Integer
Dim R As Integer

connString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\deepblue\EngineeringBOM\VIA-WD User Files\" & cboJobNo.Value & ".mdb;" & _
"Persist Security Info=False"

MsgBox connString

Set cn = New ADODB.Connection
cn.Open connString

sql = "SELECT COMP.CAT, COMP.DESC1, COMP.DESC2, COMP.MFG, COMP.LOC FROM [COMP] WHERE (((COMP.CAT) Is Not Null) AND ((COMP.LOC) = '" & Me.cboSubAssy.Value & "'))"
Debug.Print sql

Set rs = New ADODB.Connection
rs.Open sql, cn, adOpenStatic, adLockReadOnly

rs.MoveLast
R = rs.RecordCount
rs.MoveFirst
For C = 1 To R
Debug.Print rs.Fields("CAT&quot
rs.MoveNext
Next C

' While Not rs.EOF
' Debug.Print rs.Fields("CAT&quot
' rs.MoveNext
' Wend

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

MsgBox ("Program Completed Successfully...&quot

 

[sanders720]

updated 11/13/2002

 

[sanders720]

move to top - 11/15/2002