Basic excel to sql question

[davecarrera]

Hi All,

Whits end, a million searches here and google :-(

How do i connect to and update 1 (one) field in a sql server table with the data in 1(one) field in my spreadsheet.

I have got the date from sql server into my spreadsheet and now i want to simply send changes in my one field back to the field i want to update in my sql server.

I am really new to this and have got completley snowblind with the highly achademic articles i have stumbled on.

Many Many thanks for any help you may give.

Dave

 

[davecarrera]

Hi Geoff,

I get a debug on rs.Open SQL, adOpenForwardOnly, adLockPessimistic

saying

runtime error "91"

Object variable or With Block variable not set

Errr scatching head.....

Nope still errrrr

Message box works nice

Dave

 

[PHV]

Why not simply use the Execute method of the Connection object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davecarrera]

hmmmm,

using cn.execute (SQL)

I get the msgbox giving back to me what looks like a good update sql statement, but i get a debug error what looks like to me as if the update is trying to update the first row of the table not the one in my where clause, like is said which looks correct in the msgbox.

Needless to say no updates are performed.....

Any clues

Dave

 

[SkipVought]

How about

For i = 5 To lRow

 SQL = "UPDATE table SET field = " & Range("D" & i).Value & "WHERE field2 = " & Range("A" & i).Value & " LIMIT 1"
 
 msgbox SQL
 
 rs.Open SQL, adOpenForwardOnly, adLockPessimistic
 [b]rs.close[/b]
Next i

Skip,

_[red][/red]

 

[PHV]

Lack of space before the WHERE keyword.
Unless field and field1 are defined as numeric, lack of single quotes.

SQL = "UPDATE table SET field='" & Range("D" & i).Value & "' WHERE field2='" & Range("A" & i).Value & "' LIMIT 1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

ooops - shows my lack of SQL skills
<slinks off and gets his coat>

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question