execute code from a database far, far away? 
Author Message
 execute code from a database far, far away?

If I have db1 in one location and db2 in another (across country), is it
possible to call a procedure in db2 from db1 across a WAN?  I'm trying to
setup some remote databases to import and normalize daily data loads without
remote user involvement.  Replication is a possibility, but since it would
involve partial replicas (and my Access skills are only semi-tough), I'd
save that path for last.  I've tried appending data directly to the tables
from my local database, and it works but probably too slowly to be
acceptable and is prone to write errors given the meager WAN bandwidth.  If
I can move the data around as text files, and call the procedures to load
them from a far, I'm all set.

Thanks,

Stuart



Sat, 27 Nov 2004 00:57:47 GMT  
 execute code from a database far, far away?
Stuart,

While you can open a remote mdb with something like the following:

   Dim oApp As Access.Application
   Set oApp = New Access.Application
   oApp.OpenCurrentDatabase ("\\RemoteLocation\YourMdb.mdb")
   oApp.Run ImportNewData 'routine to append data

I think the .OpenCurrentDatabase would end up sucking the whole mdb across
the wan to your memory.

Why not upload the new data in text (or mdb) and have a procedure run in the
startup form of the remote database...  the database opens, ckecks for a
"update" file and if exists, pop a message to the user to wait while the
data is updated, rename the update file then continue with the normal
startup.

Steve


Quote:
> If I have db1 in one location and db2 in another (across country), is it
> possible to call a procedure in db2 from db1 across a WAN?  I'm trying to
> setup some remote databases to import and normalize daily data loads
without
> remote user involvement.  Replication is a possibility, but since it would
> involve partial replicas (and my Access skills are only semi-tough), I'd
> save that path for last.  I've tried appending data directly to the tables
> from my local database, and it works but probably too slowly to be
> acceptable and is prone to write errors given the meager WAN bandwidth.
If
> I can move the data around as text files, and call the procedures to load
> them from a far, I'm all set.

> Thanks,

> Stuart



Sat, 27 Nov 2004 01:44:35 GMT  
 execute code from a database far, far away?
Hi Steve,

That's kinda how it works now, part of the process happens "automatically"
upon database open, but other parts are user driven and this has proven to
be a problem.  I'm looking for a way to control all data import activities
from a single location.  Options are 1) replication, 2) use individual user
sessions on the various servers (running terminal services) and do things
manually/remotely, 3) append data across the WAN, 4) do as you suggest w/
likely consequences.  Even if implemented, replication may prove to slow.
Looks like my local users may be running sessions on multiple servers...

Thanks,

Stuart

Quote:

> Stuart,

> While you can open a remote mdb with something like the following:

>    Dim oApp As Access.Application
>    Set oApp = New Access.Application
>    oApp.OpenCurrentDatabase ("\\RemoteLocation\YourMdb.mdb")
>    oApp.Run ImportNewData 'routine to append data

> I think the .OpenCurrentDatabase would end up sucking the whole mdb across
> the wan to your memory.

> Why not upload the new data in text (or mdb) and have a procedure run in
the
> startup form of the remote database...  the database opens, ckecks for a
> "update" file and if exists, pop a message to the user to wait while the
> data is updated, rename the update file then continue with the normal
> startup.

> Steve



> > If I have db1 in one location and db2 in another (across country), is it
> > possible to call a procedure in db2 from db1 across a WAN?  I'm trying
to
> > setup some remote databases to import and normalize daily data loads
> without
> > remote user involvement.  Replication is a possibility, but since it
would
> > involve partial replicas (and my Access skills are only semi-tough), I'd
> > save that path for last.  I've tried appending data directly to the
tables
> > from my local database, and it works but probably too slowly to be
> > acceptable and is prone to write errors given the meager WAN bandwidth.
> If
> > I can move the data around as text files, and call the procedures to
load
> > them from a far, I'm all set.

> > Thanks,

> > Stuart



Sat, 27 Nov 2004 02:27:47 GMT  
 execute code from a database far, far away?
OK, Michael Kaplan certainly has most of the lowdown on replication:
www.trigeminal.com/usenet/usenet.asp?1033

Steve


Quote:
> Hi Steve,

> That's kinda how it works now, part of the process happens "automatically"
> upon database open, but other parts are user driven and this has proven to
> be a problem.  I'm looking for a way to control all data import activities
> from a single location.  Options are 1) replication, 2) use individual
user
> sessions on the various servers (running terminal services) and do things
> manually/remotely, 3) append data across the WAN, 4) do as you suggest w/
> likely consequences.  Even if implemented, replication may prove to slow.
> Looks like my local users may be running sessions on multiple servers...

> Thanks,

> Stuart


> > Stuart,

> > While you can open a remote mdb with something like the following:

> >    Dim oApp As Access.Application
> >    Set oApp = New Access.Application
> >    oApp.OpenCurrentDatabase ("\\RemoteLocation\YourMdb.mdb")
> >    oApp.Run ImportNewData 'routine to append data

> > I think the .OpenCurrentDatabase would end up sucking the whole mdb
across
> > the wan to your memory.

> > Why not upload the new data in text (or mdb) and have a procedure run in
> the
> > startup form of the remote database...  the database opens, ckecks for a
> > "update" file and if exists, pop a message to the user to wait while the
> > data is updated, rename the update file then continue with the normal
> > startup.

> > Steve



> > > If I have db1 in one location and db2 in another (across country), is
it
> > > possible to call a procedure in db2 from db1 across a WAN?  I'm trying
> to
> > > setup some remote databases to import and normalize daily data loads
> > without
> > > remote user involvement.  Replication is a possibility, but since it
> would
> > > involve partial replicas (and my Access skills are only semi-tough),
I'd
> > > save that path for last.  I've tried appending data directly to the
> tables
> > > from my local database, and it works but probably too slowly to be
> > > acceptable and is prone to write errors given the meager WAN
bandwidth.
> > If
> > > I can move the data around as text files, and call the procedures to
> load
> > > them from a far, I'm all set.

> > > Thanks,

> > > Stuart



Sat, 27 Nov 2004 02:38:58 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Please Help So Close yet so far away (Access97)

2. closing access from code doesn't work, see further down

3. Further to Help! Please check my code

4. HOWTO: Creating a Floating Window - Code So Far Included

5. HOWTO: Creating a Floating Window - Code So Far Included

6. further help

7. Borland newsgroup are far more responsible...

8. further help with formating pics

9. Further dumb questions...

10. Footer spreads out too far

11. Word to Excel...further minor problems

12. Quest: How far is it from this address to Koln aka Cologne

 

 
Powered by phpBB® Forum Software