Hi, 



I have created a file, "this_file.asp", in order to download new records

from a database on a remote web server "onlinedb.mdb" to an identically

formatted database on my local PC "offlinedb.asp".



The following header code works if both databases are on my PC, which runs

PWS to simulate the web server.



Before I upload "onlinedb.mdb" to the actual web server, could someone

confirm that the following code would also work in the "real" scenario ?

(I'm pretty sure that the first section, the on-line access, will work OK,

but, since the asp file will obviously sit on the server, I'm not sure if

the asp file will look at my local file system, which is what I want - or

look at the server file system, which I definately don't want!)



Code:



' connect to online and offline databases



dim strpathinfo

strpathinfo=Request.ServerVariables("PATH_INFO")

dim strphyspath

strphyspath=Server.mapPath(strpathinfo)



strphyspath=Replace(strphyspath,"this_file.asp","onlinedb.mdb") 

dim strconnectON

strconnectON="Driver={Microsoft Access Driver (*.mdb)};DBQ=c:"

strconnectON=Replace(strconnectON,"c:",strphyspath)



dim strconnectOFF

strconnectOFF="Driver={Microsoft Access Driver

(*.mdb)};DBQ=c:\webpages\wwwroot\nss\asp\offlinedb.mdb"



' use strconnectON and strconnectOFF in subsequent code to query databases

etc..........





regards



Simon Garstin

The ASP code will look at the local web-server's file system (which is what

you don't want) - the ASP engine on the server has no knowledge about the

file system on your local machine)



Cheers

Ken



----- Original Message -----

From: <sgarstin@t...>

To: "ASP Databases" <asp_databases@p...>

Sent: Sunday, October 22, 2000 11:41 PM

Subject: [asp_databases] Download info from ONLINE to OFFLINE DB





> Hi,

>

> I have created a file, "this_file.asp", in order to download new records

> from a database on a remote web server "onlinedb.mdb" to an identically

> formatted database on my local PC "offlinedb.asp".

>

> The following header code works if both databases are on my PC, which runs

> PWS to simulate the web server.

>

> Before I upload "onlinedb.mdb" to the actual web server, could someone

> confirm that the following code would also work in the "real" scenario ?

> (I'm pretty sure that the first section, the on-line access, will work OK,

> but, since the asp file will obviously sit on the server, I'm not sure if

> the asp file will look at my local file system, which is what I want - or

> look at the server file system, which I definately don't want!)

>

> Code:

>

> ' connect to online and offline databases

>

> dim strpathinfo

> strpathinfo=Request.ServerVariables("PATH_INFO")

> dim strphyspath

> strphyspath=Server.mapPath(strpathinfo)

>

> strphyspath=Replace(strphyspath,"this_file.asp","onlinedb.mdb")

> dim strconnectON

> strconnectON="Driver={Microsoft Access Driver (*.mdb)};DBQ=c:"

> strconnectON=Replace(strconnectON,"c:",strphyspath)

>

> dim strconnectOFF

> strconnectOFF="Driver={Microsoft Access Driver

> (*.mdb)};DBQ=c:\webpages\wwwroot\nss\asp\offlinedb.mdb"

>

> ' use strconnectON and strconnectOFF in subsequent code to query databases

> etc..........

>

>

> regards

>

> Simon Garstin

>

I am only a beginner in the weird and wonderful world of asp and databases, 

but what you say makes sense.

So the next (and somewhat predictable) question is:

is there a known way of transferring data between an internet-hosted 

database and a local one ? - without having to enter the data manually ?



kind regards



S.G





At 10:50 23/10/00 +1000, you wrote:

>The ASP code will look at the local web-server's file system (which is what

>you don't want) - the ASP engine on the server has no knowledge about the

>file system on your local machine)

>

>Cheers

>Ken

>

>----- Original Message -----

>From: <sgarstin@t...>

>To: "ASP Databases" <asp_databases@p...>

>Sent: Sunday, October 22, 2000 11:41 PM

>Subject: [asp_databases] Download info from ONLINE to OFFLINE DB

>

>

> > Hi,

> >

> > I have created a file, "this_file.asp", in order to download new records

> > from a database on a remote web server "onlinedb.mdb" to an identically

> > formatted database on my local PC "offlinedb.asp".

> >

> > The following header code works if both databases are on my PC, which runs

> > PWS to simulate the web server.

> >

> > Before I upload "onlinedb.mdb" to the actual web server, could someone

> > confirm that the following code would also work in the "real" scenario ?

> > (I'm pretty sure that the first section, the on-line access, will work OK,

> > but, since the asp file will obviously sit on the server, I'm not sure if

> > the asp file will look at my local file system, which is what I want - or

> > look at the server file system, which I definately don't want!)

> >

> > Code:

> >

> > ' connect to online and offline databases

> >

> > dim strpathinfo

> > strpathinfo=Request.ServerVariables("PATH_INFO")

> > dim strphyspath

> > strphyspath=Server.mapPath(strpathinfo)

> >

> > strphyspath=Replace(strphyspath,"this_file.asp","onlinedb.mdb")

> > dim strconnectON

> > strconnectON="Driver={Microsoft Access Driver (*.mdb)};DBQ=c:"

> > strconnectON=Replace(strconnectON,"c:",strphyspath)

> >

> > dim strconnectOFF

> > strconnectOFF="Driver={Microsoft Access Driver

> > (*.mdb)};DBQ=c:\webpages\wwwroot\nss\asp\offlinedb.mdb"

> >

> > ' use strconnectON and strconnectOFF in subsequent code to query databases

> > etc..........

> >

> >

> > regards

> >

> > Simon Garstin

> >

>

>

Yes you can, but it's a bit cumbersome, and depends on the size of your

database.



(Of course, the most obvious solution would be to use the task scheduler or

similar to automate a download of the online database)



On innovative solution that I saw worked like this:



a) ComputerA - you computer, runs PWS, has "offline" database

b) ComputerB - ISP's hosting computer, with "online" database.



On your machine, you browse to:



http://localhost/getrecords.asp



This page runs a select query on your local database, and gets the IDs of

all the records and puts them into a form, the form gets submitted

getRecords.asp running on ComputerB



<form method="post" action="www.myserver.com/getRecords.asp">



(of course you might want to submit some kind of password etc as well).

(you also need to write out the IP address that you machine currently has)



getRecords.asp on ComputerB then does a select on the database on the

server, similar to:



SELECT field1, field2, field3

FROM table1

WHERE ID NOT IN (" & Request.Form("ID") & ")"



which should select all the records which have IDs numbers that don't exist

on your local database.



These get written to a form.

The action="" of this form is set to the IP address of your machine

(ComputerA), similar to:



<form method="post" action="http://129.94.52.10/receiveRecords.asp">



and the records get posted back to your PWS, to an ASP page that will accept

them all, and insert them into the database.



The main difficulty with this system is keeping the ID numbers in synch.



Otherwise, if you can afford SQL Server, and can get a permamanet IP

address, then look an SQL Server's TCP/IP replication.



Cheers

Ken



----- Original Message -----

From: "Simon Garstin" <Sgarstin@t...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, October 24, 2000 5:07 AM

Subject: [asp_databases] Re: Download info from ONLINE to OFFLINE DB





> I am only a beginner in the weird and wonderful world of asp and

databases,

> but what you say makes sense.

> So the next (and somewhat predictable) question is:

> is there a known way of transferring data between an internet-hosted

> database and a local one ? - without having to enter the data manually ?

>

> kind regards

>

> S.G

>

>

> At 10:50 23/10/00 +1000, you wrote:

> >The ASP code will look at the local web-server's file system (which is

what

> >you don't want) - the ASP engine on the server has no knowledge about the

> >file system on your local machine)

> >

> >Cheers

> >Ken

> >

How about this very crude solution:



1. Start point: in the online db, have a main and backup table which are 

identical.

2. New records then added via web

3. Via (password-protected) asp file run a query to test which recs are in 

main and not backup table (ie which are new records)

4. Then use asp file to stick output of query into a text file 

("newrecs.txt") and store on web server

5. Call up another asp file to download newrecs.txt to local PC

   -- and then via same asp file another query bring online backup table up 

to date (2 processes must be in sync for obvious reasons)

6. Run a local asp to unpack newrecs.txt and append to local database via 

an append query



Use reverse procedure for uploading changes



The only disadvantages I see to this solution are:



a. Double the number of online db tables required (however regular pruning 

of records would alleviate the problem)

b. The local user needs to have PWS on his machine to run the asp scripts.



On point (b) I have investigated what other Microsoft Office applications 

could do this instead and the answer is: none.

Word for instance will run queries (via Microsoft Query) but these are only 

SELECT or PARAMETER. There is no other standard desktop application (I am 

aware of) which will take data in, and then use an Access APPEND query 

(from the external) to add records to a database.



Simon Garstin





At 11:35 24/10/00 +1000, you wrote:

>Yes you can, but it's a bit cumbersome, and depends on the size of your

>database.

>

>(Of course, the most obvious solution would be to use the task scheduler or

>similar to automate a download of the online database)

>

>On innovative solution that I saw worked like this:

>

>a) ComputerA - you computer, runs PWS, has "offline" database

>b) ComputerB - ISP's hosting computer, with "online" database.

>

>On your machine, you browse to:

>

>http://localhost/getrecords.asp

>

>This page runs a select query on your local database, and gets the IDs of

>all the records and puts them into a form, the form gets submitted

>getRecords.asp running on ComputerB

>

><form method="post" action="www.myserver.com/getRecords.asp">

>

>(of course you might want to submit some kind of password etc as well).

>(you also need to write out the IP address that you machine currently has)

>

>getRecords.asp on ComputerB then does a select on the database on the

>server, similar to:

>

>SELECT field1, field2, field3

>FROM table1

>WHERE ID NOT IN (" & Request.Form("ID") & ")"

>

>which should select all the records which have IDs numbers that don't exist

>on your local database.

>

>These get written to a form.

>The action="" of this form is set to the IP address of your machine

>(ComputerA), similar to:

>

><form method="post" action="http://129.94.52.10/receiveRecords.asp">

>

>and the records get posted back to your PWS, to an ASP page that will accept

>them all, and insert them into the database.

>

>The main difficulty with this system is keeping the ID numbers in synch.

>

>Otherwise, if you can afford SQL Server, and can get a permamanet IP

>address, then look an SQL Server's TCP/IP replication.

>

>Cheers

>Ken

>

>----- Original Message -----

>From: "Simon Garstin" <Sgarstin@t...>

>To: "ASP Databases" <asp_databases@p...>

>Sent: Tuesday, October 24, 2000 5:07 AM

>Subject: [asp_databases] Re: Download info from ONLINE to OFFLINE DB

>

>

> > I am only a beginner in the weird and wonderful world of asp and

>databases,

> > but what you say makes sense.

> > So the next (and somewhat predictable) question is:

> > is there a known way of transferring data between an internet-hosted

> > database and a local one ? - without having to enter the data manually ?

> >

> > kind regards

> >

> > S.G

> >

> >

> > At 10:50 23/10/00 +1000, you wrote:

> > >The ASP code will look at the local web-server's file system (which is

>what

> > >you don't want) - the ASP engine on the server has no knowledge about the

> > >file system on your local machine)

> > >

> > >Cheers

> > >Ken

> > >

>

>

>