problems with SQL connection string

[mazza]

in my application I used to run a code that checked for a valid connection string with the sql server or let me easily change SQL server / databases.
Hwr is suddenly stopped working. All I can think of that has changed was loading XP SP3.

the error I get is at the following line of code
Set tdf = myDB.TableDefs(strSourceTable)
ITEM NOT FOUND IN THIS COLLECTION

As I said it runs perfectly on other machines running windows XP and 2000, just not sure if those running XP also have SP3




Dim myDB As DAO.Database, tdf As DAO.TableDef
Dim strFolderName As String
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
'Dim dbsTemp As DAO.Database
'Dim tdfLinked As TableDef
'Dim strConnect As String
Dim strSourceTable As String
Dim serverName As String
Dim databasename As String

'set the database & servername

Set myDB = CurrentDb()
Set rs = myDB.OpenRecordset("TblConnection", dbOpenDynaset)
serverName = rs!MYServername
databasename = rs!MYDbasename
rs.Close
Set rst = myDB.OpenRecordset("TblRenameLinks", dbOpenDynaset)
rst.MoveFirst
Do While rst.EOF = False
'starter:
'strConnect = strFolderName & "\" & rst("OLD")
strSourceTable = rst("OLD")


Set tdf = myDB.TableDefs(strSourceTable)
Debug.Print tdf.Connect
tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & serverName & ";DATABASE=" & databasename & ";Trusted_Connection=Yes"
tdf.RefreshLink
Debug.Print tdf.Connect
rst.MoveNext
Loop

 

[namliam]

The error seems to indicate that the table comming from your rst TBLRenameLinks doesnt excist in this DB anymore.

 

[mazza]

tks

that was the cause indeed, but can't remember making any changes..
still baffled but its running

 

[namliam]

As an alternative...

Type "For each" in VBA, select it and hit F1 and find the relavent help entry...
You can loop (pretty easily) all (in this case) tables to do work on them.

Same thing you can use for queries.... Have a look it really helps doing this instead of having a table.