[AccessD] Relink Multiple BE

Hi Mark,

This is a little air code - give it a try.  This assumes that you have the
full path of the remote mdb's listed in your table, and that the column
names between the matching remote and local tables are identical.

Dim stg as string
Dim rst as DAO.Recordset

Stg = "SELECT * FROM tblMDBList"
Set rst = CurrentDB.OpenRecordset(stg,dbopensnapshot)

Do While rst.EOF = FALSE

	stg = "INSERT INTO " & rst!TableLocal _
		& " SELECT * FROM " & rst!TableRemote & " IN '" &
rst!RemoteMDBFullPath & "'"

	Docmd.setwarnings false
	Docmd.runsql
	Docmd.setwarnings true

	Rst.movenext
Loop


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Monday, April 13, 2009 4:40 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Relink Multiple BE


Hello All,

 

Current...I have a list of all tables and which MDB they are in...I need to
loop through this list and populate the local copy of said table.
Transferdatabase imports and creates new table...but I want to append the
data to an existing table.  Should I link 1...append...delete link...link
new...append???

 

Any advice?

 

Thanks,

 

Mark
 
> From: dwaters at usinternet.com
> To: accessd at databaseadvisors.com
> Date: Tue, 7 Apr 2009 11:27:06 -0500
> Subject: Re: [AccessD] Relink Multiple BE
> 
> Are the tables duplicated (or mostly) in all 30 old BE's?
> 
> If so, then make a table with the names of the BE's, and a hard-coded list
> of tables in your transitioning code and then loop through that list of
> tables for each BE. Of course, that might be too easy?
> 
> Good Luck,
> Dan
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
> Sent: Tuesday, April 07, 2009 11:17 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Relink Multiple BE
> 
> 
> Hello All,
> 
> Inherited situation: 4 FE and 30+ BE
> 
> I have condensed...to 1 and 1. Now I need to move the BE to their location
> and populate with current data.
> 
> I'm considering a table which lists table names and what old mdb it
belonged
> to...then loop through and link each one in code?
> 
> run queries againts each table appending in new single BE.
> 
> Any suggestions/comments on approach?
> 
> Thanks,
> 
> Mark
> _________________________________________________________________

_________________________________________________________________
Rediscover HotmailR: Now available on your iPhone or BlackBerry
http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Mob
ile1_042009
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com