Connection Not Establishing in VB6.0

Hi All,
I'm doing my project in visual basic 6.0 and sql server 2014.
Everything was working fine,but suddenly it started showing that connection coulld not be established.Now I'm getting eror like Run time error 3704.Operation is not allowed when the object is Closed.

Here is my code:
Form_load
--------

Code:

---

Private Sub Form_Load()

Connection
Cn.Execute "IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ServiceAdvisor' AND type = 'U') drop table ServiceAdvisor", adExecuteNoRecords
Cn.Execute "select code2 ServiceAdvisorId, Name ServiceAdvisorName into ServiceAdvisor from mr006 where L2 = (select masterid from mr006 where name='Service Advisor')", adExecuteNoRecords
CloseConnection
End Sub

---

Code:

---

Public Sub Connection()
    If Cn.State = 1 Then Cn.Close
    Cn.Open "Driver={SQL Server};Server=SQLSERVER2014;Database=C010;Uid=sa;Pwd=MyPwd"
    Cn.Execute "set dateformat dmy"
    Cn.CommandTimeout = 0
    Exit Sub
End Sub

---

Am getting error on Cn.Open statement.
Can anyone help me?

Thanks
Mary

Have you changed the connection string? Is the machine where the server resides named sqlserver2014?

You should not use a sub named Connection as that is one of the methods of the ado object.
Why are you closing the connection if it is open and then opening it again? Would be better to open it only if it is not already open.

The error you mention should not be occurring on the cn.open line instead you should see that on one of the execute lines or on a recordset.open statement.

I'm keep on getting the following error:
Runtime Error -2147467259 (80004005)
Specified driver could not be loaded due to system error 126 (Sql server)
I reinstalled sql 2014 ,Still am getting the same error..
Using DSN I can connect,but when I use SQL server as driver in connection string,am getting this error.
Can anyone help me?

It would help if you answered the questions asked in post #2

You said it was working, which means that either you were using a DSN or that you had the correct connection string. You did not say what you changed.
You also gave a different error in your first post than the most recent one.

Which of these is your actual problem and what changed between it working and not working?
Is SQL server that you are trying to connect to on the local PC?
What is the name of the PC your are trying to connect to?

Have you tried other connection strings?

Quote:

---

Originally Posted by DataMiser

It would help if you answered the questions asked in post #2

You said it was working, which means that either you were using a DSN or that you had the correct connection string. You did not say what you changed.
You also gave a different error in your first post than the most recent one.

Which of these is your actual problem and what changed between it working and not working?
Is SQL server that you are trying to connect to on the local PC?
What is the name of the PC your are trying to connect to?

Have you tried other connection strings?

---

I didnt changed anything .Suddenly it happened.It shows the error as "Specified driver could not be loaded due to system error 126."
After that error the following error is showing :"Run time error 3704 ".I debugged and its throwing in Connection.open statement.
SQL and the project are on the same machine.If I try the same project in other systems,it is working fine.So I dont think its something related to code.Registry values under ODBC ,the sql server path is pointing to C:\Windows\System32 only.
I dont have any idea why it is showing again and again?Please help..
NB:If i use DSN connection(Cn.ConnectionString = "PROVIDER=MSDASQL;dsn=MyDSNCon;uid=" & MyUid & ";pwd=" & MyPwd & "") instead of
Cn.ConnectionString = "DRIVER={Sql Server}; SERVER=SQLSERVER2014;Database=C010;Uid=" & MyUid & ";pwd=" & MyPwd & "")
its working fine.

Quote:

---

Originally Posted by jamuna

I didnt changed anything .Suddenly it happened.It shows the error as "Specified driver could not be loaded due to system error 126."
After that error the following error is showing :"Run time error 3704 ".I debugged and its throwing in Connection.open statement.
SQL and the project are on the same machine.If I try the same project in other systems,it is working fine.So I dont think its something related to code.Registry values under ODBC ,the sql server path is pointing to C:\Windows\System32 only.
I dont have any idea why it is showing again and again?Please help..
NB:If i use DSN connection(Cn.ConnectionString = "PROVIDER=MSDASQL;dsn=MyDSNCon;uid=" & MyUid & ";pwd=" & MyPwd & "") instead of
Cn.ConnectionString = "DRIVER={Sql Server}; SERVER=SQLSERVER2014;Database=C010;Uid=" & MyUid & ";pwd=" & MyPwd & "")
its working fine.

---

Got it.Under HKEY_LOCAL_USERS-->WOW6432Node-->ODBC-->SQL Server path was wrong.I changed it to C:\Windows\System32 and it started working.