如何在超过255字段限制的access中组合表？

Question

access中有没有合并表格的功能，当合并结果大于255个字段限制时，可以将表格输出到excel

Answer 1

考虑将255列限制以下的查询拆分为多个查询(都是相同的结构)。然后，将查询作为VBA记录集打开，以便在特定范围内复制到Excel工作簿中以进行连续输出，特别是使用CopyFromRecordset方法。

下面是一个通用的Access VBA解决方案，假设有两个查询，每个查询有200列。根据需要调整详细信息。您可以在OpenRecordset()中使用SQL语句，而不是命名对象。

Dim xlApp As Object, xlWkb As object
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim fld1 As Field, fld2 As Field
Dim i As Integer
Const xlOpenXMLWorkbook = 51

Set db = CurrentDb
Set rst1 = db.OpenRecordset("query1", dbOpenDynaset)  ' FIRST 200 COLUMNS
Set rst2 = db.OpenRecordset("query2", dbOpenDynaset)  ' NEXT 200 COLUMNS

Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.Workbooks

' COLUMNS
i = 1
For Each fld1 in rst1.Fields
   xlWkb.Sheets(1).Cells(1, i) = fld1.Name
   i = i + 1
Next fld1

 ' DATA ROWS
xlWkb.Sheets(1).Cells(2, 1).CopyFromRecordset rst1


' COLUMNS
i = 0
For Each fld2 in rst2.Fields
   xlWkb.Sheets(1).Cells(1, 201 + i) = fld2.Name
   i = i + 1
Next fld2

 ' DATA ROWS
xlWkb.Sheets(1).Cells(2, 201).CopyFromRecordset rst2

xlWkb.SaveAs "C:\Path\To\Workbook\AccessData.xlsx", xlOpenXMLWorkbook
xlApp.Visible = True

rst1.Close: rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing    
Set db = Nothing

Set xlWkb = Nothing
Set xlApp = Nothing