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
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!