使用VBA将链接表创建为本地表

Question

我有一个CSV文件，需要使用链接表导入Access。但是，这超过了255列的限制。

这是通过使用一些带有按钮的VBA解决的。按下按钮后，数据被加载到一个链接表中。我现在需要在下面添加一些额外的代码来创建链接表的副本，并将其保存为本地表。

这需要一次按下按钮。以下是我目前得到的。

Private Sub cmdImportExcel_Click()
'DoCmd.TransferSpreadsheet acImport, , "tblRawTestData", "C:UsersjacklythgoedocumentsAccessTest AnalyzerdataTestResultsCopy.csv", True, Range:="TestResultsCopy!A:C"

' Requires reference to Microsoft Office 11.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim testResultsWorkSheet As Worksheet, strFile As String

' Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog

' Allow user to make multiple selections in dialog box
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Please select the font(s)."

' Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Text File", "*.csv"

' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then

'Loop through each file selected and add it to our list box.
For Each varFile In .SelectedItems
Next

End If
End With
DoCmd.TransferText TransferType:=acLinkDelim, tableName:="tblImport", FileName:="C:UsersjacklythgoeDocumentsAccessTest AnalyzerdataTestResultsCopy.csv", HasFieldNames:=False
'DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tblImport", FileName:="C:MyData.csv", HasFieldNames:=True
End Sub

Answer 1

你可以尝试这样做:

DoCmd.CopyObject , "tblImport_Copy", acTable, "tblImport"
DoCmd.SelectObject acTable, "tblImport_Copy", True
DoCmd.RunCommand acCmdConvertLinkedTableToLocal

1. 复制链接表，2。选择副本，3。转换为本地表

Answer 2

我不确定你的问题是否包含了所有的细节。

如果您已经解决了将CSV文件链接为表的问题，那么只需构建一个make table query，从链接的CSV文件中选择字段，并从按钮单击事件中运行查询。它为您创建了一个本地表

这样就可以了:

Dim dbs As DAO.Database
Dim lngRowsAffected As Long
Dim lngRowsDeleted As Long
Set db = CurrentDb
' Execute runs both saved queries and SQL strings
db.Execute <mymaketablequery>, dbFailOnError
' Get the number of rows inserted. 
lngRowsAffected = db.RecordsAffected
Msgbox "Inserted " & lngRowsAffected & " new records"