Updating Access database from Excel worksheet

See also Database Administration Screens

A – Download database

• It's in the data folder of your website.
• Note the date and time of the database file on the remote site.

B – Open Microsoft Access –> your database

• Go Objects –> Tables
• Select the table you wish to maintain, but don't open it.
• Go File –> Export –> File name –> Save as type (Microsoft Excel – your version)

C – Open Microsoft Excel –> your datafile

• To see all data – Ctrl A (to select all) –> Format –> Cells –> Alignment –>
  Horizontal –> General, Vertical –> Top, Text–control –> Wrap-text
• Change prices and other data using formulas etc.
• Quote marks inside fields are often OK, but in some situations can cause errors. If you want indicate inches use the abreviation 'ins'. Or use the HTML code. Use ` instead of ' for the same reason.
• If you wish our program to use your product code + '.jpg' to find images for each product, don't end any product code with a dot or you will cause errors.
• As HTML compresses more than one space into one space, an image filename with two spaces invites problems. A filename with any spaces invites problems with some browsers.
• To prepare for loading back into Access, delete the ID column, as Access will create a new ID number for each record, and delete any extra columns you may have created for working from.
• Delete any empty rows, as these can cause unwanted spaces in your web pages.

D – Open Microsoft Access –> your database

• Go Objects –> Tables –> open your product table
• Ctrl A to select all – then Delete (deletes all products)
• Go Tools –> Database utilities –> Compact.
• Go File –> Get external data –> Import –> Files of type –> Microsoft Excel
  –> (select and open file)
  –> Show worksheets (where you have other sheets in same file) –> Next
  –> First row contains column headings
  –> In existing table (select table)
  –> Confirm
  –> Finish
• If next message does not say the import was successful, then cancel and inspect the Excel worksheet. Possible causes include extra columns in the worksheet you may have created for pricing calculations, or column heading changes not recognised in the Access database.
• Go Tools –> Database utilities –> Compact.

E – Upload Access database with FTP

CAUTION:

• Before performing E check the date and time of the database on the web which you are going to overwrite.
• If it is different from the date and time noted in A then you may have received a new sales order, or new customer details.
• If so, steps A and D must be performed again to avoid overwriting any data entered by a customer while you were doing steps B and C.
• Make sure that any amendments to products that you may have made on-line through the Admin Screens are also in the database that you are going to upload.