Connection

[Anonymous]

What Am I doing wrong? I am getting the message object is close Dim objcnn1 As New ADODB.Connection Dim objcmd1 As New ADODB.Command Dim objrst1 As New ADODB.Recordset Dim str As String Dim GConnection As String objcnn1.Open "Provider=MSDASQL.1;password =;User Id = ;Data Source=TEST;" objcmd1.ActiveConnection = objcnn1 With objrst1 .ActiveConnection = objcnn1 .CursorType = adOpenDynamic End With sDownload = "SELECT * FROM test " objrst1.Open sDownload Do While Not objrst1.EOF MsgBox " " & sDownload objrst1.MoveNext Loop

[Lost User]

:cool: as such you are using the MSDASQL provider u r here not specifying the path of the source :-O i think that u should specify the path of the source else wise he will take the application path also u r using the MSDASQL provider then i think that you have to login the SQL server to access the database i m not sure abt it :~ check whether the objcnn1 is open or not and get the error from err.number first do the connection and then do the rest of the things with that object :laugh: :cool: VickyMD A Specialist in Message Digest Security

[mikasa]

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