To write data to an SQL database using VBA, you’ll need to leverage the SQL Server ODBC driver and connect to the database using ADO (ActiveX Data Objects). Here’s an example of VBA code that writes data to an SQL database:
Sub WriteDataToSQL() Dim conn As Object ' ADODB.Connection Dim strSQL As String ' Connection string for the SQL Server database Dim connStr As String connStr = "Driver={SQL Server};Server=YourServerName;Database=YourDatabaseName;Trusted_Connection=yes;" ' Specify the data to be written to the database Dim fieldValue As String fieldValue = "Hello, World!" ' Replace with your data ' Create a Connection object Set conn = CreateObject("ADODB.Connection") ' Open the Connection to the SQL Server database conn.Open connStr ' Create the SQL INSERT statement strSQL = "INSERT INTO TableName (FieldName) VALUES ('" & fieldValue & "')" ' Replace TableName and FieldName with the actual table and field names ' Execute the SQL statement conn.Execute strSQL ' Close the connection conn.Close ' Clean up the object Set conn = Nothing End Sub
In this code, you need to modify the connStr variable with the appropriate connection string for your SQL Server database. Replace "YourServerName" with the name or IP address of your SQL Server instance, and "YourDatabaseName" with the name of your database. Also, replace "TableName" and "FieldName" with the actual table and field names where you want to write the data.
The fieldValue variable contains the data you want to write to the SQL database. You can customize it according to your requirements. The example provided writes the string “Hello, World!” to the specified field.
When you run this code, it will establish a connection to the SQL Server database using ADO and the SQL Server ODBC driver, create an SQL INSERT statement with the data, execute the statement to write the data to the database, close the connection, and clean up the object.
Note: Make sure that you have the appropriate permissions and that the specified SQL Server instance and database are accessible by the VBA code.