For Next Loop to update linked tables hosted on remote SQL Server

[mrsbean]

I usually need help with For Next ... always have trouble with it, and I'm in a bit over my head with the business of linking the tables to SQL Server.

I found an article which told me how to form a DNSless connection for a linked table at:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;892490

I want to take it one step further and have the code loop through each linked table in my MDB file. I don't want to repeat the code for each and every table individually.

Help is always appreciated. Thanks in advance.

MrsBean

*************************************
Here's what I have so far.

Public Sub relink_tables()
   Dim stLocalName As Variant
   Dim stLocal As String
   Dim rstTemp As Recordset

     
     Set rstTemp = CurrentDb.OpenRecordset("qryConnections", dbOpenForwardOnly)
        stLocalName = rstTemp("Connect")
        stLocal = rstTemp("Connect")

'SQLHostAddress ... XXX.XXX.XXX.XXX format, SQL Server IP Address
'SQLDatabaseName ... Your database name hosted on the server
'userName ... Your user name for accessing the SQL Server
'userPassword ... Your user password for accessing the SQL Server
    

For Each stLocal In rstTemp
If AttachDSNLessTable(stLocal, "dbo." & stLocal, SQLHostAddress, SQLDatabaseName, userName, userPassword) Then
'all is okay
End If
 
    Next
    

rstTemp.Close

End Sub

 

[PHV]

A starting point here:

http://www.mvps.org/access/tables/tbl0009.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886