How do I save access as multiple excel tables?

This answer quotes ChatGPT

Requirements can be realized through a combination of SQL statements and VBA code.

First, the operation to obtain the dbb table only takes one operation, which can be written as an SQL query or stored procedure to facilitate subsequent calls. Assuming the name of the query is query1, you can execute the query in VBA with the following code:

Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "query1"

Next, you need to export the dbb table data into the three sheets in the Excel file. You can use the ADODB library in VBA to connect the Access database and Excel files, query the data in the dbb table, and then write them into different sheets of Excel files. The specific code is as follows:

'连接Access数据库和Excel文件
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\access.accdb;Persist Security Info=False;"
cn.Open

'查询dbb表中的数据
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM dbb", cn

'导出数据到Excel文件
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim xlSheet2 As Excel.Worksheet
Dim xlSheet3 As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet1 = xlBook.Worksheets(1)
Set xlSheet2 = xlBook.Worksheets(2)
Set xlSheet3 = xlBook.Worksheets(3)
xlSheet1.Name = "Sheet1"
xlSheet2.Name = "Sheet2"
xlSheet3.Name = "Sheet3"

Dim i As Long
For i = 1 To rs.RecordCount
    '根据行号写入到不同的sheet中
    If i <= 1000000 Then
        xlSheet1.Cells(i, 1).CopyFromRecordset rs
    ElseIf i <= 2000000 Then
        xlSheet2.Cells(i - 1000000, 1).CopyFromRecordset rs
    Else
        xlSheet3.Cells(i - 2000000, 1).CopyFromRecordset rs
    End If
    rs.MoveNext
Next i

'保存Excel文件
xlBook.SaveAs "C:\path\to\output.xlsx"
xlBook.Close False
xlApp.Quit