OK. I solved the problem. The problem was in the SetIMEX2 function.
I changed:
Set tdf = DBEngine(0)(0).TableDefs(strTbl)
to:
Set tdf = dbLocal.TableDefs(strTbl)
and everything worked fine.
Perhaps one of you geniuses out there can elucidate as to why
DBEngine(0)(0)
would keep re****ting the old connect string from the dropped link table,
while dbLocal (CurrentDB) would correctly be re****ting the new path and
filename.
ragtopcaddy wrote:
>I have the following lines of code:
>
>This line deletes the link to an excel workbook. I presumed that any
vestige
>of it's tabledef.connect string would be gone as well.
> DropTable lclName
>
>This line recreates the same local name but links it (successfully: I
checked
>the data) to a workbook with a different name and in a different
directory:
>
>DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, lclName,
fName,
>True, rngName
>
>but, at this point, in trying to edit the connect string to IMEX 1, the
>connect string returns the old workbook name and path and the function
craps
>out (3044, Not a valid path):
>
>SetIMEX2 1, lclName
>
>Here's the function:
>
>Function (nVal As Integer, strTbl As String)
>'Changing the IMEX value from 2 to 1 will allow clean records from a
linked
>Excel file,
>'provided the registry key (Hkey_Local_Machine/Software/Microsoft/Jet/4.
>0/Engines/Excel/)
>'TypeGuessRows is set to 0.
>
>Dim tdf As TableDef
>
>Set tdf = DBEngine(0)(0).TableDefs(strTbl)
>tdf.Connect = Replace(tdf.Connect, "IMEX=2", "IMEX=" & nVal)
>-------------here's where it craps out
>tdf.RefreshLink
>
>End Function
>
>So I tried to edit the connect string with the following line, but it has
no
>effect. The tdf.connect string still reflects the path and filename of
the
>dropped table.
>
>dbLocal.TableDefs(lclName).Connect = "Excel 8.0;HDR=YES;IMEX=1;DATABASE="
&
>strXLDir & .Fields("WkbkName")
>
>How is this even possible? And how do I get around it?
--
Bill Reed
"If you can't laugh at yourself, laugh at somebody else"
Message posted via http://www.accessmonster.com
|
