First, you're using some bad programming techniques that will lead to some overhead in the Code. Anytime you Declare a Variable with the "...As New" and it's an Object, then every time you set a Property or use a Method, the VB Compiler ALWAYS checks to see if the Object has been instantiated...this is some significant overhead if you use this routine a lot. Actually, it looks like the whole Connection String is wrong... The Provider "MSDASQL" is strictly used for DataSources that reside in the ODBC DataSources. If this is the Case, and you do not have a User / Password, then leave those Parameters out. Also, do not put a SemiColon at the end of the Connection String. Also, why did you make an "ADODB.Command" Object if you never used it?? NOTE: Also, you better learn the difference between Dynamic, ForwardOnly, Static, and Keyset cursors before you program any further, you are not making the most efficient use of resources...
Dim adoCon As ADODB.Connection
Dim rsData As ADODB.Recordset
'Open the Connection
Set adoCon = New ADODB.Connection
'objcnn1.Open "Provider=MSDASQL.1;password =;User Id = ;Data Source=TEST;" (Wrong)
adoCon.Open "Provider=MSDASQL.1;Data Source=TEST"
'Open the Recordset
'Open ForwardOnly / ReadOnly because we are only Enumerating the Records (maybe filling a ComboBox)
Set rsData = New ADODB.Recordset
rsData.CursorLocation = adUseClient
rsData.Open "Select * From Test", adoCon, adOpenForwardOnly, adLockReadOnly, adCmdText
While (Not rsData.EOF)
MsgBox " " & rsData.Fields(0).Value 'sDownload (Huh?)
'MsgBox " " & rsData.Fields("FieldName").Value
'MsgBox " " & rsData!FieldName
'MsgBox " " & CStr(rsData!StringField)
'MsgBox " " & CInt(rsData!NumberField)
rsData.MoveNext
Wend