Problems connecting SQL Server from VB 6

mikko · February 11th, 2004, 09:10 AM

Hi!

I have made a program which uses a database in SQL Server 2000 (SP3) via DSN from VB6 (Y2K SP4). When I run the program in VB everything is just fine but when I install the program on any other pc connection doesn't work any more. This happens even if it's me who is logged on the pc. There are several users including me who should be able to use the database in SQL Server. The users have usernames in database.

I use DSN and in code as following: strConn = "DSN=databasename;"... I tried to add TrustedConnection=yes to the end of the line but it doesn't seem to help.

Thanks!

joefawcett · February 11th, 2004, 12:18 PM

Firstly I would advise agaianst connection via dsn, you must create them on every machine that uses the application. Try using a dsn-less connection, there are lots of examples at:

www.connectionstrings.com

--

Joe

hortoristic · February 11th, 2004, 12:59 PM

We have an interesting situation here at GOVT where many of our batch jobs use DSN's - and one thing this is saving us much work is that somebody in their great wisdom had decided we must change all our server names from mountain names, Rainier, Bachlor, Hood, etc - to forest names, Spruce, Pine, Alder, etc.

So we just have to change the DSN's and not worry about all the app's that use them.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

rgerald · February 11th, 2004, 01:45 PM

mikko,

You may need to explicitly name the server in the DSN. This is necessary if you exist in a multi-server environment. If you don't explicitly name the server, the DSN will pick the default server which may be different for each pc.

Rand

rgerald · February 11th, 2004, 02:14 PM

We also use a lot of DSN's. The most difficult thing is standardizing them. I have a short VB utility program which allows me to create standardized DSN's from an internal array. It's designed to hold 28 DSN's and will create them very quickly. If anyone is interested, contact me directly ([email protected]) and I will supply the source code.

Rand

defiant · February 12th, 2004, 04:08 AM

Quote:

quote:Originally posted by hortoristic
We have an interesting situation here at GOVT where many of our batch jobs use DSN's - and one thing this is saving us much work is that somebody in their great wisdom had decided we must change all our server names from mountain names, Rainier, Bachlor, Hood, etc - to forest names, Spruce, Pine, Alder, etc.

So we just have to change the DSN's and not worry about all the app's that use them.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

i would use a Microsoft Data Link (.udl) file for the connection in such cases, since a file is easier to manage.

defiant.

mikko · February 12th, 2004, 05:37 AM

Hi!

Thank you for your answers. Actually I am aware of most of what you have written. I was just wondering if the connection text is not correct: "dsn="name". Has anyone experiences of same kind of situation. As far as I know I've done everything as I am supposed to do and it still won't work.