FTP Access DB to Web Server

[DMUM via AccessMonster.com]

Firstly, I apologize if this is the wrong place to ask this question and if
this question has been asked before - I have looked, but nothing I found
seems to answer my question, or either I don't know enough to realize that
they are.... Secondly, I am new to coding for FTP transactions so forgive me
if my questions seems to be rather vague....

Anyway, I have a client who has an Access Db (MainDB) that FTPs photos to a
location on a Host Server and also updates tables in another Access DB (DB2)
with specific information about these pictures. The information is needed
with the pictures as they appear on the website. The tables between MainDB
and DB2 are linked. Somehow the newly refreshed tables in DB2 needs to be
FTP'd to the Host along with the pictures. Not sure how this was originally
set up because the original developer did not leave any documentation and my
client doesn't know. My client wants the data and photos to be available to
the website as soon as the user send the photos to the web (they click on a
button in an Access web form which kicks off the FTP process to send the
photos).

1. What is the best thing to do here - FTP th entire DB2 database to the
Host or just send the data in the tables or only send the new data. I would
prefer to just send the entire DB. I think it would be easier then sending
multiple tables (not just 1 or 2 - more like 8) and as for only sending the
new information - again, a few tables are updated so I think this will take
more time.

2. How would I do any of the FTP processes briefly described in item 1 above.
Again, I would prefer the easiest way so as not to have a long development
time. Seems this should be easy, I'm just not sure how to go about it.

Please help. Thank you

 

[Brendan Reynolds]

Firstly, I apologize if this is the wrong place to ask this question and
if
this question has been asked before - I have looked, but nothing I found
seems to answer my question, or either I don't know enough to realize that
they are.... Secondly, I am new to coding for FTP transactions so forgive
me
if my questions seems to be rather vague....

Anyway, I have a client who has an Access Db (MainDB) that FTPs photos to
a
location on a Host Server and also updates tables in another Access DB
(DB2)
with specific information about these pictures. The information is needed
with the pictures as they appear on the website. The tables between
MainDB
and DB2 are linked. Somehow the newly refreshed tables in DB2 needs to be
FTP'd to the Host along with the pictures. Not sure how this was
originally
set up because the original developer did not leave any documentation and
my
client doesn't know. My client wants the data and photos to be available
to
the website as soon as the user send the photos to the web (they click on
a
button in an Access web form which kicks off the FTP process to send the
photos).

1. What is the best thing to do here - FTP th entire DB2 database to the
Host or just send the data in the tables or only send the new data. I
would
prefer to just send the entire DB. I think it would be easier then
sending
multiple tables (not just 1 or 2 - more like 8) and as for only sending
the
new information - again, a few tables are updated so I think this will
take
more time.

2. How would I do any of the FTP processes briefly described in item 1
above.
Again, I would prefer the easiest way so as not to have a long development
time. Seems this should be easy, I'm just not sure how to go about it.

Please help. Thank you

I'm not familiar with DB2, but I can think of some problems that would arise
if you tried to do things this way with a SQL Server database, and I
strongly suspect that you would encounter the same type of problems using
DB2 or any other client-server relational database.

FTP stands for File Transfer Protocol, and it does exactly what it says on
the tin - it transfers files. To transfer a SQL Server data file using FTP
you would have to first detach the data files from both the source and
target SQL Server instances, as they cannot be copied while attached. This
of course would break any app that attempted to access those databases while
the files were detached, including a web browser, so your client's web site
would unusable while the files were being transferred. Then you'd have to
transfer the data file, which would over-write any changes that might have
been made by any other user, so only one user can ever update the file. And
then you'd have to re-attach the data files to the two SQL Server instances.

The bottom line is that although FTP is perfectly OK as a means of
transferring the pictures, I do not believe it will be practical to use FTP
to transfer the associated data. I believe you'll need to use other
technologies for that.

 

[DMUM via AccessMonster.com]

Oh Dear, I didn't meant DB2 as Database2 not a DB2 database. I know what FTP
is. I am trying to transfer an Access Database not a SQL Database. Sorry,
didn't mean to sound that stupid. I thought I was clear that I was trying to
deal with MS Access not SQL. Thanks for trying to answer my question though.
Can you answer my question now that I shed some light on this? Thanks

Firstly, I apologize if this is the wrong place to ask this question and
if

[quoted text clipped - 40 lines]

Please help. Thank you

I'm not familiar with DB2, but I can think of some problems that would arise
if you tried to do things this way with a SQL Server database, and I
strongly suspect that you would encounter the same type of problems using
DB2 or any other client-server relational database.

FTP stands for File Transfer Protocol, and it does exactly what it says on
the tin - it transfers files. To transfer a SQL Server data file using FTP
you would have to first detach the data files from both the source and
target SQL Server instances, as they cannot be copied while attached. This
of course would break any app that attempted to access those databases while
the files were detached, including a web browser, so your client's web site
would unusable while the files were being transferred. Then you'd have to
transfer the data file, which would over-write any changes that might have
been made by any other user, so only one user can ever update the file. And
then you'd have to re-attach the data files to the two SQL Server instances.

The bottom line is that although FTP is perfectly OK as a means of
transferring the pictures, I do not believe it will be practical to use FTP
to transfer the associated data. I believe you'll need to use other
technologies for that.

 

[Brendan Reynolds]

Oh Dear, I didn't meant DB2 as Database2 not a DB2 database. I know what
FTP
is. I am trying to transfer an Access Database not a SQL Database.
Sorry,
didn't mean to sound that stupid. I thought I was clear that I was trying
to
deal with MS Access not SQL. Thanks for trying to answer my question
though.
Can you answer my question now that I shed some light on this? Thanks

Oops sorry. Upon re-reading your original post I see that you did say
"Access DB (DB2)", but I somehow managed to only see "DB2".

Well, as an Access (more accurately a JET) database is file-based and not
client server, you can disregard what I said about having to detach from and
re-attach to the server. You still have the problem of over-writing any data
that was changed by any other user if you transfer the entire database or
entire tables, though.

To transfer only specific tables or only specific records, you'd have to
export those tables or records to a file (another JET database or another
format such as CSV or XML), transfer the file, then have some logic running
on the server to import that data into the target database.

If only one user ever updates data, that *might* be an acceptable solution
for you, otherwise you may need to look in to providing users with some way
to directly update the data in the target database, via terminal server, via
a web-based app using ASP.NET or other technologies, or by using a
web-service.

Other factors to consider is how often data is updated and how large the
file is. Transferring a small file once a day might work, transferring a
large file every minute probably wouldn't.