create column in sql server

[Waka]

Hi,

Anyone know is it possible to write a macro that can create new column in sql server and after created the newly column, insert the data into the new column?

[Siddharth Rout]

Search Google on "SQL ALTER TABLE Statement"

[Waka]

this is how i use to insert. advice me how to change it or make it to ALTER TABLE Statement from

strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
    "Server=D1406;Database=OP;" & _
    "UID=coc;PWD=pd1].TestDB " & _
    "Select * FROM [Insert$]"

TO

ALTER TABLE Persons
ADD [2009-10-Wk1] date

ALTER TABLE Persons
ADD [2009-10-Wk2] date

ALTER TABLE Persons
ADD [2009-10-Wk3] date

ALTER TABLE Persons
ADD [2009-10-Wk4] date

ALTER TABLE Persons
ADD [2009-10-Wk5] date

and

after creating columns how can i make it insert to my new column like in my screenshot. i already square out the location.

Thanks for your respone

[Waka]

i tried using this codes its not woking

strSQLadd = "ALTER TABLE[odbc;Driver={SQL Server};" & _
    "Server=D1406;Database=OP;" & _
    "UID=coc;PWD=pd1].TestDB " &_
    "ADD ( 2009-10-Wk1 float(8),  2009-10-Wk2 float(8),  2009-10-Wk3 float(8),  2009-10-Wk4 float(8), 2009-10-Wk5 float(8) )"

[Siddharth Rout]

Answer to your 1st question in post 3

If "2009-10-Wk1" is the Column Name then change this ALTER TABLE Persons ADD [2009-10-Wk1] date to

ALTER TABLE Persons ADD 2009-10-Wk1 date

Answer to your 2nd question in post 3

If you want to add a Column with the name "DateOfBirth" then try this

ALTER TABLE Persons ADD DateOfBirth date

[Waka]

how can i do it in macro ? i would like to create a new column before insert the new data in that rows. like figure 1 to figure 3.

this code is not working, for my macro.

strSQLadd = "ALTER TABLE[odbc;Driver={SQL Server};" & _
    "Server=D1406;Database=OP;" & _
    "UID=coc;PWD=pd1].TestDB " &_
    "ADD ( 2009-10-Wk1 float(8),  2009-10-Wk2 float(8),  2009-10-Wk3 float(8),  2009-10-Wk4 float(8), 2009-10-Wk5 float(8) )"

follow by

strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
    "Server=D1406;Database=OP;" & _
    "UID=coc;PWD=pd1].TestDB " & _
    "Select * FROM [Insert$]"

[Siddharth Rout]

Ok here is an example which is untested

Check first it if it adds a column...

Private Sub Command1_Click()
    Dim conn As ADODB.Connection, rs As ADODB.Recordset
    Dim sqlStatement As String
    
    '~~> In case there is error
    On Error GoTo ErrHandler:

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    '~~> Connection String
    conn.ConnectionString = _
    "DRIVER={SQL Server};" _
    & "SERVER=D1406;" _
    & "DATABASE=OP;" _
    & "UID=coc;" _
    & "PWD=pd1;"

    conn.CursorLocation = adUseClient
    conn.Open
    
    '~~> Add the column to the table "MyTableName" Amend as required
    sqlStatement = "ALTER TABLE MyTableName ADD [2009-10-Wk1] float(8)"
    
    Call conn.Execute(sqlStatement)
    
    '~~> Close the recordset and connection
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
    
ErrHandler:
    '~~> Error Handling
    MsgBox Err.Number & ": " & Err.Description
End Sub

[Waka]

the code works but the there is an error too and the data type in my SQL server is 'real' instead of float.

[Hack]

Moved To Database Development

[si_the_geek]

the code works but the there is an error too

Which line of code did it occur on?

I presume it was on rs.Close , because the recordset is never opened. If so, that line should be removed (and anything else that refers to rs can be too).

and the data type in my SQL server is 'real' instead of float.

That is fine - they are just different names for the same thing.

[Waka]

Which line of code did it occur on?

at the end of the line encountered error

End sub

i remove rs.Close, no more error.

[Waka]

i using the nomarl insert statment i got ther result in 'fig 1' but i would like to make it like my figure 3, how can i make the insert statment into my figure 3 ?

[si_the_geek]

at the end of the line encountered error

End sub

i remove rs.Close, no more error.

Ah.. I should have pointed out that to find out which line caused the error you need to disable the error hander, by adding ' at the start of the On Error line.

However, as the fix was valid - it means my suspicion was correct.

i using the nomarl insert statment i got ther result in 'fig 1' but i would like to make it like my figure 3, how can i make the insert statment into my figure 3 ?

Don't use Insert, because that explicitly adds a new record.

To alter existing records, use Update.

[Waka]

i using this code on it but, its not working. please advice me how to make the update statment works. sorry about this i'm newbie. thanks for reply

strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
    "Server=D1406;Database=OP;" & _
    "UID=coc;PWD=pd1].TestDB " & _
    "Update TestDB set * FROM [Insert$]"

[Waka]

Hi,

i'm using this statemen but its not working.

sql = "Update TestDB SET "
    sqlStatement = sql & "2009-11-Wk1 = '" & Worksheets("Forecast").Range("E2:E6").Value"' "

[si_the_geek]

Rather than post something almost meaningless like "not working" and make us guess what it means in this particular case (which takes time, and can be wrong), actually tell us what is happening. eg:

• is there an error? if so what was it? and which line was it on?
• if variables are involved, what did they contain?
• ...

[Waka]

there is only an error msg 'End statement'.

[si_the_geek]

Was that the entire message?

Was a line of code highlighted? (if so, what was it?)

[Waka]

sql = "Update TestDB SET "
    sqlStatement = sql & "2009-11-Wk1 = '" & Worksheets("Forecast").Range("E2:E6").Value"'

in proper End statement.

[si_the_geek]

in proper End statement.

Er, what?

Please tell us the entire error message you are getting.

[Waka]

This error has shown

[si_the_geek]

"End statement" is very different to "Expected: end of statement", and far less informative. Now we know what the problem is, we can help solve it.

The problem is caused by not joining strings properly. Rather than this:

    sqlStatement = sql & "2009-11-Wk1 = '" & Worksheets("Forecast").Range("E2:E6").Value"'

..you should have this:

    sqlStatement = sql & "2009-11-Wk1 = '" & Worksheets("Forecast").Range("E2:E6").Value & "'"