Hi,
I have a SQL Server Express 2005 installation and database on my PC. I want to connect to it from VB6 using DAO (i have to use DAO as the software i'm using is already written in it and i've just upgraded the database from Access to SQL SERVER that it connects to.)

I've tried a few connection variations but keep getting error that the "ODBC CALL FAILED"

I have set up an ODBC DSN also to try and connect through that, but it doesnt matter if the connection is ODBC or just directly to the Server, I just need to get it running.

SQL Server name: .\SQLEXPRESS
SQL Database name: workwise
Username: peter
Password:
ODBC datasource name: workwise

Any help on the connection string would be greatly appreciated.

@@@@@ This is what I've tried so far. @@@@@@

I went to the connectionstrings.com website but I still couldnt get much further with my connection.

I've intentionally left the code so that if the login fails it will show the login box. This gives me a chance to try different connection variables in the form.

When the connect box shows up, it shows my Server options to choose from as:

(local)
(local)
Main
Main

But in SQL Manager, it shows my server being named .\SQLEXPRESS .
So what is my SQL Server named? how do I know which one contains my database residing on it?

I've just started using SQL Server after coming from Access and am somewhat confused here.

I'm currently working with this connection string ;

cnStr = "ODBC;Driver=SQL Server;Server='.\SQLEXPRESS';UID='Peter';PWD=;Database='Workwise'"

'Open database
Set m_DB = OpenDatabase("", False, False, cnStr)