ACCESS FEED: Re: [MS_AccessPros] Changing links to multiple back ends

Rabu, 22 Agustus 2012

Re: [MS_AccessPros] Changing links to multiple back ends

John,

Thanks. I couldn't find it in you IO book (2003 version). I'll try it when I get in, later this morning.

Adam

P.S. I'm still in the .mdb world.

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Adam-
>
> The Connect property is really simple for a linked table and usually looks like
> this:
>
> ;DATABASE=D:\MyDatabases\ContactsData.accdb
>
> strFileName = Mid(tdfLinkedTable.Connect, InstrRev(tdfLinkedTable.Connect, "\")
> + 1)
>
> strFilePath = Mid(tdfLinkedTable.Connect, 11) ' Skip over ;DATABASE=
>
> strPath = Left(strFilePath, InstrRev(strFilePath, "\"))
>
> So, in a TableDef object named tdfLinkedTable, the above code will give you the
> file name in strFileName and the old path in strPath. If you want to point to
> the same file name in a different path, then do:
>
> tdfLinkedTable.Connect = ";DATABASE=" & strNewPath & strFileName
> tdfLinkedTable.RefreshLink
>
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of AdamF
> Sent: Wednesday, August 22, 2012 2:48 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Changing links to multiple back ends
>
> John,
>
> I don't want/need to ask them to specify a path. First, they don't really need
> to know the file names (I want to make this automatic and hidden from them).
> Second, I know the file names and location. Third, they will do this before they
> leave the office to make sure it works (the file is too big to e-mail, and they
> don't have vpn), when the open test may not fail. It's too late for me to help
> them once they are on the road. And, we don't want them to connect to a new set
> of files if for some other reason they can't connect to the servers (I don't
> want them modifying what they think is live data, but isn't).
>
> How do I pick up the file name from the existing connect in VB?
>
> Adam
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > Adam-
> >
> > My code, as does most sample relink code, assumes all Access tables link to
> the
> > same file name. It should be easy to modify the code to look for one of the
> > databases in one of several default *path* locations after a test open fails.
> > If you can't find the path in a default location, prompt the user to specify
> the
> > path using a Windows dialog. When you have the path, pick up the file name
> from
> > the existing Connect before rebuilding the DATABASE= part to do the
> RefreshLink.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of AdamF
> > Sent: Tuesday, August 21, 2012 11:23 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Changing links to multiple back ends
> >
> > I've been reading through the code in John's code in Inside Out and at
> > http://tech.groups.yahoo.com/group/MS_Access_Professionals/message/99444, as
> > well as Bill's at
> > http://tech.groups.yahoo.com/group/MS_Access_Professionals/message/99443
> >
> > I have 3 separate back ends with different data (can explain that and you can
> > try to get me to change that later). When the sales manager travels, he takes
> a
> > local copy of the database with him. He first copies the back ends to a local
> > drive, then I update the links. I'd like to automate this.
> >
> > First, how would I automate copying, for example, g:\datafile1.mdb,
> > g:\datafile2.mdb, etc., to c:\localfolder?
> >
> > Second, using either of your codes above, I'd need to get the file name of
> each
> > table to use when I reconnect to the same table in a local file. What's the
> code
> > for doing that? It's not, but something like
> > stFileName = tdf.dbAttachedTable.FileName
> >
> > Adam
> > Denver, CO
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>