Can there be a way of connecting a vb 6.0 application to SQL server, how possible is that if am using ADODB?

Yes it's possible.
Assuming that you have a module, in the module declare a connection object. You can either use the SQL Native Provider or use the OLE DB database Provider.
An example. The server name is Tana and I have a database named WaterPro. we will be connecting to the database using a user named Manager whose password is LetItGoN0w.

    strUserName = "Manager"
    strPassword = "LetItGoN0w"

    Set cnxWaterPro = New ADODB.Connection
    cnxWaterPro.CommandTimeout = 0
    cnxWaterPro.ConnectionString = " PROVIDER=SQLOLEDB" & _
        ";SERVER=Tana" & _
        ";UID=" & strUserName & _
        ";PWD=" & strPassword & _

Above example uses the SQL OLE DB provider. To make it use the native provider change the Provider directive to be SQLNATIVE. If using SQL Server 2008 then that will be Provider=SQLNCLI10.1

Yes, you can. All the connection codes needed for your specific MS Sql can be found HERE, using ADODB.