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?
Printable View
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?
Search Google on "SQL ALTER TABLE Statement" ;)
this is how i use to insert. advice me how to change it or make it to ALTER TABLE Statement from
TOCode:strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=D1406;Database=OP;" & _
"UID=coc;PWD=pd1].TestDB " & _
"Select * FROM [Insert$]"
andCode: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
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
i tried using this codes its not woking
Code: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) )"
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
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.
follow byCode: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) )"
Code:strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=D1406;Database=OP;" & _
"UID=coc;PWD=pd1].TestDB " & _
"Select * FROM [Insert$]"
Ok here is an example which is untested
Check first it if it adds a column...
Code: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
the code works but the there is an error too and the data type in my SQL server is 'real' instead of float.
Moved To Database Development
Which line of code did it occur on?Quote:
Originally Posted by Waka
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).
That is fine - they are just different names for the same thing.Quote:
and the data type in my SQL server is 'real' instead of float.
at the end of the line encountered errorQuote:
Originally Posted by si_the_geek
i remove rs.Close, no more error.Code:End sub
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 ?
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.Quote:
Originally Posted by Waka
However, as the fix was valid - it means my suspicion was correct.
Don't use Insert, because that explicitly adds a new record.Quote:
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 ?
To alter existing records, use Update.
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 :)
Code:strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=D1406;Database=OP;" & _
"UID=coc;PWD=pd1].TestDB " & _
"Update TestDB set * FROM [Insert$]"
Hi,
i'm using this statemen but its not working.
Code:sql = "Update TestDB SET "
sqlStatement = sql & "2009-11-Wk1 = '" & Worksheets("Forecast").Range("E2:E6").Value"' "
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?
- ...
there is only an error msg 'End statement'.
Was that the entire message?
Was a line of code highlighted? (if so, what was it?)
in proper End statement.Code:sql = "Update TestDB SET "
sqlStatement = sql & "2009-11-Wk1 = '" & Worksheets("Forecast").Range("E2:E6").Value"'
Er, what?Quote:
Originally Posted by Waka
Please tell us the entire error message you are getting.
This error has shown
"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:
..you should have this:Code:sqlStatement = sql & "2009-11-Wk1 = '" & Worksheets("Forecast").Range("E2:E6").Value"'
Code:sqlStatement = sql & "2009-11-Wk1 = '" & Worksheets("Forecast").Range("E2:E6").Value & "'"