Re: Refreshing linked tables

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 02/17/04 

Date: Tue, 17 Feb 2004 15:59:02 +1100

I'm surprised at that -- my information may be out of date.
I see two things that would not have worked when I was
developing our code:

>CurrentDb.TableDefs(rsODBCTables!Name).Connect = strConnect
>CurrentDb.TableDefs(rsODBCTables!Name).RefreshLink

1) We could not use refresh to reliably change an ODBC
connect string.

2) We needed to use a persistent Database object so
that the connect string was not lost when the Tabledef
went out of scope between the first and second line.

Is this code in use now?

(david)

"jmuller" <user@yahoo.com> wrote in message
news:Xns94914BFCBCD9EAnonyman@63.218.45.21...
> 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: return multiple rows from sql statement
    ... Concatening SQL ... strings from input values is almost certainly a safe path to SQL ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Establish connection with and transferring data to Access
    ... If you Google for SQL +"Insert into" you'll a dozen tutorials. ... The specific problem with your code is that you need to quote string ... Dim vConnection As New ADODB.Connection ... Doug's code opens and writes to an existing database. ...
    (microsoft.public.word.vba.general)
  • 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: Random Access Files in databases
    ... I am not a Database design guru either, but it might be wise to break ... location and read in in the string, and then you want to get an integer, so ... I'm pretty sure the SQL language does have ways to seek around in BLOB ... some SQL purist are quick to point out that MySQL doesn't implement the SQL ...
    (comp.lang.java.programmer)
  • Problems Setting Up Database
    ... I seem to be having errors creating and accessing an SQL database. ... New SQL Server Database'. ... System.Web.Management.SqlServices.ExecuteFile(String file, String server, ...
    (microsoft.public.dotnet.framework.aspnet)