Frank, try this code. Please not you will need to add a reference to the MS ActiveX library 2.5 in the tools / references section. This worked fine for me
Have fun.
Sub InsertData()
Dim SQLStatement
Dim ConnectString
Dim MyODBCConnection
'The name of my ODBC connection
MyODBCConnection = "UsersDB"
' Open connection
' You need to set the ADO reference up in the Tools/Refence menu (Microsoft Actice X data objects Library)
Set ConnectDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
ConnectDB.Open ConnectString
' Open recordset from database table
Set rsNames = New ADODB.Recordset
rsNames.CursorLocation = adUseClient
'rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText
' Setup a counter to run down the first 10 rows of sheet1
' getting data froom the first two columns
LineCount = 1
' Loop through the cells
Do While LineCount < 10
'Get values from worksheet
x = Worksheets("Sheet1").Cells(LineCount, 1).Value
y = Worksheets("Sheet1").Cells(LineCount, 2).Value
'Write to database
SQLStatement = "Insert into mytable values('" & x & "','" & y & "')"
rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText
LineCount = LineCount + 1
Loop
Set ConnectDB = Nothing
Set rsNames = Nothing
End Sub