Error with OLEDB connection from Excel to Act

Question

I'm creating an Excel file linked to an ACT database using ACT OLE DB provider 2.0 All is working fine (credentials approve etc) until I have to select tables from the ACT database to insert, then it gives me this error;

DataSource.Error: An error happened while reading data from the provider: 'Value cannot be null.
Parameter name: pUnk'
Details:
    DataSourceKind=OleDb
    DataSourcePath=data source="U:\ACTDatabase2011rev3-database files\ACTDatabase2011rev3.pad";provider=ACTOLEDB2.1

I have searched for the error but most answers refer to Visual Studio which I have never used and don't know what it is.
Can someone explain in layman's terms what's wrong here, and what I should do to successfully import the data?

Answer 1

I'm glad I'm not the only one who's had this problem anyway!

In this case I had an excel file from a colleague (an older one made in Excel 2016) that had a working connection, albeit to the wrong table in ACT.
I made a workaround by exporting the connection files (.odc files) of both and comparing them in Notepad, then editing the 'new' connection file to be the 'old' format and somehow miraculously it worked. Still not sure precisely what the issue is, I just changed every single line that was different rather than trying them individually.

And I made a vba macro for editing the .odc files, because I had quite a few to do; see below (password info has been replaced with EnterPasswordHere)

Sub EditConnectionFile()
'MACRO FOR UPDATING BAD ACT CONNECTION FILES

'Get File to be fixed;
Dim FilePath As String
GetSelectFile FilePath, , "*.odc"
If Len(FilePath) = 0 Then
    MsgBox "No File was Selected. Exiting Macro.", vbCritical + vbOKOnly, "Cancelled"
    Exit Sub
End If

'Put file lines into an Array;
Dim LineArray() As String
Call TxtFileToArray(FilePath, LineArray)

'Find Table Name;
Dim TableName As String
For x = LBound(LineArray) To UBound(LineArray)
    If LCase(LineArray(x)) Like "*select * from [[]*]*" Then
        TableName = Right(LineArray(x), Len(LineArray(x)) - InStr(LineArray(x), "["))
        TableName = Left(TableName, InStr(TableName, "]") - 1)
        Debug.Print "Table Name: " & TableName
        Exit For
    End If
Next

'Fixed Line Changes:
Dim arrChange(1 To 4, 1 To 2) As String
    arrChange(1, 1) = "<meta name=ProgId content=ODC.Database>"
    arrChange(1, 2) = "<meta name=ProgId content=ODC.Table>"    'Confirmed
    arrChange(2, 1) = "<meta name=SourceType content=OLEDB>:"   'Confirmed
    arrChange(2, 2) = "<meta name=SourceType content=OLEDB>:" & vbCrLf & _
        "<meta name=Schema content=dbo>" & vbCrLf & _
        "<meta name=Table content=" & TableName & ">"
    arrChange(3, 1) = "odc:PowerQueryConnection"
    arrChange(3, 2) = "odc:Connection"
    arrChange(4, 1) = "CommandType>SQL"     'Confirmed
    arrChange(4, 2) = "CommandType>Table"   'Confirmed

'Custom Property Changes;
Dim arrCustom(1 To 3, 1 To 2) As String
    arrCustom(1, 1) = "title"
    arrCustom(1, 2) = "Title"
    arrCustom(2, 1) = "o:Description"
    arrCustom(2, 2) = "Description"
    arrCustom(3, 1) = "o:Name"
    arrCustom(3, 2) = "Name"
'==============================================================

'Changes to each line;
For a = 1 To UBound(LineArray)
    If Len(LineArray(a)) > 0 Then
        'Complex Changes;
        If LineArray(a) Like "*<script language='javascript'> *" Then
                Exit For 'from this line downwards, old and new files were identical
        ElseIf LineArray(a) Like "*<odc:ConnectionString*" Then
            LineArray(a) = _
            "<odc:ConnectionString>Provider=ACTOLEDB2.1;Data Source=U:\ACTDatabase2011rev3.pad;" & _
            "User ID=Mark Groombridge;Password=EnterPasswordHere;Persist Security Info=True;" & _
            "Initial Catalog=&quot;&quot;;Extended Properties=&quot;&quot;;Location=&quot;&quot;;" & _
            "Mode=ReadWrite;Initial Catalog=(Default)</odc:ConnectionString>"
        ElseIf Trim(LineArray(a)) Like "<odc:PowerQueryMashupData*" Then
            LineArray(a) = "" 'that line needed deleting
        ElseIf LCase(LineArray(a)) Like "*select * from [[]*" & LCase(TableName) & "*]*" Then
            LineArray(a) = "<odc:CommandText>&quot;dbo&quot;.&quot;" & TableName & "&quot;</odc:CommandText>"
        End If
        
        'Fixed Properties;
        For b = LBound(arrChange) To UBound(arrChange)
            LineArray(a) = Replace(LineArray(a), arrChange(b, 1), arrChange(b, 2))
        Next
        
        'Custom Properties;
            'Connection Title, name etc.
        For C = LBound(arrCustom) To UBound(arrCustom)
            If Trim(LineArray(a)) Like "<" & arrCustom(C, 1) & ">*</" & arrCustom(C, 1) & ">" Then
                LineArray(a) = "<" & arrCustom(C, 1) & ">" & _
                InputBox("Enter " & arrCustom(C, 2) & ":", arrCustom(C, 2), _
                Replace(Replace(LineArray(a), "<" & arrCustom(C, 1) & ">", ""), "</" & arrCustom(C, 1) & ">", "")) & _
                "</" & arrCustom(C, 1) & ">"
            End If
        Next

    End If
Next

'------------------------------------------------------------------------------------------------
'Lines now corrected, write back into file
'------------------------------------------------------------------------------------------------
Open FilePath For Binary As #1
For z = LBound(LineArray) To UBound(LineArray)
    Put #1, , LineArray(z) & vbCrLf
Next
Close 1

MsgBox FilePath & " has been fixed, and can now be opened as usual.", vbOKOnly + vbInformation, "Process Complete"

End Sub

Which relies on the below, copied/adapted from the internet;

Sub TxtFileToArray(FilePath As String, LineArray As Variant, Optional LineDelimiter As String = vbCrLf, _
                    Optional TwoDimArray As Variant, Optional ColumnDelimiter As String = vbTab)
'copied from https://www.thespreadsheetguru.com/blog/vba-guide-text-files
Dim TextFile As Integer
Dim FileContent As String
Dim TempArray() As String
Dim row As Long, Col As Long

'Inputs
    row = 0
    
'Open the text file in a Read State
  TextFile = FreeFile
  'Open FilePath For Input As TextFile
  Open FilePath For Binary Access Read As TextFile
  
'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
  Close TextFile
  
'Separate Out lines of data
  LineArray = Split(FileContent, LineDelimiter)

'Read Data into an Array Variable
If Not IsMissing(TwoDimArray) Then
  For x = LBound(LineArray) To UBound(LineArray)
    If Len(Trim(LineArray(x))) <> 0 Then
      'Split up line of text by delimiter
        TempArray = Split(LineArray(x), ColumnDelimiter)
      
      'Determine how many columns are needed
        Col = UBound(TempArray)
      
      'Re-Adjust Array boundaries
        ReDim Preserve TwoDimArray(Col, row)
      
      'Load line of data into Array variable
        For y = LBound(TempArray) To UBound(TempArray)
          TwoDimArray(y, row) = TempArray(y)
        Next y
    End If
    
    'Next line
      row = row + 1
    
  Next x
End If

End Sub

Running these fixed my connections - I then had to double-click the .odc file and they would open in a new excel file. After fixing, I found I could add SQL to the connection in excel (in the connection properties) to filter the incoming data. Presumably I could have left the SQL line in the original files, but I didn't have the time to devleop the macro any further and 'if it ain't broke don't fix it'!!

So I acknowledge it's far from perfect, but it worked for me and I'm putting it here in the hope that someone else can benefit from it too.
Sorry for the long answer post, hope it's helpful to somebody!