Unavailable Linked Table during Export

[AZDeveloper777]

trafficstars

Discussed in https://github.com/joyfullservice/msaccess-vcs-addin/discussions/416

^{Originally posted by AZDeveloper777 July 28, 2023} I'm using Access 2010 with v4.0.15-beta. I have linked tables in my applications MDB that are unavailable in my development environment. When I click "Export Source Files" in the ribbon bar and it gets to the "Exporting tables ... " section, I get "Run-time error '68': Device unavailable". I did a bit of debugging and it is failing in GetUncPath(strPath As String) on the line With FSO.GetDrive(strDrive). This makes sense as the drive letter doesn't exist on my development machine. Is there a way to make the tool skip over linked tables that aren't available ?

Here is my fix.
Private Sub IDbComponent_Export(Optional strAlternatePath As String)

Dim strFile As String
Dim dbs As Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim dItem As Dictionary
Dim strHash As String
Dim strContent As String

On Error GoTo IDbComponent_Export_Error 'Get the export file name strFile = Nz2(strAlternatePath, IDbComponent_SourceFile)

' For internal tables, we can export them as XML.
If Not IsLinkedTable Then

    ' Save structure in XML format
    VerifyPath strFile
    Perf.OperationStart "App.ExportXML()"
    ' Note that the additional properties are important to accurately reconstruct the table.
    Application.ExportXML acExportTable, m_Table.Name, , strFile, , , , acExportAllTableAndFieldProperties
    Perf.OperationEnd

    ' Rewrite sanitized XML as formatted UTF-8 content
    strHash = SanitizeXML(strFile, True)

Else
    ' Linked table - Save as JSON
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(m_Table.Name)
    
    Set dItem = New Dictionary
    With dItem
        .Add "Name", m_Table.Name
        .Add "Connect", SanitizeConnectionString(tdf.Connect)
        .Add "SourceTableName", tdf.SourceTableName
        .Add "Attributes", tdf.Attributes
        ' indexes (Find primary key)
        If IndexAvailable(tdf) Then
            For Each idx In tdf.Indexes
                If idx.Primary Then
                    ' Add the primary key columns, using brackets just in case the field names have spaces.
                    .Add "PrimaryKey", "[" & MultiReplace(CStr(idx.Fields), "+", vbNullString, ";", "], [") & "]"
                    Exit For
                End If
            Next idx
        End If
    End With

    ' Write export file.
    strContent = BuildJsonFile(TypeName(Me), dItem, "Linked Table")
    strHash = GetStringHash(strContent, True)
    WriteFile strContent, strFile

End If

' Additional processing when exporting to source folder
If strAlternatePath = vbNullString Then

    ' Remove any alternate source file in case we have switched formats
    RemoveAlternateFormatSourceFile

    ' Optionally save in SQL format
    If Options.SaveTableSQL Then
        Log.Add "  " & m_Table.Name & " (SQL)", Options.ShowDebug
        SaveTableSqlDef m_Table.Name, IDbComponent_BaseFolder
    End If
End If

' Update index
VCSIndex.Update Me, IIf(strAlternatePath = vbNullString, eatExport, eatAltExport), strHash

IDbComponent_Export_Exit: Exit Sub

IDbComponent_Export_Error: If Err.Number = 68 Then 'Drive not available GoTo IDbComponent_Export_Exit Else Resume Next End If End Sub

[joyfullservice]

@AZDeveloper777 - Gracefully handling the error from a missing drive should be pretty simple... The other question about skipping certain objects is a bit more involved. That being said, I think it is a very legitimate scenario where a development machine may not have access to all of the network resources required for a full export.

Of course we could just handle the error and move on, but some connections like remote SQL tables might take several seconds to time out, causing a significant delay on every export operation. For cases like this I could see the value of having a way to exclude certain objects, but only on certain machines. We wouldn't want to save this in vcs-options.json, since that is replicated to each development computer, but I could see this as being saved to a .env file that is specific to that one machine, and not checked into version control.

I recently implemented .env support as a part of #415 and this might be a good use case for implementing a way to exclude certain objects. Initially I am thinking of a line Exclude= and set the value to a JSON array of objects. My hesitation is that requiring users to hand-edit JSON arrays could be asking for trouble. We could add some UI elements for selecting objects for exclusion, but that's a bit of work to put something like that together for a rarely-needed feature...

Open to suggestions... 😄

[AZDeveloper777]

I think skipping linked tables that aren't ODBC and aren't available would be a good compromise. I'm pretty sure that my error handling for Err.Number = 68 accomplishes that since an ODBC table shouldn't generate that number if it isn't available.