Change Linked Table Connection error

[dgriffin]

I've got a text file that the following code reads behind the splash screen to verify/update the linked table connections. For example, this line:

CC Numbers = C:\Files\Db\Lists.mdb

is read and parsed into the sTable (left side) and sFile (right side) variables to be used to set/verify the connection. However, at the line:

tdf.RefreshLink

an error occurs that reads:

'C:\Files\Db\ C:\Files\Db\Lists.mdb' isn't a valid path...

which of course it isn't, even though the following value is displayed when you hold your cursor over the preceding line (preceding tdf.RefreshLink) in debug mode:

tdf.Connect = ";DATABASE=C:\Files\Db\Lists.mdb"

Where is this doubled-up value coming from?

*******************************
Function ResetConnections() As Boolean
On Error GoTo Err_ResetConnections
Dim dbs As Database, rst As Recordset, tdf As TableDef
Dim sLine As String, sFile As String, sTable As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT SysObjects.Connect,_
MsysObjects.Database, MSysObjects.Name from MSysObjects " &_
"WHERE MSysObjects.Type = " & IntAttachedTableType)
If rst.RecordCount <> 0 Then
Open &quot;C:\DPO\DPOsetup.txt&quot; For Input As #1
Do While Not EOF(1)
Line Input #1, sLine
sTable = Left(sLine, InStr(sLine, &quot;=&quot - 2)
sFile = Mid(sLine, InStr(sLine, &quot;=&quot + 1)
rst.MoveFirst
Do While Not rst.EOF
If rst![Name].value = sTable Then
Set tdf = dbs.TableDefs(rst![Name].value)
tdf.Connect = &quot;;DATABASE=&quot; & sFile
tdf.RefreshLink
End If
rst.MoveNext
Loop
dbs.TableDefs.Refresh
Loop
End If
dbs.Close
Reset

Exit_ResetConnections:
Exit Function

Err_ResetConnections:
MsgBox Err.Description
Resume Exit_ResetConnections

End Function
*******************************

Thanks in advance.

Dan

 

[RickSpr]

Connect is a funny property. When you assign a value to it, the value it ends up with isn't the same as the value you assigned. Its Property Let procedure 'massages' the value you give it in different ways, depending on the type of database it refers to.

The correct Connect string to assign to a Jet database is:
drive:\path\filename.mdb
In other words, your statement should just be:
tdf.Connect = sFile
The &quot;;DATABASE=&quot; part will be added by the Property Let procedure.

For more information, see the table at the end of the Connect Property topic in the help file. It's certainly not the clearest documentation ever written, but if you follow it blindly, it works. Rick Sprague

 

[dgriffin]

Gawd, I LOVE programming!!!!!

Well, that seemed to get me past the 'C:\Files\Db\ C:\Files\Db\Lists.mdb' isn't a valid path... error. Thanks for the quick response.

Now I get a 'Couldn't find installable ISAM.' error. Which suggests that Access doesn't know what kind of file it is linking. Which is what I thought the reference &quot;;DATABASE=&quot; was for (inferring MS Access with tha absense of declaring dBase, Paradox, etc).

What now?

(You guys, or gals, are worth your weight in gold!)

Dan

 

[RickSpr]

You're right in your analysis. I gave you bad code. The connect string needs to have a &quot;;&quot; at the end. Change that code line to:
tdf.Connect = sFile & &quot;;&quot;

That will probably do it. Rick Sprague

 

[dgriffin]

Same error message.

'Couldn't find installable ISAM.'

 

[RickSpr]

Well, darn it all, anyway!

Dan, I owe you an apology. I went and checked the code I use to refresh links. Lo and behold, the value I assign to tdf.Connect is &quot;;DATABASE=C:\path\filename.mdb&quot;. I gave you bad advice about using just the filename, and I'm very embarassed about that.

You didn't get past the other error, you're now getting an earlier error because Access is looking for an installable ISAM with the name of your database file.

I got confused. I told you that the property Let procedure massages the value you assign to it. Now I remember that yes, it does so, but it stores the massaged result in the Description property, not the Connect property.

So you need to put your code back to the way it was, because that was right. The only question is, why doesn't it work?

I think I may have the answer to that. Looking at your code again, you take the database path name from the input file starting with the character following the &quot;=&quot;. But the file text you listed appears to have a space before and after the &quot;=&quot;, which would mean your connect string is being set to &quot;;DATABASE= C:\Files\Db\List.mdb&quot;. I'll bet Access is looking at the second character after the &quot;=&quot; in the connect string to see if it's a &quot;:&quot;; if not, it appears that you didn't provide a full path name, so Access inserts the current working directory. If that happens to be C:\Files\Db, the resulting file path would be &quot;C:\Files\Db C:\Files\Db\List.mdb&quot;, which of course is what you had in the message.

One of two things will fix it: (1) Remove the space after the equals sign in the text file, or (2) change the line of code that extracts it to skip the space:
sFile = Trim(Mid(sLine, InStr(sLine, &quot;=&quot + 1))
(The Trim function will remove any leading and trailing spaces, so it won't matter if you have a space--or even multiple spaces--after the &quot;=&quot; sign.)

I hope this works!

Rick Sprague

 

[dgriffin]

BINGO!

Thanks a million for your help. If I had known what I was doing, it would have taken me a while to have figured that one out. As it is, I would have NEVER figured it out without your help.

I hope this makes YOU feel as good as it does for me to finally have made it over this hurdle.

Thanks again.

Dan