将大桌子分成较小的桌子以供出口 [英] Breaking a large table into smaller ones for export

问题描述

我在MS Access中有一个很大的表，该表超出了Excel可以容纳的行数.由于许可问题，我们不能将访问权限用作外部数据源，但是可以使用Excel来存储表，直到可以使用几个月后才可以使用SQL Server.

I have a very large table in MS Access which exceeds the amount of lines Excel can hold. Due to licensing issues, we cannot use access as an external data source, we can, however use Excel to store the tables until we can use SQL Server which is coming in several months.

我的任务是将桌子分成几个国家.

I have been tasked with breaking up the table into countries.

查询查询以获取所有不同的国家/地区是没有问题的.

Getting a query for getting all the distinct countries is no problem.

Select distinct Country_Code
from AllCountries

但是然后我需要为每个国家/地区获取一个结果集.

But then I need to get a result set for each country.

在去这里之前，我曾去过多个站点，试图弄清楚这一点. 我知道这是一个基本问题，不幸的是，中风和距离赛场10年的时间让我感到生锈.

I've been to several sites trying to figure this out before coming here. I know it's a basic question, unfortunately, a stroke and 10 years away from the field has left me rusty.

我需要创建这些较小的表0r结果集并将其导出.我迷失了这个.

I need to create these smaller tables 0r result sets and export them. I'm lost on this one.

推荐答案

像这样吗?

PUBLIC FUNCTION exportFiles() AS Boolean
    Dim db AS DAO.DATABASE
    Dim qdf AS DAO.QueryDef
    Dim rsRptGroup AS DAO.Recordset
    Dim sSQL AS String
    Dim sRptGroup AS String
    Dim sPath AS String

    Const sQryExport AS String = "qryExport"

    sPath = Application.CurrentProject.PATH

    SET db = CurrentDb
    SET qdf = db.CreateQueryDef(sQryExport, sSQL)
    qdf.NAME = sQryExport

    ' Get list of labeler values
    sSQL = "SELECT DISTINCT labeler FROM qry_export"
    Set rsRptGroup = db.OpenRecordset(sSQL, dbOpenDynaset, dbReadOnly)

    ' Now LOOP THROUGH list OF labeler VALUES AND CREATE a QUERY FOR EACH labeler
    ' so that the data can be exported
    Do While Not rsRptGroup.EOF
        sRptGroup = rsRptGroup("labeler")
        sSQL = "SELECT * FROM qry_export WHERE labeler = '" & sRptGroup & "'"
        qdf.sql = sSQL

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sQryExport, sPath & "\xls\" & sRptGroup & ".xls"

        rsRptGroup.MoveNext
    Loop

    rsRptGroup.Close
    Set rsRptGroup = Nothing    
    db.Close
    Set db = Nothing    
    exportFiles = True
End Function

这篇关于将大桌子分成较小的桌子以供出口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！