greetings Hans hope that all is well in the Netherlands....
struggling w some code
I have a utility db let's call it CopyUtility.accdb; at the press of a button it copies another existing split DB and it's back end files (there is more than one
so i.e. FE is copied as FE2
and BE_a as BE_a2
BE_b as BE_b2
now I need to change the linking in FE2 and this is where I'm getting hung
[for brevity let's say the file names include the paths]
dim dbs as dao.database
set dbs = opendatabase(FE2)
dim tdf as dao.tabledef
for each tdf in dbs.tabledefs
if left(tdf.connect,10)=";DATABASE=" then
tdf.connect=replace(tdf.connect, BE_a, BE_a2)
is this the right direction? and how does one weave in BE_b ?
excuse my lack of capitalization am just kind of air coding this
link change via VBA
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: link change via VBA
The names/paths of the old and new backends must be in quotes.
How do you handle the copying of multiple backends?
Do you hard-code the old and new names/paths in the code?
Or do you store them in a table in the utility database?
Or ...?
How do you handle the copying of multiple backends?
Do you hard-code the old and new names/paths in the code?
Or do you store them in a table in the utility database?
Or ...?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 308
- Joined: 24 Dec 2015, 16:41
Re: link change via VBA
ah ok so my post was a little too brief
dim FE2 as string
same with BEs ....that's what I tried to say that they included the path....
doing a FileCopy to initially make the 2s
and because there really is only 2 BEs - not planning to put them in a table and so no need to do a loop for those or anything that involved
dim FE2 as string
same with BEs ....that's what I tried to say that they included the path....
doing a FileCopy to initially make the 2s
and because there really is only 2 BEs - not planning to put them in a table and so no need to do a loop for those or anything that involved
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 308
- Joined: 24 Dec 2015, 16:41
Re: link change via VBA
for the 2 BEs does one stack the code lines within 1 loop:
tdf.connect=replace(tdf.connect, BE_a, BE_a2)
tdf.connect=replace(tdf.connect, BE_b, BE_b2)
or do I set up 2 separate loops?
or can you string together with an AND somehow in one loop?
tdf.connect=replace(tdf.connect, BE_a, BE_a2)
tdf.connect=replace(tdf.connect, BE_b, BE_b2)
or do I set up 2 separate loops?
or can you string together with an AND somehow in one loop?
-
- 3StarLounger
- Posts: 308
- Joined: 24 Dec 2015, 16:41
Re: link change via VBA
thanks for the sanity check
got it going using 2 different 'for each'
may not be as efficient as possible but hey
one thing: had to add a code line to refresh link or it didn't stick
for each tdf in dbs.tabledefs
if left(tdf.connect,10)=";DATABASE=" then
tdf.connect=replace(tdf.connect, BE_a, BE_a2)
tdf.RefreshLink
next tdf
got it going using 2 different 'for each'
may not be as efficient as possible but hey
one thing: had to add a code line to refresh link or it didn't stick
for each tdf in dbs.tabledefs
if left(tdf.connect,10)=";DATABASE=" then
tdf.connect=replace(tdf.connect, BE_a, BE_a2)
tdf.RefreshLink
next tdf
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: link change via VBA
For just 2 backends there is no point in trying to make it more elegant or efficient.
Best wishes,
Hans
Hans