Connect string doesn't update after transferspreadsheet   

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 SetIMEX2(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?

-- 
Message posted via http://www.accessmonster.com

date: Fri, 18 Jul 2008 16:45:39 GMT   author:   ragtopcaddy via AccessMonster.com u9289@uwe

Re: Connect string doesn't update after transferspreadsheet   

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 reporting the old connect string from the dropped link table,
while dbLocal (CurrentDB) would correctly be reporting 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

date: Fri, 18 Jul 2008 17:13:29 GMT   author:   ragtopcaddy via AccessMonster.com u9289@uwe