Option Compare Database
Option Explicit
Public Function RefreshLinks() As Boolean
On Error GoTo Err_RefreshLink s
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnectStrin g As String, strReadConnectS tring 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.Cur rentProject.Pat h & "\myDsnFileName .dsn"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Set fs = CreateObject("S cripting.FileSy stemObject")
strConnectStrin g = ""
fOdbc = False
Set a = fs.OpenTextFile (strFileName, ForReading, False)
Do While a.AtEndOfStream <> True
strReadConnectS tring = a.ReadLine
If Mid(strReadConn ectString, 1, 1) = "[" Then
If Mid(strReadConn ectString, 1, 6) = "[ODBC]" Then
fOdbc = True
Else
fOdbc = False
End If
Else
If fOdbc Then
If Len(strConnectS tring) > 0 Then
strConnectStrin g = strConnectStrin g & ";" &
strReadConnectS tring
Else
strConnectStrin g = strReadConnectS tring
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;" & strConnectStrin g
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_RefreshLin ks:
Exit Function
Err_RefreshLink s:
MsgBox ("<Some message>")
Resume Exit_RefreshLin ks
End Function
Hope this helps,
--
James Lankford
"KathyK" <ka************ *@nike.com> wrote in message
news:df******** *************** ***@posting.goo gle.com...
"James Lankford" <dr*********@ho tmail.com> wrote in message
news:<vp******* *****@corp.supe rnews.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.goo gle.com... 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
