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

 

[fredericofonseca]

search these forums for ADO and INSERT, and eventually Excel and you will find plenty of samples.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

I have got the date from sql server into my spreadsheet
How did you that ?

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

 

[davecarrera]

Sorry i meant data.

As for searching forums, i have, and cant make heads or tails of all the stuff i am finding.

Let me clarify where i am at.

I have pulled in records using import data in excel relevant for the update job i want to send back.

One of these fields is a number colum, currently all set to the number 1.

I want to change this number and send it back to the sql server table field something like this

update table set field=excelfield where id=excelidfield and loop through all the fields in the excel file and stop at the end.

When i refresh the excel file the data should come back with the numbers i sent back changed.

Who do i do that ????

Dave

 

[fredericofonseca]

show the code you have tried so far from the examples you have found.

If you cant get around simple ADO code, then perhaps you should consider reading the manuals at

http://www.microsoft.com,

or buying a book, or eventually hiring someone to do it for you (or teach you).

As for the looping bit, there are also plenty of examples on these forums (mainly VBA forum), on that area, so once again show us what code you have tried to far.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[davecarrera]

Its not so much as "get around simple ADO code" its where does it go ????

I am new to MS ways of doing things, come from php/mysql/cgi background and cant work out what i am to do in as far as telling the spreadsheet to connect to sql server, and where to tell it.

 

[fredericofonseca]

it goes to the VBA editor.

Press alt+F11 from Excel and you will get to it.

then just add references to the correct components (MDAC for ADO), and start coding in VBA.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[davecarrera]

would that be tick the box in refs for Microsoft Data Access Components Installed Version ???

 

[davecarrera]

Got it it think Microsoft DAO Object Libary 3.6

 

[PHV]

DAO is mainly used for JetSQL.
I suggest you use Microsoft ActiveX Data Objects instead for SQL Server.

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

 

[davecarrera]

ok ive got this far without compile errors

Public Sub DataLink()
Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")
cn.Provider = "SQLOLEDB"
cn.ConnectionString = "server=xxx.xxx.xxx.xxx;database=test;uid=me;pwd=right"
cn.ConnectionTimeout = 600
cn.Open

cn.Close
End Sub

So if i have it right i now have to do something with the rs object.....

So how do i know loop through my excel sheets rows, ones with data in, and update relevant sql server tables field with the one in my excel sheet ????

Thanks for the help so far

Dave

 

[davecarrera]

p.s

i would not mind this little trick to for my spreadsheet

i have to cells with the database name and server info in them.

Is making these part of the connection string something like this

Set Sheet.A1 = server
Set sheet.A2 = database

Dave

 

[xlbo]

Other way round

dim theServer as string, theDB as string

theServer = Sheets("Sheet1").range("A1").text
theDB = Sheets("Sheet1").range("A2").text

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

 

[davecarrera]

So i now have my connection to SQL Server

I have also added a control button that onclick triggers the module i have made.

Al good so far(this is like pulling teeth)

So how do i now loop through my excel records and update the relvant fields in my sql table.

Kind Regards

Dave

 

[davecarrera]

Ok below is my attempt at looping through cells in my spreadsheet.

The message box is just to make sure i am picking up the right value.

How do i also show the value of A5 to End of A colum.

So far i only get value of D5 to end of D

So my message box should output value of A5 and D5, A6 and D6 and so on

Public Sub DataLink()
Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")

cn.Provider = "SQLOLEDB"
cn.ConnectionString = "server=xxx.xxx.xxx.xxx;database=test;uid=me;pwd=mine"
cn.ConnectionTimeout = 600
cn.Open

Dim r As Range

For Each r In Range(Cells(5, 4), Cells(5, 4).End(xlDown))

MsgBox r.Value
Next


cn.Close
End Sub

 

[xlbo]

For Each r In Range(Cells(5, 4), Cells(5, 4).End(xlDown))

   MsgBox r.Value 'column D
   [b]msgbox r.offset(0,-4).value[/b] 'column A
Next

to explain, your loop is going through each cell (r) in the range defined from your start cell (D5) to the last cell that has data in that column

Another way to write it may be:

dim lRow as long
lRow = cells(65536,4).end(xlup).row
'alternatively use lRow = cells(5,4).end(xldown).row

for i = 5 to lRow
msgbox cells(i,1).value 'Column A
msgbox cells(i,4).value 'Column D
Next i

No real difference in performance but sometimes easier to reference using a counter variable rather than an object variable

Could also be:

dim lRow as long
lRow = cells(65536,4).end(xlup).row
'alternatively use lRow = cells(5,4).end(xldown).row

for i = 5 to lRow
msgbox Range("A" & i).value 'Column A
msgbox Range("D" & i).value 'Column D
Next i

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

 

[davecarrera]

Geoff your a star

Now that i have my two sets of data for my update sql i think i can move on to completing this task.

Once again a very big thank you for your easy to understand explinations.

Many kind Regards

Dave

 

[xlbo]

no worries

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

 

[davecarrera]

Ok spoke to soon

I am doing something wrong with the call to the sql bit i think....

I think its in the rs.open bit as thats where the debugger throws a wobbler.


Public Sub DataLink()
Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")

cn.Provider = "SQLOLEDB"
cn.ConnectionString = "server=xxx.xxx.xxx.xxx;database=test;uid=jiffy;pwd=jaffy"
cn.ConnectionTimeout = 600
cn.Open

Dim SQL As String

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


Dim lRow As Long
lRow = Cells(5, 4).End(xlDown).Row

For i = 5 To lRow

rs.Open SQL, adOpenForwardOnly, adLockPessimistic

Next i
rs.Close
cn.Close
Set rs = Nothing
End Sub


Nearlly there

Dave

 

[xlbo]

SQL variable needs to be a string and needs to be set on each loop:

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

Next i

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