By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

Re-linking database through code

P: n/a
Hi

I have a front/back end scenario for my access app. I need to allow user to
connect the front end to one of two back ends at start time. Presumably this
needs linking to the relevant back end using vba code. How is this achieved?

Thanks

Regards
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Check http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web" for
one approach.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"news.dsl.pipex.com" <jo**@nospam.infovis.co.uk> wrote in message
news:40***********************@news.dial.pipex.com ...
Hi

I have a front/back end scenario for my access app. I need to allow user to connect the front end to one of two back ends at start time. Presumably this needs linking to the relevant back end using vba code. How is this achieved?
Thanks

Regards

Nov 12 '05 #2

P: n/a
news.dsl.pipex.com wrote:
Hi

I have a front/back end scenario for my access app. I need to allow
user to connect the front end to one of two back ends at start time.
Presumably this needs linking to the relevant back end using vba
code. How is this achieved?

Thanks

Regards


Many people use code that does the following:

For each table...
-attempt to open the table in code
-when the first table fails to open relink all tables (look in current
folder or a stored folder name, else prompt user for location)

I've found attempting to open each table to see if it opens can be a
little slow over a network. Instead I do the following to test the
links:

For each tabledef...
-get the file/folder name in from the link property
-test that the file/folder exists
-if the data file exists test that the tabledef exists in that database
-when the first test fails relink all tables (look in current folder or
a stored folder name, else prompt user for location)

You can be smarter and just relink the tables that failed but I haven't
found it worth the effort:)

Also, instead of relinking the tables you can also look at just deleting
the tabledefs and creating new ones...

ps. If you need a sample of code to relink let me know... I've got a
simplified version I wrote to attach to different data files (so my
Access app acts like Word in that you have to open a "doc" which in this
case is a seperate data file)
--
regards,

Bradley

Nov 12 '05 #3

P: n/a
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote:
I've found attempting to open each table to see if it opens can be a
little slow over a network. Instead I do the following to test the
links:

For each tabledef...
-get the file/folder name in from the link property
-test that the file/folder exists
-if the data file exists test that the tabledef exists in that database
-when the first test fails relink all tables (look in current folder or
a stored folder name, else prompt user for location)


FWIW once you've relinked the first table open a recordset against that table. Then
relink the rest of the tables and close the recordset. Relinking will go much
faster.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #4

P: n/a
Tony Toews wrote:
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote:
I've found attempting to open each table to see if it opens can be a
little slow over a network. Instead I do the following to test the
links:

For each tabledef...
-get the file/folder name in from the link property
-test that the file/folder exists
-if the data file exists test that the tabledef exists in that
database
-when the first test fails relink all tables (look in current folder
or a stored folder name, else prompt user for location)


FWIW once you've relinked the first table open a recordset against
that table. Then relink the rest of the tables and close the
recordset. Relinking will go much faster.

Tony


Thanks I'll try that.

The biggest concern was having the link check running slowly as it
happens every startup :)

I assume you leave the recordset open while you relink the other
tables...
--
regards,

Bradley
Nov 12 '05 #5

P: n/a
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote:
The biggest concern was having the link check running slowly as it
happens every startup :)
Agreed
I assume you leave the recordset open while you relink the other
tables...


Yes. That was clear in my mind but when I reread my posting I see that it could use
some clarification. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #6

P: n/a
Tony Toews wrote:
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote:
The biggest concern was having the link check running slowly as it
happens every startup :)


Agreed
I assume you leave the recordset open while you relink the other
tables...


Yes. That was clear in my mind but when I reread my posting I see
that it could use some clarification. <smile>

Tony


Works great thanks... in one test it was a quarter the time! That's a
good difference.
--
regards,

Bradley
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.