Use VBA to automatically update data to ACCESS in EXCEL

How to use VBA in EXCEL to automatically update data to ACCESS

This is my code for importing data from Excel into Access. You can refer to it: (Make sure the Excel and Access files are in the same folder) ```python import pandas as pd #Read Excel file excel_data = pd.read_excel('File path/file name.xlsx') # Connect to Access database access_conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=file path/database name.accdb') cursor = access_conn.cursor() # Insert Excel data into Access table for index, row in excel_data.iterrows(): cursor.execute("INSERT INTO

Public Sub Customer's normal price supply batch maintenance ()

If Cells(4, 6) = ＂＂ Then

MsgBox "Please select the system that needs to maintain the price first!", vbInformation

Cells(4, 6).Select

Exit Sub

Else

Dim i As Integer, j As Integer, k As Integer, sht As Worksheet 'i,j,k are integer variables; sht is an excel worksheet object variable, pointing to a certain worksheet

Dim cn As New ADODB.Connection 'Define data link object and save connection database information; please add ADO reference first

Dim rs As New ADODB.Recordset 'Define the recordset object and save the data table

Dim strCn As String, strSQL As String 'String variable

Dim mdbFile As String

On Error GoTo add_err

mdbFile = ActiveWorkbook.Path & "\DY_DATA.mdb"

strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile 'Define database link string

cn.Open strCn 'Establish a connection with the database. If successful, return the connection object cn

Set rs = New ADODB.Recordset

rs.Open "dbl direct customer normal price list", cn, adOpenKeyset, adLockOptimistic

k = Cells(4, 8) 8

For i = 9 To k

If Cells(i, 7) = ＂＂ Then

Else

rs.AddNew

rs(1) = Cells(i, 2)

rs(2) = Cells(i, 7)

rs(3) = Cells(i, 8)

rs.Update

Cells(i, 7) = "＂"

Cells(i, 8) = "＂"

End If

Next

MsgBox "Data record added successfully!", vbInformation

Cells(4, 6).Select

ActiveWorkbook.RefreshAll

add_exit:

Exit Sub

End If

add_err:

MsgBox Err() & vbCrLf & Error()

Resume add_exit

End Sub

Please tell me how to refresh a worksheet using Excel VBA

You can indicate that you believe the database is by adding a digital signature to the database (Digital signature: An electronic, cryptography-based secure verification stamp on a macro or document. This signature confirms that the macro or document comes from the issuer and has not been tampered with.) is safe and its content is trustworthy. This helps users of the database determine whether to trust the database and its contents.

The process used to digitally sign a database depends on whether the database uses a Microsoft Office Access 2007 file format (such as an .accdb file) or a previous format (such as an .mdb file). However, both processes require the use of security certificates.

You can use a commercial security certificate or create your own security certificate. This topic explains how to create your own security certificate.

before the start

To add a digital signature, you must first obtain or create a security certificate. Think of a security certificate as a signature pen for digital signatures or your own sealing wax seal.

If you don't have a security certificate, you can use the SelfCert tool (included with Microsoft Office) to create one.

Create a self-signed certificate

1. In Microsoft Windows, click the "Start" button, point to "All Programs", "Microsoft Office", "Microsoft Office Tools", and then click "Digital Certificate for VBA Projects".

- or -

Browse to the folder where the Office Professional 2007 program files are located. The default folder is drive:\Program Files\Microsoft Office\Office12. In this folder, please find and double-click "SelfCert.exe".

The "Create Digital Certificate" dialog box will appear.

2. In the "Your certificate name" box, type the name of the new test certificate.

3. Click "OK" twice.

Notes If you don't see the Digital Certificates for VBA Projects command or you can't find SelfCert.exe, you may need to install SelfCert.

The above is the detailed content of Use VBA to automatically update data to ACCESS in EXCEL. For more information, please follow other related articles on the PHP Chinese website!