Here’s an example of VBA code that writes data to an Access database using ADO (ActiveX Data Objects):
Sub WriteDataToAccess() Dim conn As Object ' ADODB.Connection Dim rs As Object ' ADODB.Recordset Dim dbPath As String Dim strSQL As String Dim fieldValue As String ' Specify the path and filename of the Access database file dbPath = "C:\Path\to\your\Database.accdb" ' Specify the data to be written to the database fieldValue = "Hello, World!" ' Replace with your data ' Create a Connection object Set conn = CreateObject("ADODB.Connection") ' Open the Connection to the Access database conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath ' Create a Recordset object Set rs = CreateObject("ADODB.Recordset") ' Specify 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 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 dbPath variable to the actual path and filename of your Access database file. 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 Access 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 Access database using ADO, create an SQL INSERT statement with the data, execute the statement to write the data to the database, close the recordset and the connection, and clean up the objects.
Note: Make sure that the specified Access database file exists in the specified path and that the table and field names are accurate.