SQL uploading

[fodzilla]

Hi Guys,

Got myself in a bit of a pickle today. I've always been able to get data out of SQL using select statements and laying down records in a excel sheet but now I need to insert rows and update records that already exist from an excel sheet.

This is something I have never done before and believe i'm making it more complicated that it actually is.

I need help with a simple uploader that i can step through with f8 and watch it upload each row after another but after reading all the detail I can find online, i am fully confused on the process. Even the connect string peices look confusing now...

I have an excel file saved:
C:\Files\Excel1.xls

And need it updating to to my sql server
server name: SQLserver1
username: username1
password: password1

Columns in the file are named and these all match the sql table, even in the same order.

column1
column2
column3
column4
column5
column6
column7

with column 3 being the primary key.
ANy help would be apreicated as i've been looking into this for 3 days now.

Thanks in advance

Fodzilla

 

[fodzilla]

Sorry I forgot to mention that my experience is in vb to date and a little SQL.

 

[wigi]

You say that SQL statements work to SELECT data. Then, I would focus on such a statement and get it working. For instance, output the results in the immediate window.

After that, you can change the SELECT into an UPDATE statement; you should first set it up oin the database itself (for instance MS SQL Server), then port it to Excel and VBA. This will mean bringing in cell references instead of hardcoded values. Use test tables meanwhile (to not compromise the good tables).

 

[fodzilla]

I'm a little lost now. Sorry.

To say I use select statements to read the data fro
SQL and lay it down on a named range and loop to next row would be correct. Are you saying its a case of chnging the select to update to reverse the process? With a named range is row one of the pk?