This is how to write into SQL db table using excel VBA

[v.vasquezsj]

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
[VBA]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[/VBA]

[Mavyak]

[vba] 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"[/vba]
I'm confused, is strConn the connection object or a string variable containing the connection object's connection string?

[v.vasquezsj]

strConn is a string variable containing the connection string.

This concatenated string format

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"

is the same as this:
strConn = "PROVIDER=sqloledb.1;Network Library=dbmssocn;DATA SOURCE=SQLServer2005Name\SQLEXPRESS;INITIAL CATALOG=YourDatabaseName;Integrated Security=SSPI"

[Mavyak]

[VBA]strConn.Open
strConn.Execute strSQL, lngRecsAff, adExecuteNoRecords[/VBA]

It looks like you're executing the "Execute" method of a connection object against a string variable here.

[Mavyak]

[vba]strConn.Open
strConn.Execute strSQL, lngRecsAff, adExecuteNoRecords[/vba]
It looks like you're executing the "Execute" method of a connection object against a string variable here.

Disregard. I was not aware that the connection string was the default property of the connection object and could be accessed by using only the object name instead of "object name dot property" (strConn.ConnectionString).

Good to know info. Thanks for sharing!

[v.vasquezsj]

You're welcome!

I hope we all can share our code and learn from each other!