Connecting to SQL Server 2000 database vs Access 9x database

[33216CLC]

I am a VB developer who uses an Access database to store my data. I have never coonected to a sql server database and I am not at all familiar with stored procedures. I have read about how useful they are and am sure I will learn more about them. However, presently I do not have enough time do this. I would like to know how what I did with Access can be done with sql server in vb6. A few code examples to compare with what I have for use with Access would be appreciated.

Here is what I presently have in my application.

1. Path of database

MyDbPath = MyPath & MyDbName & ".mdb"


2. Creation of workspace

Set wsPayroll = CreateWorkspace ("NewODBCWorkspace", "admin", "", dbUseODBC)

3. Opening the connection to the database and dsn.

Set dbPayroll = wsPayroll.OpenConnection ("ConPayroll", _
dbDriverNoPrompt, , _
"ODBC;DATABASE=Payroll.mdb;UID=admin;PWD=rocket;FILEDSN=Payroll4&quot

Set dbs = DBEngine.Workspaces(0).OpenDatabase(MyDbPath, False, False, "; pwd=" & g_sPassword)

4. Opening a recordset

Dim sql As String
Dim rs as recordset

sql = "select * from company where ID=" & Company_ID
Set rs = dbPayroll.OpenRecordset(sql, dbOpenDynaset, dbExecDirect, dbOptimistic)

5. Updating a recordset

rs.edit

With rs
![SalNo] = txtSalNo
![Sal] = txtSal
![NINo] = txtNINo
![NI] = txtNI
![mod_user] = UserId
![mod_dt] = Now
End With

rs.Update

Thanks