Option Compare Database
Option Explicit
Public Function RefreshLinks() As Boolean
On Error GoTo Err_RefreshLinks
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnectString As String, strReadConnectString As String, fOdbc As
Boolean
Dim dbTargetDB As DAO.Database
Dim fs, a, retstring
Dim strFileName As String
Dim fLinkFound As Boolean
RefreshLinks = False
' -- If you want to hard code your ODBC link, then you can basically
omit all the code within
' -- Section I, and use a variable to hold your ODBC string for use in
Section II.
' -- Section I --
strFileName = Application.CurrentProject.Path & "\myDsnFileName.dsn"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Set fs = CreateObject("Scripting.FileSystemObject")
strConnectString = ""
fOdbc = False
Set a = fs.OpenTextFile(strFileName, ForReading, False)
Do While a.AtEndOfStream <> True
strReadConnectString = a.ReadLine
If Mid(strReadConnectString, 1, 1) = "[" Then
If Mid(strReadConnectString, 1, 6) = "[ODBC]" Then
fOdbc = True
Else
fOdbc = False
End If
Else
If fOdbc Then
If Len(strConnectString) > 0 Then
strConnectString = strConnectString & ";" &
strReadConnectString
Else
strConnectString = strReadConnectString
End If
End If
End If
Loop
a.Close
Set fs = Nothing
' -- End Section I
' -- Start Section II
' Loop through all tables in the database.
Set dbs = CurrentDb
fLinkFound = False
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
' -- I looked for a specific name in this case because I didn't
necessary want
' -- to refresh all my linked tables, in which I had some linked
..xls and .mdb files.
' -- I was looking for a specific table name. Modify as needed.
If tdf.Name = "<somename>" Then
fLinkFound = True
tdf.Connect = "ODBC;" & strConnectString
Err = 0
On Error Resume Next
' Relink the table.
tdf.RefreshLink
RefreshLinks = (Err = 0)
End If
End If
Next
Set dbTargetDB = Nothing
If fLinkFound = False Then
' -- Do something
End If
' -- End Section II
Exit_RefreshLinks:
Exit Function
Err_RefreshLinks:
MsgBox ("<Some message>")
Resume Exit_RefreshLinks
End Function
Hope this helps,
--
James Lankford
"KathyK" <ka*************@nike.com> wrote in message
news:df**************************@posting.google.c om...
"James Lankford" <dr*********@hotmail.com> wrote in message
news:<vp************@corp.supernews.com>... James,
If it's not too much trouble I would love to see the code!
Kathy
Hi Kathy,
I had this same issue a couple of weeks ago. Just running a routine
to login into the server is not enough. I'm presuming you have linked ODBC
tables? I ended up creating a procedure to basically re-link the tables,
or rather, refresh the link to the tables using a file dsn, and running
that at startup. Since the connection is cached for the session, the users won't
be prompted to logon. The file dsn was just the option I selected, but you
could actually embed the connection string in your module code and not
have a dsn file.
I would copy and paste the code, but I'm at home and my project is at
work. If this gives you a good start - great! If you need a sample to go by,
let me know and I'll paste it in the morning.
--
James Lankford
"KathyK" <ka*************@nike.com> wrote in message
news:df**************************@posting.google.c om... Hi and thanks in advance!
I have an Access 2000 front end and a SQL Server 2000 back end. When
the Access main form opens I run code that logs the user on to the SQL
server using the generic read only log on I have created. On some PCs
I am having a problem. The code runs and then displays a Log On box
with the Trusted Connection checked and the users NT logon displayed.
What causes this? Is there a way to correct it?
Thanks!
Kathy
