I have a CSV file that needs to be imported into Access using a linked table. However, this exceeds the 255 column limit.

This was solved by using some VBA with a button. On press the data is loaded into a linked table. I now need to add some extra code under this to create a copy of the linked table and save it as a local table.

This needs to be done on one press of the button. Below is what i have got currently.

Private Sub cmdImportExcel_Click()
'DoCmd.TransferSpreadsheet acImport, , "tblRawTestData", "C:\Users\jacklythgoe\documents\Access\Test Analyzer\data\TestResultsCopy.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:\Users\jacklythgoe\Documents\Access\Test Analyzer\data\TestResultsCopy.csv", HasFieldNames:=False
'DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tblImport", FileName:="C:\MyData.csv", HasFieldNames:=True

End Sub

CodePudding user response：

You could try something like this:

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

1. Makes a copy of the linked table, 2. Selects the copy, 3. Converts into a lokal table

CodePudding user response：

Not sure if you have all the details in your question.

If you already have solved the issue with the linking the CSV file as a table, then just build a make table query that selects the fields from the linked csv file and run the query from your button click event. It creates a local table for you

Something like this would work:

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"