VBA Access input data to table mysql

To insert data into a MySQL table from an Access database using VBA, you will need to use an ADO connection object to connect to the MySQL database and execute an SQL INSERT statement. Here is an example of how to do this:

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

Set rs = New ADODB.Recordset

' Replace "server_name", "database_name", "username", and "password"

' with the appropriate values for your MySQL database.

cn.Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=server_name;Database=database_name;Uid=username;Pwd=password;"

' Replace "table_name" and "field_list" with the appropriate values for your MySQL table.

' The field list should be a comma-separated list of field names.

rs.Open "INSERT INTO table_name (field_list) VALUES (?, ?)", cn, adOpenDynamic, adLockOptimistic

' Replace "field1_value" and "field2_value" with the values that you want to insert into the table.

rs.AddNew

rs("field1").Value = "field1_value"

rs("field2").Value = "field2_value"

rs.Update

rs.Close

cn.Close

Set rs = Nothing

Set cn = Nothing

This code creates a connection to the MySQL database using the MySQL ODBC driver, and then opens a recordset using an SQL INSERT statement. The AddNew method is used to add a new record to the recordset, and the values for each field are set using the field's Value property. The Update method is then called to save the new record to the MySQL table.

Keep in mind that you will need to install the MySQL ODBC driver on your computer in order to use the MySQL ODBC driver in your VBA code. You can download the driver from the MySQL website (https://dev.mysql.com/downloads/connector/odbc/).

Semoga Membantu..

Tweet

Subscribe to receive free email updates: