Export of tbldef linked to Excel generates syntactically invalid .sql if Excel file is missing

[jhgarrison]

trafficstars

In my application I import data from a website that is downloaded in Excel format. The process is driven from VBA and involves:

1. Run a python script that invokes Selenium to script a website download to a temp file
2. Dynamically relink a tabledef to the downloaded file
3. Import data from the linked Excel file

I recently cleaned out temp files, which left the table link pointing at a no-longer-existing file. Prior to purging temp files, the export produced

$ git show HEAD:tbldefs/OrderImportExcel.sql
CREATE TABLE [OrderImportExcel] (
  [First name] VARCHAR (255),
  [Nick Name] VARCHAR (255),
  .
  .
  .
  [ZIP/Postal] VARCHAR (255)
)

I ran an export after purging temp files, and the output was the following (note the missing open parenthesis)

$ cat tbldefs/OrderImportExcel.sql
CREATE TABLE [OrderImportExcel]
)

This is probably a low impact problem. I'm going to investigate changing my code to not leave potentially dangling linked tables around, which will avoid this issue.

[jhgarrison]

I just noticed that, after the export, Access seems to have created a file in the temp directory matching the missing file name. Its contents are binary and kind of look like it might be .xls format, but Excel refuses to open it.

Here's the output of the cygwin (linux) strings command:

A satisfied Microsoft Office9 User                    B
BN*8
"$"#,##0_);\("$"#,##0\)
"$"#,##0_);[Red]\("$"#,##0\)
"$"#,##0.00_);\("$"#,##0.00\)
"$"#,##0.00_);[Red]\("$"#,##0.00\)
_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_)
_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)
_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)
_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)

Do you generate this or is this something Access does? Let me know if you want the whole file.

[jhgarrison]

I have worked around this by modifying my code to do what it should have done all along.

Instead of depending on an old (now likely invalid), dangling linked table def, I create the link from scratch each time and then delete it when I'm done.

[jhgarrison]

I can see a case for declaring a limitation:

"A DB with broken table links is in an invalid state and not eligible for source export"

I'm fine with you deciding to close this as "WontFix" if you like.

[joyfullservice]

Thanks for posting more details on this. I think the intended behavior for the add-in would be to throw a non-fatal error when attempting to export a linked table that does not exist. I didn't catch from your description exactly what happens when it tries to export the definition for the missing table, but ideally it would be an error that shows up red on the console output, but the export continues to completion.

[jhgarrison]

Nothing unusual happens, the export completes normally with no error indication.

I only caught this because I always review all the diffs in git gui to partition sets of related changes into individual commits. The diff for the TableDef showed that the output was invalid.

[joyfullservice]

That makes sense. Reviewing the code, it looks like it is clsDbTableDef.SaveTableSqlDef where the issue is occurring. A linked table with an invalid data source would (understandably) not have any columns. Perhaps a better way to handle this situation would be to throw a non-fatal error, and skip writing the .sql definition file, since it would not have valid content anyway.

[jhgarrison]

That sounds like a reasonable approach.