Here’s an example of VBA code that reads data from a SQL database using ADO (ActiveX Data Objects):
Sub ReadDataFromSQLDatabase() Dim conn As Object ' ADODB.Connection Dim rs As Object ' ADODB.Recordset Dim strSQL As String ' Connection string for the SQL database Dim connStr As String connStr = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password;" ' Specify the SQL query to retrieve data from the SQL database strSQL = "SELECT * FROM TableName" ' Replace TableName with the actual table name or query ' Create a Connection object Set conn = CreateObject("ADODB.Connection") ' Open the Connection to the SQL database conn.Open connStr ' Create a Recordset object and execute the SQL query Set rs = CreateObject("ADODB.Recordset") rs.Open strSQL, conn ' Loop through the recordset and read the data If Not rs.EOF Then rs.MoveFirst Do Until rs.EOF ' Read data from the recordset and do something with it Dim fieldValue As Variant fieldValue = rs.Fields("FieldName").Value ' Replace FieldName with the actual field name in the table MsgBox fieldValue rs.MoveNext Loop End If ' Close the recordset and the connection rs.Close conn.Close ' Clean up the objects Set rs = Nothing Set conn = Nothing End Sub
In this code, you need to modify the connStr variable with the appropriate connection string for your SQL database. Replace "ServerName" with the name or IP address of the SQL server, "DatabaseName" with the name of the database, "UserName" with the username, and "Password" with the password. Also, replace "TableName" with the actual name of the table or query you want to retrieve data from. Inside the loop, you can access specific field values by replacing "FieldName" with the actual field name in the table.
When you run this code, it will establish a connection to the SQL database using ADO, execute the SQL query, loop through the recordset, read the data from the specified field, and display it in a message box.