Database Connection

Hi,

I am currently testing different ways that I can connect my SQL Server
Database to my Excel Sheet and I just wanted to know if there are any real
advantages to either, at first I was trying the below connection string in VB
in my Excel Sheet -:

"ODBC;DSN=NameOfDSN;UID=UserName;pword=PASSWORDAPP=Microsoft®
Query;WSID=SERVER;DATABASE=DATABASE;Network=DBMSSOCN;Trusted_Connection=Yes" _

Now if I have my logic right which I might not do :-) this has to have a
ODBC connection set up through control pannel to work.

The other connection string is -:

"ODBC;Driver={SQL
Server};Server=SERVER;Database=DATABASE;WSID=WorkStationID;UID=UserName;pword=PASSWORDAPP=Microsoft® Query;Network=DBMSSOCN;Trusted_Connection=Yes" _

This string sets up all the connection attributes through the VB without
having to use Control Pannel.

Are my assumptions with both of these connections correct and if so for some
reason I get a different output from the second string connection, for some
reason it will return my table headings in the SQL Database but no data, but
if it is run a couple of times it suddenly starts to work, I though I was
slowly getting the grasp of all this but it appears not.

Any help that is offered would be much appreciated.

Regards
Philip

Hi Phil,

If you're using ADO to connect to SQL Server, I suggest using an OLEDB
connection string. More info here:

http://msdn.microsoft.com/library/de...qlprovspec.asp

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Phil wrote:
> Hi,
>
> I am currently testing different ways that I can connect my SQL Server
> Database to my Excel Sheet and I just wanted to know if there are any
> real
> advantages to either, at first I was trying the below connection
> string in VB
> in my Excel Sheet -:
>
> "ODBC;DSN=NameOfDSN;UID=UserName;pword=PASSWORDAPP=Microsoft®
> Query;WSID=SERVER;DATABASE=DATABASE;Network=DBMSSOCN;Trusted_Connection=Yes"
> _
>
> Now if I have my logic right which I might not do :-) this has to
> have a
> ODBC connection set up through control pannel to work.
>
> The other connection string is -:
>
> "ODBC;Driver={SQL
> Server};Server=SERVER;Database=DATABASE;WSID=WorkStationID;UID=UserName;pword=PASSWORDAPP=Microsoft®
> Query;Network=DBMSSOCN;Trusted_Connection=Yes" _
>
> This string sets up all the connection attributes through the VB
> without
> having to use Control Pannel.
>
> Are my assumptions with both of these connections correct and if so
> for some
> reason I get a different output from the second string connection,
> for some
> reason it will return my table headings in the SQL Database but no
> data, but
> if it is run a couple of times it suddenly starts to work, I though I
> was
> slowly getting the grasp of all this but it appears not.
>
> Any help that is offered would be much appreciated.
>
> Regards
> Philip