ADO (VB6 to SQLExpress2005

Hi Alan!

I had this same nasty issue long time ago. What I did to solve it is to
create an ODBC DSN to ensure I had communication with the database itself
using the user ID and password specified. Basically, I had four mayor
problems:

1. MSSQL 2005 Express installer likes to create an "instance" of the "SQL
Engine", which results in an independent Windows Service (kinda like Oracle
does with their listeners). So, to specify which "instance" I wanted to talk
to, I had to add it to the server name. Following your example, I had to
specify the server name as ".\SQLEXPRESS".

2. SQL has an option to listen to different ports. The ODBC Driver has the
option to try to find which one is. However, in order to be able to use this
feature, the current logged Windows User must have enough authority to look
for these ports. My user ID didn't have any authority with the server, so I
had to tell the ODBC DSN to not dynamically detrmine the port, and always use
the 1433 port instead.

3. Once I solved the last problem, I was then able to work around the
Firewall. I had to manually create an entry in the Windows Firewall to
specifically allow connections to the port 1433.

4. SQL Express by default does not allow SQL authentication. In order to be
able to connect, I had to enable this feature.

After these problems were solved, my VB6 app connected
successfully!!!!... but then there was this nasty performance issue with
ADODB that I had to deal with... Then again, that is another story, and will
be told in another post.

I hope it helps!!!!

Best Regards,

Tarh Ik
PS: This posting has been posted "AS IS"


"Alan Gillott" wrote:

> I suspect that this has been answered 1000 times but...
>
> I am connecting from an older VB6 program running on Vista with
> SQLExpress2005 installed. it was a standard install and everything can't be
> completely wrong because the Management Studio works. I added the database
> (but no tables as the first thing the app does is deletes them all and
> readds them). However, as creating a mixed mode instance is beyond the
> abilities of my tiny mind (or rather MS has made it exceptionally
> inconvenient) I have to recast my connection string (yes, naughty me was
> using sa with no password), and, as is usual when messing with something
> that works, it doesn't any more.
>
> clearly I have misstepped somewhere: can any one lead me by the nose to
> nirvana...
>
> The latest attempt at connection is:
>
> Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=Circles;Integrated
> Security=SSPI;Persist Security Info=False
>
> According to my info Data source can be either . or (local) if i am just
> using the default memory management option.
>
> Error is [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied.
> 80004005
>
> Any advice gratefully received: hair transplant also required <grin>
> A
>
>
 >> Stay informed about: ADO (VB6 to SQLExpress2005