Re: Access Refresh Links (access 2002)

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Harp,

It looks as if you're dumping the last part of the connection string.
Something like this air code should do the job:

Dim strOldPath As String
Dim strNewPath As String
Dim lngPos As Long

strOldPath = "\\LON0302\blah blah"
strNewPath = "Q:"

...
For Each tbl ...
lngPos = InStr(strOldPath, tbl.Connect)
If lngPos > 0 Then
tbl.Connect = _
Left(tbl.Connect, lngPos - 1) _
& strNewPath _
& Mid(tbl.Connect, lngPos + Len(strOldPath) +1)
Else
'tbl is connected to something else
End If
...

On Tue, 24 May 2005 07:51:09 -0700, Harp Binning
<HarpBinning@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>Help,
>
>Refreshing Text Link in VB & VBA
>In my VB App or inside a VBA code how can I refreshing the table link to an
>external text file without receiving the error:
>
>Runtime Error '3625': The text file specification 'xxx' does not exist. You
>can't import, export, or link using the specification.
>
>The code I am typing in is :
>
>For Each tbl In CurrentDb.TableDefs
> If tbl.SourceTableName <> "" Then
> strOldCon = tbl.Connect
> For I = 1 To Len(strOldCon)
> If UCase(Mid(strOldCon, I, 29)) = "\\LON0302\DFS\DATA\DTA\FMAN_Q"
>Then
> strNewCon = strNewCon & "Q:"
> I = I + 28
> Else
> strNewCon = strNewCon & Mid(strOldCon, I, 1)
> End If
> Next
> If strNewCon <> strOldCon Then
> 'MsgBox strOldCon & vbCrLf & strNewCon
> tbl.Connect = strNewCon
> tbl.RefreshLink
> End If
> strOldCon = ""
> strNewCon = ""
> End If
>Next
>
>Thanks for any help
>Harp

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Relevant Pages

  • Re: Best way to store a connection string machine wide.
    ... 'variable to store the path of the connection string ... Dim mStrConStringPath As String ... Dim xmlDoc As New System.Xml.XmlDocument ... Dim TripleDes As New TripleDESCryptoServiceProvider ...
    (microsoft.public.dotnet.framework)
  • Re: Cant get SQL INSERT code to work
    ... Dim objConn As ADODB.Connection ... Dim szConnect As String ... ' You 'll need to modify the connection string Data Source to point to ... 'Excel file and modify the SQL statement to fit your data. ...
    (microsoft.public.access.queries)
  • Re: Determining last row/column using ADO with closed workbook
    ... >Dim strLastColName As String ... >Dim vntLastColLastval As Variant ... >Dim Con As Object ... >' Build connection string ...
    (microsoft.public.excel.programming)
  • Re: Closing connection
    ... Public Function Firstrecord(ByVal userid As String, ... Dim todaynumber As String ... 'This is used to setup the connection string to write the ... the closing of any lock files could be an issue. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: ADO and filter
    ... Sub GetData_Example_EstadisticasDatos ... Dim strDate As String ... ' Create the connection string. ...
    (microsoft.public.excel.programming)