Synchronize Access database using Frontpage 2003

[BB]

I have an ecommerce Frontpage/ASP website that takes input from visitors
(orders etc) and input from the site author (that's me, I load the products).

What I would like to do is maintain (add, delete, change) the products off
line and then post these up to the web site.

All the above items are held in tables in one Access database.

Changes can happen to the database on line, (orders etc) whilst the offline,
(product editing) changes are being made.

The ideal would be to synchronize at record level in every table when these
changes go back to the online database.

How does Frontpage 2003 synchronise an Access db?

Does it do it at record level? or table level? or just the whole db file
level?

Regards
Bob

 

[Kathleen Anderson [MVP - FrontPage]]

I'm not sure that can be done. Instead of updating the database offline, you
might try creating ASP pages in your site that you can use to update the
online database with your products.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

 

[BB]

Hi Kathleen

Thanks for your quick response. I dont really want to get into more ASP
pages to edit. I already have an admin function that enables me to edit
online using ASP pages. They are quite complex and take a lot of development.

What I want to do is edit in 'table view' so That I can add/change lots of
products at once. Some of the fields within my tables are quite large so I
also want to select fields to edit. Using Access is fine, or even using a
spreadsheet and csv transfer may be a possibility? Append to online database
using a csv file - is this possible?

The primary question remains

At what level does 'Frontpage - publish - synchronize' operate at? Field,
Record? Table or whole file?

Best Regards
Bob

 

[Ronx]

At what level does 'FrontPage - publish - synchronize' operate at? Field,

Record? Table or whole file?

Entire file.

 

[Stefan B Rusynko]

Export your DB to your PC, open/ edit it in Access and then Import it back into your PC
- warning doing off line will create a high risk of wiping out an order that was entered in the interim




| Hi Kathleen
|
| Thanks for your quick response. I dont really want to get into more ASP
| pages to edit. I already have an admin function that enables me to edit
| online using ASP pages. They are quite complex and take a lot of development.
|
| What I want to do is edit in 'table view' so That I can add/change lots of
| products at once. Some of the fields within my tables are quite large so I
| also want to select fields to edit. Using Access is fine, or even using a
| spreadsheet and csv transfer may be a possibility? Append to online database
| using a csv file - is this possible?
|
| The primary question remains
|
| At what level does 'Frontpage - publish - synchronize' operate at? Field,
| Record? Table or whole file?
|
| Best Regards
| Bob
|
| "Kathleen Anderson [MVP - FrontPage]" wrote:
|
| > I'm not sure that can be done. Instead of updating the database offline, you
| > might try creating ASP pages in your site that you can use to update the
| > online database with your products.
| >
| > --
| >
| > ~ Kathleen Anderson
| > Microsoft MVP - FrontPage
| > Spider Web Woman Designs
| > web: http://www.spiderwebwoman.com/resources/
| >
| >
| > | > >I have an ecommerce Frontpage/ASP website that takes input from visitors
| > > (orders etc) and input from the site author (that's me, I load the
| > > products).
| > >
| > > What I would like to do is maintain (add, delete, change) the products off
| > > line and then post these up to the web site.
| > >
| > > All the above items are held in tables in one Access database.
| > >
| > > Changes can happen to the database on line, (orders etc) whilst the
| > > offline,
| > > (product editing) changes are being made.
| > >
| > > The ideal would be to synchronize at record level in every table when
| > > these
| > > changes go back to the online database.
| > >
| > > How does Frontpage 2003 synchronise an Access db?
| > >
| > > Does it do it at record level? or table level? or just the whole db file
| > > level?
| > >
| > > Regards
| > > Bob
| > >
| >
| >
| >

 

[Clark]

Export your DB to your PC, open/ edit it in Access and then Import it back into your PC

- warning doing off line will create a high risk of wiping out an order that was entered in the interim

Yes that would be a problem. I havent actually tried exactly this, but
an approach to consider would be one where the product list with the
stuff you want to update is all in a "back end" Access database on
your website, to which the active Access database with the Order
processting etc links.

Then you update the "back end" product list on your computer, and when
ready, ftp it up to the website. There is only a brief time (just the
ftp upload time) that the site would be interrupted.

As long as you arent changing the table structures etc, ftp'ing a
database with changed records wont mess up Frontpage, so you dont have
to re-import into FP every time you add products.

 

[BB]

Hi guys

Yes we've all identified the risk but how to overcome it? Does anybody know
if there is some obscure csv import fuction (or the like) that could just
ADD/UPDATE a single table in the database?

If individual table edit was possible them (because the orders go into a
different table) there would not be a risk.

Any ideas on how to edit/update a single table in an Access db and uploading
that to an operation Access db, without overwriting all other tables?

Best Regards
Bob

 

[Bob Lehmann]

Yes we've all identified the risk but how to overcome it?

You have two choices -
1. Create a web interface to enter your info into the DB.
2. Write a script to parse, and insert from an uploaded csv file.

Bob Lehmann

 

[BB]

Hi Bob

Thanks for your response. I like the 'insert from an uploaded csv file'
because I can do all my edits/additions in a spreadsheet with ease.

The 'Write a script to parse' sounds daunting. Do you know of any examples I
can look at?

Best Regards
Bob

 

[Bob Lehmann]

Do you know of any examples I can look at?

I doubt that you would be able to find anything that meets your specific
needs, but you could do some research based on the following steps......

NOTE: There is nothing in FP that will do this for you. You will have to
write the code yourself.

The IUSR_xxx account will need read access to the cvs file. Your web host
will have to set this up,

Create an ASP file to open & read the CSV file line by line using File
System Object.

As each line is read, use the Split() funtion to place each value in an
array.

Read the values from the array to populate your query, preferrably a stored
procedure.

Execute the query.

Repeat until all rows are read.

You will also have to validate each data item, so you are not trying to put
an alpha into a numeric field, the count of items in each line matches what
you expect it to be - this can be off for a couple reasons, usually because
whatever you chose as a delimiter is part of the data -, you're not
inserting a duplicate etc....

You will also need error handling, and decide what to do with items that
cause errors when tring to insert. You should also have some type of
confirmation that displays the number of rows inserted, errors - if any -,
and maybe even display the items that were inserted.

Bob Lehmann

 

[Clark]

Any ideas on how to edit/update a single table in an Access db and uploading
that to an operation Access db, without overwriting all other tables?

Well, that is what I *thought* I suggested how it might be done