Newbie needs connection help!

[davevail]

Hi, I just installed SQL Server 2000 on my home computer for learning purposes - I converted some tables from an ACCESS database to a SQL database - my server is named "VWC1" and the SQL database is "Security" - I am trying to get a VB program that runs OK with Access to work on the SQL/Server database - I am having problems getting the proper "connection" code(struggling for hours..) - I will post the old ACCESS code here - any help appreciated in converting this for SQL/Server
Thanks to all responders!
----------------------------
Attribute VB_Name = "Module1"
Option Explicit
'declare DAO variables
Private db As Database
Private rs As Recordset

Public Function GoodCredentials(UserName As String, Password As String) As Boolean
'This function is exposed to the project and is used to check _
the user name and password passed from the Logon form. If the _
user name and password are found, the function returns True.
'Use DAO to create a recordset to search
Set db = OpenDatabase("\vb\dave\security.mdb&quot
Set rs = db.OpenRecordset("Users", dbOpenDynaset)

'Search the recordset object for the user name
rs.FindFirst "[userName] = '" & UserName & "'"
'If the user name is found and the password for that record _
matches the password given by the user, the credentials are _
good; if no user name is found or the password is not correct _
the credentials are bad.
If Not rs.NoMatch And rs![Password] = Password Then
GoodCredentials = True
Else
GoodCredentials = False
End If
End Function

 

[cmmrfrds]

Create and test your connection string. This is an ADO connection. I recommend changing all your DAO datasources to ADO.
Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multiple tabs.
6. use the SQL Server provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

EXAMPLE of using the connections string. This is a typical SQL Server connection string. SQLOLEDB.1 is the native SQL Server provider.
Function Atest()
Dim cn As New ADODB.Connection, Sql1 As String
Dim rs As ADODB.Recordset, connString As String, bdate As Double
Set rs = New ADODB.Recordset
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=angelfish;" & _
"Persist Security Info=False"
bdate = CDbl(#8/30/1963#)

Sql1 = "select * from dbo.Employees where convert(int, BirthDate) = " & bdate
rs.Open Sql1, connString, adOpenForwardOnly, adLockReadOnly
Debug.Print rs.EOF
Debug.Print rs!LastName

End Function