动态创建Microsoft Access SQL查询

I need to link to circa 270 (amount can change) .txt files and create a select query on all of them. I can import them all at once no problem, but as Access can only handle 32 tables per select query I need some way to create a variable (on the fly?) which will hold a SQL string which will then be used as a parameter in a create query def function. Does anyone have any ideas. I'm thinking I need to use a class, but is there an easier way?

我需要链接到大约270(金额可以更改).txt文件并在所有这些文件上创建一个选择查询。我可以立即导入它们没有问题,但是因为Access每个选择查询只能处理32个表我需要一些方法来创建一个变量(动态?),它将保存一个SQL字符串,然后将其用作参数创建查询def函数。有没有人有任何想法。我想我需要使用一个班级,但有更简单的方法吗?

1 个解决方案

#1

My approach in a similar situation is to put all of the input files in a common folder, with nothing else in that folder. Then I use the following vba script to process all the files. For each file, I add the file name to a directory table (".import files") and the data to a second table (".import data"). The "source" field in the data is the link to the "Index" field of the files list so I know which data came from each source file. It doesn't matter how many files are in the folder. (If you prefer, you can change one of the two tables so these 2 fields have the same name if I had been thinking ahead more, I would have done so.)

我在类似情况下的方法是将所有输入文件放在一个公共文件夹中,该文件夹中没有其他内容。然后我使用以下vba脚本来处理所有文件。对于每个文件,我将文件名添加到目录表(“.import files”),将数据添加到第二个表(“.import data”)。数据中的“源”字段是指向文件列表“索引”字段的链接,因此我知道哪些数据来自每个源文件。文件夹中有多少文件无关紧要。 (如果你愿意,你可以改变这两个表中的一个,这样如果我一直在考虑更多这两个字段具有相同的名称,我会这样做。)

My input files aren't particularly well formatted (and I cannot control the data source), but at least they are all in the same poor format. Specifically, there are not field names in the first row of the data. That's why I import them to a temporary file, and in the "insert into" command has fields in the source table like F1, ... F9. You'll need to change the lines that build the SQL "Insert into" statement to match your file formats.

我的输入文件格式不是很好(我无法控制数据源),但至少它们都是同样糟糕的格式。具体而言,数据的第一行中没有字段名称。这就是我将它们导入临时文件的原因,而在“insert into”命令中,源表中的字段如F1,... F9。您需要更改构建SQL“插入到”语句的行以匹配您的文件格式。

You will also need to create the directory and data files before you run the code - you can create the data file by importing one of the source files as a template, then editing it to add the "Source" field. The 3 fields in the ". import files" table are Index (which is an auto-number field), file name and update date.

您还需要在运行代码之前创建目录和数据文件 - 您可以通过将其中一个源文件导入为模板来创建数据文件,然后对其进行编辑以添加“源”字段。 “.import files”表中的3个字段是Index(自动编号字段),文件名和更新日期。

When you are done with this, you'll have two tables that you can join on "source" (from .import data) and "index" (from .import files). Then you can continue with whatever processing you require. You can select on "Source" to look at a specific input file, or group by "Source" to summarize the data by input, or ignore "Source" all together to get a summary of all of your data.

完成此操作后,您将有两个表可以加入“source”(来自.import数据)和“index”(来自.import文件)。然后,您可以继续进行所需的任何处理。您可以选择“源”以查看特定输入文件,或按“源”分组以按输入汇总数据,或者忽略“源”以一起获取所有数据的摘要。

    Function Import_BTT()

    'On Error Resume Next
    DoCmd.SetWarnings False

    'Get folder & file list
    Source_folder = Get_Folder()
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set flist = fso.GetFolder(Source_folder).Files

    'delete prior data
        Set DB = CurrentDb()
        DB.Execute ("delete * from [book to tax import files];")
        DB.Execute ("delete * from [book to tax import data];")

    'Process each file
    For Each file In flist
        If (Len(Dir(Source_folder & "\" & file.Name)) = 0) Then GoTo NextFile

        'add new data
        DB.Execute "insert into [Book to Tax import files] ([file name],[update date]) values (" & "'" & file.Name & "',#" & file.DateLastModified & "#);"
        Source = DB.OpenRecordset("SELECT @@IDENTITY")(0)
        DoCmd.TransferText , , "Book to Tax import Temp", Source_folder & "\" & file.Name

        SQL = "INSERT INTO [Book to Tax Import data] (source, Account, [Book Balance], [Book Adjustments], [Adjusted Book Balance], [Tax Reclass], [Balance After Tax Reclass], [Tax Adjustments], [Historical tax Adjustments], [Tax Balance] )"
        SQL = SQL & "select '" & Source & "',mid(F1,instr(f1,'(')+1,10),F2,F3,F4,F5,F6,F7,F8,F9 FROM [Book to Tax import temp] where ((trim(f1) like 'BK (*') or (trim(f1) like 'TAX (*'));"

        DoCmd.RunSQL SQL
        DoCmd.DeleteObject acTable, "Book to Tax Import Temp"

    NextFile: Next file

    MsgBox ("data import completed")
    DoCmd.SetWarnings True

    End Function

Public Function Get_Folder()

'Create a FileDialog object as a Folder Picker dialog box.
Const msoFileDialogFolderPicker = 4
Const msoFileDialogFilePicker = 3
Const msoFileDialogViewDetails = 2

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.AllowMultiSelect = False
fd.ButtonName = "Select"
fd.InitialView = msoFileDialogViewDetails
fd.Title = "Select Folder"
fd.InitialFileName = "MyDocuments\"
fd.Filters.Clear

'Show the dialog box and get the file name
If fd.Show = -1 Then
    Get_Folder = fd.SelectedItems(1)
    Else
    Get_Folder = "MyDocuments\"
    End If

Set fd = Nothing
End Function