v.vasquezsj
07-22-2008, 05:51 PM
Hello All,
Just wanted to share with everyone on how to write into a table in SQL 2005 server by passing variables to the respected fields.
Enjoy!
-V
VBA Code
Sub writeIntoSQL()
Dim strConn As ADODB.Connection
Dim var1, var2, var3 ,var4
Dim strSQL As String
Dim lngRecsAff As Long
Set strConn = New ADODB.Connection
strConn = "PROVIDER=sqloledb.1;"
strConn = strConn & "Network Library=dbmssocn;"
strConn = strConn & "DATA SOURCE=SQLServer2005Name\SQLEXPRESS;"
strConn = strConn & "INITIAL CATALOG=YourDatabaseName;"
strConn = strConn & "Integrated Security=SSPI"
var1 = "John"
var2 = "Smith"
var3 = "555-5555"
var4 = "Single"
'Using INSERT INTO command and passing variables
strSQL = "INSERT INTO [tblName] ([fieldName1], [fieldName2], [fieldName3]," & _
"[fieldName4] VALUES ('" & var1 & "', '" & var2 & "', '" & var3 & "','" & var4 & "')"
Debug.Print strSQL
strConn.Open
strConn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
strConn.Close
Set strConn = Nothing
End Sub
Just wanted to share with everyone on how to write into a table in SQL 2005 server by passing variables to the respected fields.
Enjoy!
-V
VBA Code
Sub writeIntoSQL()
Dim strConn As ADODB.Connection
Dim var1, var2, var3 ,var4
Dim strSQL As String
Dim lngRecsAff As Long
Set strConn = New ADODB.Connection
strConn = "PROVIDER=sqloledb.1;"
strConn = strConn & "Network Library=dbmssocn;"
strConn = strConn & "DATA SOURCE=SQLServer2005Name\SQLEXPRESS;"
strConn = strConn & "INITIAL CATALOG=YourDatabaseName;"
strConn = strConn & "Integrated Security=SSPI"
var1 = "John"
var2 = "Smith"
var3 = "555-5555"
var4 = "Single"
'Using INSERT INTO command and passing variables
strSQL = "INSERT INTO [tblName] ([fieldName1], [fieldName2], [fieldName3]," & _
"[fieldName4] VALUES ('" & var1 & "', '" & var2 & "', '" & var3 & "','" & var4 & "')"
Debug.Print strSQL
strConn.Open
strConn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
strConn.Close
Set strConn = Nothing
End Sub