Ok, When you add a new ODBC data source you do select SQL Server as your driver.

From there the next screen you get differs depending on the version of ODBC installed. Basically though it is asking you for a "Name". This is the DSN name and is what you will use in your code to refer to it (ie OneDB in our example). The description can be anything you like and the "Server" should be the machine you have SQL Server installed on, use [localhost] if its on the same machine.

Then it will take you to another screen where you select if you either want to use Windows Authentication (ie your login name is used and compared to the SQL Server logins list) OR SQL Server Authentication (You can temporarily give the username of "sa" and its password for testing purposes). Then another screen will give you the option of setting the default database. Click this and select the database you created on SQL Server.

Now skip through all the rest until it asks you to "TEST" your connection and make sure it says "TEST SUCCESSFUL".

You've created the connection

Now as for creating or updating records... There are 2 ways.

Method 1 - Execute
Code:
sSQL = "UPDATE Table Set Field1 = 'fred' WHERE key = 1"

dbsDatabase.Execute(sSQL)
OR

Method 2 - Recordset
Code:
Set rstData = dbsDatabase.OpenRecordset("SELECT * FROM Table", dbOpenDynaset, dbRunAsync, dbOptimistic)

Do While Not rstData.Eof
  If rstData("Key") = 1 Then
    ' Modifying the CURRENT Record
    rstData.Edit                 ' Set to edit mode
    rstData("Field1") = "fred"   ' Change the field value
    rstData.Update               ' Save the record
  End if
  rstData.MoveNext
Loop

' Adding a NEW Record
rstData.AddNew
rstData("Key") = 3
rstData("Field1") = "john"
rstData.Update

rstData.Close
Set rstDate = nothing
Have a look at the help files on the AddNew, Edit, Update and other functions and it should logically lead you from there to give you all the examples you need.