Re: Refreshing linked tables

From: jmuller (user_at_yahoo.com)
Date: 02/16/04 

Date: 16 Feb 2004 12:26:29 GMT

Subject: Re: Refreshing linked tables
Newsgroups: Newshosting:microsoft.public.vb.database
To: david epsom dot com dot au <david@epsomdotcomdotau>

If it's just matter of refreshing a few tables that have the same schema,
you can use something like this to create DSN-less ODBC links in your
Access database (requires DAO 3.6 object reference and assumes there are
existing links from your original database):

relink2 "sqlbox","databasetolink","validUser","validPassword"

Public Sub reLink2(pstrServer As String, pstrDatabase As String, pstrUID
As String, pstrPWD As String)
    On Error Resume Next
    Dim strConnect As String
    Dim rsODBCTables As Recordset
            ' msysobject type 4 is ODBC linked, type 6 is linked access tables
    Set rsODBCTables = CurrentDb.OpenRecordset("select * from msysobjects
where type = 4", dbOpenSnapshot)
    strConnect = "ODBC;DRIVER={SQL Server}" _
               & ";SERVER=" & pstrServer _
               & ";DATABASE=" & pstrDatabase _
               & ";UID=" & pstrUID _
               & ";PWD=" & pstrPWD & ";"
    Do While Not rsODBCTables.EOF
        CurrentDb.TableDefs(rsODBCTables!Name).Connect = strConnect
        CurrentDb.TableDefs(rsODBCTables!Name).RefreshLink
        rsODBCTables.MoveNext
    Loop
End Sub

I believe the username, password parameters would be unecessary if SQL
uses integrated NT authentication. You will need to compact the db
afterwards, as the relink and refresh makes for very puffy databases.

John
jmuller at iname dot com

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:eM1qZDD9DHA.3348@TK2MSFTNGP09.phx.gbl:

> Don't know without more explicit information, but you can't
> normally change an ODBC link by using 'refresh'.
>
> Instead, you have to create a new link and delete the old one.
>
> You can 'refresh' from an MDB to an ODBC link, or from
> an MDB link to a different MDB link.
>
> If you are using 'refresh', have another look around and
> find some code that creates fresh links.
>
> (david)
>
>
> "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in
> message news:s4c2205n2ru3kt125u33up2q1d5e0bn57r@4ax.com...
>> On Wed, 4 Feb 2004 09:16:10 -0800, "sr"
> <anonymous@discussions.microsoft.com> wrote:
>>
>> ¤ Hi, we have a companion program to another company's major app, and
>> we
> have our own little Access database with our own tables and some
> Linked ODBC tables imported over from that company's database (they're
> using SQL Server). Anyway, when we install our utility programs at
> client sites, we always get a connection error when we run our utility
> as a result of those linked tables - what we've done was just
> re-imported the corresponding tables from the other company's database
> and it all works. Now, I've seen some code around the 'net that uses
> refreshes links but they don't work in our case with the Linked ODBC
> tables. Can you offer some suggestions? I've never set up an SQL
> Server database or know much about networking so I don't fully
> understand what's going on in the background.
>> ¤ Thanks. I posted this in the ODBC section but am not sure where it
> really fit it and I didn't get any response from there.
>>
>> What is the error you are receiving and what is it that your code is
>> doing
> when you receive the
>> error?
>>
>>
>> Paul ~~~ pclement@ameritech.net
>> Microsoft MVP (Visual Basic)
>
>
>

Relevant Pages

  • Re: Refreshing linked tables
    ... We could not use refresh to reliably change an ODBC ... We needed to use a persistent Database object so ... that the connect string was not lost when the Tabledef ... password parameters would be unecessary if SQL ...
    (microsoft.public.vb.database)
  • Re: Inlavid Operation ereror when refresh table link
    ... I also use it to refresh my links. ... Dim strTblName As String, strConn As String ... Dim db As DataBase, rs As Recordset, tbl As TableDef ...
    (comp.databases.ms-access)
  • Re: Refresh questions
    ... submitted item repeats each time the refresh is clicked. ... > in my database that stored a submission ID and checking it when the page ... > a hidden field in the form. ... I allowed the submission to be inserted into the ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Refresh questions
    ... submitted item repeats each time the refresh is clicked. ... in my database that stored a submission ID and checking it when the page is ... a hidden field in the form. ... I allowed the submission to be inserted into the ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Me.Refresh Kills Access
    ... I never use Refresh, Matt, but I suspect the issue is symptomatic of another problem, such as a Name AutoCorrect bug or a complied code error. ... Compact the database to get rid of this junk: ... Still in the code window, choose Compile from the Debug menu. ...
    (microsoft.public.access.formscoding)
Quantcast