Connect VB6 with SQL Server 2k by DSN and DBQ, logging screen, dbase loc, uid and pwd

[ayan.mukherjee]

Dear Sir / Madam,

Date : 12th February 2008

Hi, I am Ayan Mukherjee, a Programmer seeking for help. Please help me out.

Software Specification : I have Windows 2000 Server with service pack 4, IIS version 5, IE 6 with service pack 6.1.

Working Environment : I have to work for an application with VB6 as front end tool and MS SQL Server 2000 as a database.

I need to develop a DSN for my application, which I have successfully done from the ODBC tab. The DSN name is "Employee". My SQL Server 2000's database name is also "Employee". My SQL Server name is "VK_AYANMUKHERJE".
I am able to connect with SQL Server from VB6 easily at local machine. Please see the code below.

Public Function Connection()

On Error GoTo errcon

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Set adoconn = New ADODB.Connection
adoconn.ConnectionString = "Driver={SQL Server};Server=VK_AYANMUKHERJE;Database=Employee;Uid=;Pwd=;"
adoconn.Open
adoconn.CursorLocation = adUseClient 

Dim sqlstr As String

sqlstr = "select * from emp_details"

rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

rs.Open sqlstr, adoconn, adOpenDynamic, adLockOptimistic

Set rs = Nothing

Exit Function

errcon:
MsgBox "The error is : " & err.Description & " and the error no. is : " & err.Number, vbCritical, "Connection Error."
Exit Function

End Function

Now the problem is I need to develop the application in such a way that, 1) The Application path i.e. App.Path has to be supplied along with the DSN name and DBQ Name is also required to provide as it is a required that the Registry should have the key values, 2) When the login screen comes, the user is required to provide the Database name, User Id and Password. Means the database has to be found out from the drive. How can I do it?
Please help so that I can start as soon as possible. I am running from pillar to post, but have not got the solution yet. Yours earlier intervention will be highly appreciated.

Please publish it in your esteemed site as soon as possible and it would be very helpful and nice, if you can also send me the solution in mail in my mail id.

Thanking you in anticipation, and hoping for a honorable reply from your kind hands, I remain.

Yours faithfully,

With regards,

Ayan Mukherjee