We moved our VB6 application's database to another server and changed the connection string of our application to point to it. The following is the connection string and it does not work. It gives the error "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
Provider=SQLOLEDB.1;Password=ppppppp;Persist Security Info=True;User ID=uuuuuuu;Initial Catalog=AppDB;Data Source=10.10.10.10\NEWMSSQL
It seems our application has a problem with the named instance NEWMSSQL because when I try with just Data Source=10.10.10.10, it works, but that's not where the database resides so I can't use that. I thought the provider SQLOLEDB.1 is unable to recognize the named instance so I tried changing the provider to SQLNCLI.1, changed the connection string to the following and it works on my development machine.
Provider=SQLNCLI.1;Password=ppppppp;Persist Security Info=True;User ID=uuuuuuu;Initial Catalog=AppDB;Data Source=10.10.10.10\NEWMSSQL
But when I deploy the application to a user's machine, it gives the error "Provider cannot be found. It may not be properly installed."
Could anyone please shed light on what's wrong and help me fix this problem, please?
Thank you so much in advance for your much appreciated help!