Jumat, 24 Agustus 2012

RE: [MS_AccessPros] Changing links to multiple back ends

 

Liz-

Well, you would have to relink anytime the backend moved (either logically or
physically) or was renamed. By moved logically, I mean it might be in the same
place with the same name, but the logical drive latter the user assigned to the
share has changed. Or the share name has changed.

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 Liz Ravenwood
Sent: Friday, August 24, 2012 5:32 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] Changing links to multiple back ends

This is a question out of order but I don't recall seeing it in a thread.

What circumstances would occur to have to relink the tables?

Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Glenn Lloyd
Sent: Friday, August 24, 2012 4:32 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Changing links to multiple back ends

Many thanks John.

You and Patrick have saved me some work (and precious time) on the project.

Glenn

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: August-24-12 7:18 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Changing links to multiple back ends

Glenn-

As you loop through the TableDef objects, check first that it's a linked
table
(tdf.Attributes And dbAttachedTable), then check the first 5 characters of
the
Connect property. If it says "Excel", then use slightly different code to
find
and fixup the ;DATABASE= parameter.

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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Glenn
Lloyd
Sent: Friday, August 24, 2012 1:10 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Changing links to multiple back ends

Thanks John,

I've worked with re-linking code before but always just with Access BE's and
using Getz' GetOpenFile to have the user identify the link. In my current
project I like Patrick's code to do the relining 'silently' so to speak but
the app does use a linked Excel table to capture data that pre-dates the
application.

Glenn

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: August-24-12 5:37 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Changing links to multiple back ends

Glenn-

Typically, the link to another Access table has only the ;DATABASE=
argument. A
link to Excel looks like:

Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=D:\Microsoft Press\Access
2007
Inside Out\Fictitious Names.xlsx

All you have to change is the DATABASE= part at the end.

I'll have to dink around to find examples of the others, but they're all
pretty
much the same.

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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Glenn
Lloyd
Sent: Friday, August 24, 2012 11:01 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Changing links to multiple back ends

Patrick,

In your article you mention that

It will not relink ODBC, Excel, Outlook, dBASE, Paradox or any other type of
linked table or object.

What modifications to your code would be necessary to relink, say, an Excel
workbook?

Glenn

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: August-24-12 2:23 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Changing links to multiple back ends

And I forgot to mention ...

To point to some other known path, simply substitute it in this line:

tdf.Connect = ";DATABASE=" & CurrentProject.Path & strBackEnd

... for CurrentProject.Path

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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Friday, August 24, 2012 8:01 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Changing links to multiple back ends

Excellent, Patrick!

Here's a shorter link that avoids the line wrap:

http://tinyurl.com/cot5o74

One minor enhancement to consider - I usually check the attributes rather
than
rely on the presence of a Connect property, like this:

' Looking for attached tables
If (tdf.Attributes And dbAttachedTable) Then

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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Patrick
Wood
Sent: Friday, August 24, 2012 7:53 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] Changing links to multiple back ends

Hi Adam,

PMFBI but I may be able to help. I recently wrote an article that shows an
easy way to use code to automatically relink to tables in multiple back ends
IF they are all in the same folder as the front end. If that will help you
can read the article and get the code at this link:

http://blogs.office.com/b/microsoft-access/archive/2012/08/03/how-to-automat
ical
ly-relink-microsoft-access-tables-.aspx

Kind Regards,

Patrick Wood

Microsoft Access MVP

Gaining Access Technologies Founder

http://gainingaccess.net

Microsoft Access and SQL Azure Information Center for Developers:

http://gainingaccess.net/SQLAzure/AccessAndSQLAzureInfo.aspx

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar