Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: DoCmd TransferSpreadsheet Different Field Names

    Hi all,

    I'm trying to use Transferspreadsheet to get data from an Excel spreadsheet to a table in Access.
    Problem is, the spreadsheet has different field names than the table.

    Is there any way to get this to work without changing the names of the fields in the spreadsheet?
    Currently errors out and tells me that the first field name in the spreadsheet does not exist in the table.

    thx
    w

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You can always import the data in a temporary table, then use an INSERT query to transfer the data from the temporary table to the destination table.
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sinndho,

    I'm now trying to import to a tmp table and then appending to my main table.
    I am receiving a error at the TransferSpreadsheet snippet of the Sub():

    Run-time error '3035':
    System resource exceeded.
    Any thought on why that is happening?

    thanks
    w

    Code:
    Sub TransferSpreadsheetUsingFunctions()
     
        'Purpose: Transfer Excel Worksheet to Access Table
        'Log    :
        '---------------------------------------------------------------------------------------------------------------------------------------------------
        'Date               Developer                   Action                      Comments
        '---------------------------------------------------------------------------------------------------------------------------------------------------
        
        'Declare variables
            Dim strFullFileName             As String
            Dim strPath                     As String
            Dim strFileNameSubstring        As String
            Dim sql                         As String
            
        'Declare constants
            Const strTableName As String = "tblMetrics"
            Const strTempTable As String = "tmpMetrics"
            
        'Initialize variables
            strFullFileName = GetFDObjectName()             'File for import
            sql = "INSERT INTO " & strTableName
            sql = sql & "SELECT * FROM " & strTableName & ";"
            
        'Initialize environment
            With DoCmd
                .SetWarnings False
            
                'Clear previous data
                    DoCmd.OpenQuery ("qry_del_tblMetrics")
    
                'Transfer spreadsheet to temp table
                    .TransferSpreadsheet _
                        TransferType:=acImport, _
                        TableName:=strTempTable, _
                        FileName:=strFullFileName, _
                        HasFieldNames:=True
                        
                'Append data from tmp table to final table
                    .RunSQL sql
    
                'Tidy up
                    'Drop tmp table
                        .DeleteObject acTable, "tmpMetrics"
            
                    'Turn warnings on
                        .SetWarnings True
                    
            End With
         
    End Sub
    FileDialog Function:
    Code:
    Option Explicit
    
    Public Function GetFDObjectName(Optional ByVal strDialogType As String) As String
         
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'GetFDObjectName
        'Returns either the name of a folder or the name of a file based on the type passed into the function, "strDialogType"
        'Return is a value of data type string
        '
        'Parameters        :
        'strDialogType     :   Required, A string. If "Folder" then the FileDilaog will be msoFileDialogFolderPicker,
        '                      otherwise msoFileDialogFilePicker
        '
        'Uses these global constants
        '  Public Const gclmsoFileDialogFilePicker = 3                 'File Picker
        '  Public Const gclmsoFileDialogFolderPicker = 4               'Folder Picker
        '  Public Const gclmsoFileDialogOpen = 1                       'Open
        '  Public Const gclmsoFileDialogSaveAs = 2                     'SaveAs
        '
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        
        'Declare variables
            Dim fd As Object
            Dim strObjectName As String
            Dim strTitle As String
              
        'Choose if user requested a folder dialog or other
            Select Case strDialogType
                Case "Folder"                                                               'Folder Dialog
                    strTitle = "Please select a folder"
                    Set fd = Application.FileDialog(gclmsoFileDialogFolderPicker)
                Case Else
                    strTitle = "Please select a file"                                       'File Dialog
                    Set fd = Application.FileDialog(gclmsoFileDialogFilePicker)
            End Select
              
        'Invoke filedialog
            With fd
                .Title = strTitle
                .AllowMultiSelect = False
                .Show
                strObjectName = .SelectedItems(1)
            End With
              
        'Pass value to function
            GetFDObjectName = strObjectName
          
        'Tidy up
            Set fd = Nothing
              
    End Function

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Are there many lines to import from the Excel sheet ?

    If it's the case, the error could be due to the value of the MaxLocksPerFile Registry key . In the windows registry, search for an occurence of "MaxLocksPerFile". On my system, it's located in HKLM\Software\Wow6432Node\Microsoft\Jet\3.5\Engine \Jet 3.5. The value was 9500 but I changed it to 50000 to be able to import data from huge external files.

    As usual, be careful when editing the Registry and take a copy of the hive you're going to edit before doing so.
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sinndho,

    I edited MaxLocksPerFile and changed the value to 50000. I then rebooted. I am receiving the same error.
    The file is in .xlsb format. It contains 201K records.

    thx
    w

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    200.000 rows is a lot. I gues you could try to increase the value of the MaxLocksPerFile but I don't know whether there is a maximum value or not (see: https://support.microsoft.com/en-us/kb/815281).

    Personally, I'd probably try another method to import the data : through an ODBC connection or by opening the file in a COM+ instance of Excel. That would probably be slower but there would be virtually not limits (except the max. size of a table in Access).
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •