ACCESS FEED: Re: [MS_AccessPros] Copy/import tables between 2 DBS

Hi Thomas,

> "I am looking for a mechanism to copy tables which reside in one BE to another BE."

open the db to pull with OpenDatabase in code -- or just run SQL against it. Import tables from another database. Alternately you can push, but then you have no control over what happens after that

1. get a method from your current database (use as test for BE to put everything into)

2. then translate that to code. Ask for me help along the way as you need it.

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: Thomas <thomas_lam_us@yahoo.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, August 28, 2012 3:52 PM
Subject: Re: [MS_AccessPros] Copy/import tables between 2 DBS

Hi Crystal,

You bring up a very interesting approach. Is MS/Access designed to run in this mode, ie one FE interacting with multiple BEs? This will greatly relieve the Access limitations on the size issue.

I would love to explore this option.

I am looking for a mechanism to copy tables which reside in one BE to another BE. The tables are being created by one app and then at the end of the day copied to the other BE to be consumed. Therefore, the data is not dynamically linked. The business process allows for the timeliness of the data. The apps are used by different groups of users.

My guess is there is either a push or a pull model.
The push model will copy the source to the destination by a process set outside the Access code.

The pull model will have embedded code to copy the source when the Access code in the second app starts the first time. After that, the pull will be deactivated.

Your thoughts.

Thanks,
Thomas

--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> Hi Thomas,
>
> are you trying to change BE paths depending on who is using the app? or combine data?
>
> I am doing an app right now where different users will be pulling data from a service for purposes of appraising property values. Each user may have a different path for where import files are located, and also the BE database. I keep track of those locations with usys tables in the FrontEnd and have an administrator form to pick the user. In code, the table links are deleted and then links are built again ... is this along the lines of what you are trying to do?
>
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
> ________________________________
> From: Thomas <thomas_lam_us@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Monday, August 27, 2012 9:38 AM
> Subject: Re: [MS_AccessPros] Copy/import tables between 2 DBS
>
> Hi John,
>
> One of the option is to have VBA code to allow the App's administrator to update the txt file when necessary. This will prevent the access to and modify the location and name of the BE mdb in the VBA code.
>
> Regards,
> Thomas
>
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > Thomas-
> >
> > Sure, you could put the location in a text file, but then how do you keep the
> > text file updated?
> >
> > 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 Thomas
> > Sent: Friday, August 24, 2012 7:20 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Copy/import tables between 2 DBS
> >
> > Hi John,
> >
> > Thanks for your response.
> >
> > I hate to muddle with the registry and avoid prompting the end user to enter the
> > location of the BE. The copy/import should be hidden from the user. It should be
> > transparent to them.
> >
> > One option is externalize the location of the BE to a text file. The VBA code
> > can then read the file, get the location of the BE and then import/copy the
> > file. That way, if the BE is moved to a new location, the text file will be
> > updated making it transparent. Of course, if there are linked tables, it will
> > have to be relinked.
> >
> > I do have codes in place to prompt if a relink is required. This operation is
> > only performed by someone who knows what s/he is doing.
> >
> > I welcome your thoughts/suggestions.
> >
> > Thanks,
> > Thomas
> >
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > Thomas-
> > >
> > > Can you explain what you mean by "need to have a cut off between the 2 apps?"
> > >
> > > Yes, you could put the file name in a file or a Registry entry, but that's
> > still
> > > "hard coded" in a way. You could also prompt the user for the file location
> > via
> > > the Windows File Dialog.
> > >
> > > Can I assume user "B" (the user needing the update) can access the files for
> > > user "A" over the network? That would definitely be a requirement.
> > >
> > > You could write code behind a button that user "B" clicks to prompt for the
> > file
> > > location, then link to the other file dynamically and import / refresh the
> > data.
> > > If you're updating local tables in user "B" database, then the best way would
> > be
> > > to temporarily link to the external tables one at a time, run queries to
> > perform
> > > the update, then delete the link. This can all be done in VBA code, but I
> > need
> > > more details.
> > >
> > > 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 Thomas
> > > Sent: Wednesday, August 22, 2012 10:19 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] Copy/import tables between 2 DBS
> > >
> > > No, the tables are not linked table.
> > >
> > > I agree with you that sharing a BE mdb is an option. However, there is a need
> > to
> > > have a cut off between the 2 apps. Hence, the need to import/copy.
> > >
> > > I imagine I can include a checking to ensure the tables are sync up. If not,
> > > refresh it on the fly before users start to use the app which use the table as
> > > an input source.
> > >
> > > Then my next question is how to externalize the location of the remote db to a
> > > file to avoid hard coding it.
> > >
> > > Regards,
> > > Thomas
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > >
> > > > Thomas-
> > > >
> > > > Is using linked tables an option? Why not have both front ends point to the
> > > > same shared back end?
> > > >
> > > > 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 Thomas
> > > > Sent: Wednesday, August 22, 2012 7:28 AM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Copy/import tables between 2 DBS
> > > >
> > > > I would like to seek recommendations from the experts the best approach to
> > > > import/Copy tables btw 2 access DBs.
> > > >
> > > > The reason is the tables are being updated by one db and then used by the
> > > other
> > > > as input/inquiry on a daily basis.
> > > >
> > > > Your recommendations are always much appreciated.
> > > >
> > > > Thank you.
> > > >
> > > > Thomas
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > 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]