tesco software

Tesco Software > Complete Office > Frequently Asked Questions > Database


1. Syntax error in query" in Database

This message can appear when sorting or filtering a table or creating a query and is caused by invalid table or field names.

The three most common problems when creating field and table names are:

  • names containing spaces
  • names containing hyphens (or minus signs)
  • names conflicting with in-built function names

To change a table name, make sure the table is closed and go to the "database manager" screen. Click once on the table and then click again to activate the rename box. Delete spaces or hyphens (or change to to underscores)

To change a field name, open the table and select Format/Table. In the format table dialog, edit the names so there are no spaces or hyphens. These rules do not apply to the field captions.

2. How do I connect to an Tesco Complete Office Database with ASP?

So you've got Microsoft Internet Information Server (IIS)and you want to use ASP to interact with an Tesco Complete Office Database. For the purposes of this exercise, you can treat an Tesco Complete Office database in exactly the same way as a Microsoft Access database. IIS ships with drivers for Jet databases and so there's no requirement for an ODBC driver or any other software to be installed on the server.

Below is a compete listing for an ASP page that will a) open an Tesco Complete Office Database and b) dump a specified table into a web page. The code assumes:

"test.adb"  - database name
"mytable" - table name in database
"/database/" - server directory (location of database)

The code does not require a Data Source Name (DSN) to be setup on the server since it explicitly states the database driver and name. It would be better practice to switch to a DSN in the longer term.

<HTML>
<%
Dim cnnAB, rsAB, i

Response.Write "<p>Opening database...</p>"

Set cnnAB = Server.CreateObject("ADODB.Connection")
cnnAB.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & _ 
            Server.MapPath("/database/test.adb")
Set rsAB = Server.CreateObject("ADODB.Recordset")
rsAB.Open "SELECT * FROM MyTable" , cnnAB, 3

Response.Write "<p>And now dump the table to screen in a table...</p>"
Response.Write "<p><table border=1><tr>"

For i=0 To rsAB.Fields.Count -1
 Response.Write "<th>" & rsAB(i).Name & "</th>"
Next

Response.Write "</tr>"

Do While Not rsAB.EOF
 Response.Write "<tr>"
 For i = 0 To rsAB.Fields.Count -1
  Response.Write "<td valign=top>" & rsAB(i) & "</td>"
 Next
 Response.Write "</tr>"
 rsAB.MoveNext
Loop

Response.Write "</table>"

%>

</HTML>

3. Multiple fields on the last line of Address label

Problem: How to get multiple fields on a single line of the address block in a label report.

Solution: Use Format/Group to "group together" fields.

Example: Select File/New/Report/Labels and follow the wizard as per normal. You'll end up with a label that looks something like:

FirstName Lastname
Company
Address
Town
State
Zip

Next, re-arrange the label to like this:

FirstName Lastname
Company
Address
Town State Zip

Finally, select all the fields on the last line (use the mouse) and select Format/Group from the menus.

4. How do I create address lables

  1. Start Database: Start/Programs/Tesco Complete Office/Tesco Complete Office Database.

  2. Create your address book: If you have already created your address book in Database skip to step 4. If not, create a new database with File/New and enter a new name. Add a table by selecting File/New/Table, select Table Wizard, select the "Customer" table, click Next, click the ">>" button to add all the fields and then click the Finish button. Click OK and then give your table a name, e.g. Addresses.

  3. Add your data: Double-click on the new table to open it and then add some real names and addresses.

  4. Create a label report: Select File/New/Report and choose the Labels option. Choose the label type to match the type of label stationery you have (nearly all stationery will match a known Avery label format). Click Next and choose a table (e.g. Addresses). Click Next and choose the fields for your first line of the report -  e.g. cust_title, firstname, lastname - use the ">" button to select these fields. Click Next and add the remainder of your fields. Click Finish. 

The report can now be previewed and printed.

5. How do I fill a sheet of labels with the same address

What you want is to print a sheet of labels with the same name and address, say 8 labels. You can do this using Tesco Complete Office's relational functions. The solution seems a little long-winded perhaps but it does mean that you can choose any address by changing a single field.

Suppose you have a client table that looks like this:

ClientID ClientName Address1 Address2
1 Phil Roach 11 Petworth Court San Francisco
2 Andy Smith 38 St Saviours Wharf Durham
3 Chris England 23 Kingston Drv Sydney

Note: The table above has a "ClientID" field that uniquely identifies any particular record. You could use "Name" - but what happens if you have two "Andy Smiths"? If your name and address table has not already got a primary key, add one! (e.g. Select Format/Table, add a new field "tablenameID" and make type Incremental. Click Format and set Index to be Primary).

Repeat Labels Step 1

Create a table with the name ClientRepeat. It has two fields and a single record:

ClientRepeatID ClientID
1 2

Make "ClientRepeatID" an incremental field and the primary key. Make "ClientID" of type numeric and select the Format button to set the numeric format to Long. This table stores the ClientID that you want to print out - in this case, ClientID is set to "2" which is the record for Andy Smith.

Repeat Labels Step 2

Next, create a table with the name LabelRepeat. It has one field and the record is repeated for the number of labels you want. In our example, we want 8 copies of a label so there are 8 records as follows:

ClientRepeatID
1
1
1
1
1
1
1
1

Make "ClientRepeatID" of type numeric and select the Format button to set the numeric format to Long.

Repeat Labels Step 3

Create a new relation with the name LabelPrn that joins the above tables together. Here's what the relation looks like in the create relation dialog:

Left Table Join Field Right Table Join Field Join Type
Client ClientID ClientRepeat ClientID Inner
ClientRepeat ClientRepeatID LabelRepeat ClientRepeatID Inner

Repeat Labels Step 4

Now create a new label report. When prompted, base the lable on the Relation LabelPrn and then proceed as normal (see FAQ 4 for more details).

How it works

Enter the "ClientID" of the address you want to replicate in the ClientID field of the ClientRepeat table. This table is relationally linked to both the LabelRepeat table (save's typing the ClientID 8 times) and the Client table to pull through the actual data.

6. Quick Database tutorial

This is a step-by-step guide to creating an address book type database for those new to Tesco Complete Office's Database.

  1. Start Database. Select Start/Programs/Tesco Complete Office/Tesco Complete Office Database
  2. Create a new database file to hold your data (if you've already created a database, just open it instead). Select File/New Database and enter a name (e.g. AddressBook).
  3. Create a new table. Select File/New/Table. For the purposes of this exercise (understanding Database better) skip the create table wizard and select the Blank Table option.
  4. Design the table. For each item of information you want to store, add a field. Start of with an "ID" field that stores a unique number for each record you add (this is good practice). Type the names of the fields in the "Name" column and set the type in the "type" column. The table design window should look something like this:

    tesco software
  5. Save the table. Select OK and give the table a name.
  6. Add data. Double-click on the new table and a blank "grid view" of your new table will be displayed. You can add your data now - each row is a new record. You can also view the data in "form view" by selecting View/Form and toggle back with View/Grid (there are toolbar buttons that let you do this).
  7. Next steps:
  • Create a report with View/Report
  • Create mailing labels  using File/New/Report and selecting Labels in the wizard
  • Search for data (use the Find button)
  • Sort and filter the data (use the right-click menu).

7. How to import ASCII text

ASCII or plain text must include a header line denoting field names. An example of the ideal format is as follows:

"FirstName", "LastName", "Company", "Age"
"Phil", "Roach", "Tesco Complete Office Plus Software", 37
"John", "Speak", "", 45
"Charles",  "Stuart", "House of Stuart", 498

Notes: 

  • It is good practice to surround text fields by double quotes

  • If a text field is blank, still enter both sets of quotes (see line 3 in above example)

  • Numeric fields do not require quotes

  • Each record must begin on a new line

  • Commas must be used to separate fields

You can check the format of the data you are trying to import using Notepad (Start/Programs/Accessories/Note). If necessary, manually add the field names at the top of the data.

8. Simple Address book example connecting with Write

How about a very simple example that shows a Database with an address book that allows browse and edit and can create a letter in Write at the click of a button.

Download the example

Open the database and look at the "AddressForm" form. There is a button that runs a macro to insert the current record's address into a new Write document. The code can easily be changed to support a "Create Envelope" function by a) Createing an envelope template in Write  and b) changing "NORMAL" to "ENVELOPE" in the code. 

9. What about templates

Templates are easy to make yourself. For example, to create your own template that contains your address in the top right corner of the page, try these steps:

  1. Start Write (Start/Programs/Tesco Complete Office/Write).
  2. Select File/New and click the Template radio button (base it on NORMAL to start off with).
  3. Select Insert/Frame and draw a frame in the top-right hand section of the page.
  4. Type your address details in the new frame.
  5. Make any other format changes you want for the template.
  6. Click the Save button and call the new template whatever you want e.g. Home.
  7. Select File/New from the menus, choose Home and click the Document radio button - you now have a new document based on the new template.

Repeat step 7 each time you want to use the template.

For more information, select Help/Write Help and look up Templates in the index.

10. Database compatibility with MDB

The Database format we use is identical to Microsoft's MDB. However, there are different versions of MDB format and these are generally not backward compatable. For example, Microsoft Access 97 cannot open a file created with Access 2000. Similar applies to Tesco Complete Office. The following table sums it up:

MDB/ADB version Tesco Complete Office Microsoft
Tesco Complete Office Database Tesco Complete Office Database Access 2000, 2002 & 2003

Note: the above does not imply that Microsoft Access 2000 can open Access 2002 files - for this complication, please refer to Microsoft!

In general, Tesco Complete Office (and Access) will update the mdb or adb to the latest version. This is usually desireable unless you need to have the file opened by some earlier version (of Access generally). You can use DAO methods to change the database version back to "3" from "4" (earlier is not possible) as follows:

1. Start Database and select Tools/Macros and create a new macro called ConvertJetMDB

2. Copy and paste the following code to create the macro

Sub ConvertJetMDB()
  dbName = "C:\path\olddb.MDB"
  dbNewName = "C:\path\newdb.MDB"
  Set dbdao = CreateObject("DAO.DBEngine.36")
  MsgBox "DAO OK, version is " & dbdao.Version

 
  dbver = 32
  dbdao.CompactDatabase dbName, dbNewName, ";LANGID=0x0409;CP=1252;COUNTRY=0", dbver
  Set newdb = dbdao.OpenDatabase(dbNewName)
  MsgBox "Database opened OK - format version is " & newdb.Version
End Sub

3. Adjust the database names to suit and right-click Run to convert the mdb

The end result is an Access 97 compatible database that can be opened by Tesco Complete Office Database, Access 97 (and later).

11. Can Database be used with MySQL

MySQL is a poweful Database Server that is used on many database driven websites - see http://www.mysql.com. Tesco Complete Office Database can be used to act as a "front end" - the application that displays and edits data. The following steps outline how to connect the two. It assumes that Tesco Complete Office Database is running on the local Windows PC and MySQL is running on a server (remote or local).

  1. Download and install the MySQL ODBC driver. See: http://www.mysql.com/products/myodbc/.
  2. Create a new ODBC data source: Goto Control Panel, Administrative Tools, Data Sources ODBC.
  3. On the User DSN, select Add and select the MYSQL driver (if you can't see this, check instructions for step 1).
  4. Give it a name: e.g. MySQLTescoCompleteOfficeTest
  5. Enter the server name, the database name, the username and password - you may have to contact your network adminstrator for these details.
  6. Start Tesco Complete Office Database and create a new blank database.
  7. Select File/Import and choose ODBC as the driver and OK
  8. Select the Machine Data Source tab and choose MySQLTescoCompleteOfficeTest
  9. You should now see a list of tables (if not go back to step 5 and try the Test button)
  10. Choose a table and, assuming you want to work with live data rather than import a copy of the data, select "Attach"

You can now use and edit the table as though it were a normal Tesco Complete Office table.

12. Creating a database of hyperlinks

Suppose you want to use database to store information that has some associated external file or image or web site.

You can use a database field to store the location of the external file and the use the HYPERLINK function to provide a connection to the file.

Here's how to do it:

  1. Start Database and open (or create a new) database
  2. Select File/New/Table
  3. Select Blank table
  4. Create four fields:
    htitle Character
    hpath Character Click Format and set Width to 255
    hlink Calculated Click Format and set Formula to:
    =HYPERLINK(hpath, htitle)
    Description Memo
  5. Save the table

Now, whatever you type into the hpath field can be loaded when in form view. For example, suppose you have an image stored in My Documents called photo1.jpg, then set:
htitle: Photo1
hpath: C:\Documents and Settings\Phil\My Documents\photo1.jpg
Description: Holiday snap

Open the table in Form view (e.g. click on the View Form button) and click on the link "Photo1". The image will be loaded in your default jpg editor (Tesco Complete Office Photopaint would work well).

In the same way, you can link to folders, web sites, word processing files - anything that Windows Explorer can understand can be used in a hyperlink.

For an example database, take a look at HYPERLINKS.ADB

13. How to create a Switchboard

A "Switchboard" is Microsoft Access speak for a form with buttons that open up other forms (or tables or queries).

Suppose you want a form that acts as a central point to the user and allows him to choose other forms or tables or run macros. Here is a guide on creating such a form:

  1. From Database Manager, select Create form using wizard
  2. Pick any table (or create a dummy table - it does not matter)
  3. Add one field and Finish the wizard
  4. In Form Design mode, delete the field since all we want the user to see is buttons and text
  5. Select Insert/Macro Button and draw out the button on the form
  6. Right click over the button and select Properties
  7. Set an appropriate caption and click on Macros
  8. In the Events column, select Click
  9. Click New Macro and give it a name to match its function
  10. Repeat steps 5 to 9 as required
  11. Save the form and in Database Manager, select Macros
  12. Click on each of the new macros in turn and right-click and select Edit
  13. You can rename the default "MacroName" to something more meaningful
  14. Add code to open a form or table or report. Examples:
    • DBForms.Open("SomeFormName")
    • Tables.Open("MyTableName")
    • Reports.Open("Report1")
    • Quit

Note that the above are all single line macros - you can make them as simple or complex as you want. The last of the above examples exits the database application.

You can now edit the form and add text (and pictures) to make it more user friendly.

14. Start Database and open a form

How to open a database from the desktop and automatically display a form

First Part: create a link on your desktop that runs the database application

  1. Right-click over the desktop and select New/Shortcut
  2. Select Browse and choose the abdata.exe program (it's normally in c:\program files\Tesco Complete Office\)
  3. Select Finish
  4. Right-click over the database and select properties
  5. Add the name of the database to the Target and include the name of the macro you are going to create. For example, suppose your database is called "MyData" and is a folder called "MyDocs" on your C: drive, the full Target should read:
    "C:\Program Files\Tesco Complete Office\Abdata.exe" c:\MyDocs\MyData.adb#StartUp

Second Part: create the macro to open a form

  1. Open the database containing the form you want to display
  2. Select File/New/Macro and call it "StartUp"
  3. Edit the macro so it opens the form. For example, suppose the form is called "CustomerForm", the macro should look like this:
    Sub Startup ()
       DBForms.Open("CustomerForm")
    End Sub

Now you can close the database and test the link on the desktop - the result should be that the form you want is displayed

15. Default sort order

The default sort order in Database is determined by the Primary Key. This can be set using Format/Table, clicking on a field, selecting Format and then setting Index to Primary Key. When adding new records, the table will show the new entries at the bottom of the table. As soon as the table is closed and re-opened, the table will be sorted according to the Primary Key.

Primary Keys are important in database design. They provide the unique identifier for a record. So usually, you should not use fields like "surname" or "company name", since you cannot guarantee these will be unique. If your table has no obvious candidate for a primary key (e.g. product number, or policy number etc), then simply add an incremental field to your table and set it to be the primary key.

If you want to work with a table in some pre-defined sort order that is different to the primary key, then create a separate Sort Order. A quick example is as follows:

  1. Right click over the field in table view and select a sort order (Ascending or Descending).
  2. Select Format/Sort Order and give the sort order a new name and OK the dialog
  3. Next time you want to work with the table, click on it in Database Manager and select the named sort order directly. Alternatively, open the table and use the Query toolbar to select the sort order.

16. How do I insert a row in database (and how to set a default sort order)

The short answer is you cannot insert rows in database table

Tesco Complete Office Database is a relational database manager and the concept of inserting rows is alien to it's operation (there is no SQL statement to do this). From the database engine's point of view, the question is how to present the records in a given order.

The solution from this perspective is to create a sort order to match the order of records you want. E.g. open a table and select Format/Sort Order.

Also note that best practice when creating a table is to specify a primary key - a field that contains a unique identifier for each record. This is typically a code of some sort. If there is no obvious field, simply add an Incremental field to your table and specify it to be the primary key as follows:

  1. Open the table and select Format/Table
  2. Type a new field name - tablenameID is always a good choice - and set type to Incremental
  3. Click the Format button and set Index to be Primary Key

Note that a table containing a primary key will use this as the default sort order. New records will appear at the bottom of the table but on closing and re-opening the table, the records will be resorted.

17. Parameter entry before running queries

Suppose you have a defined a query and each time you run it, you want to prompt for a paramater or date range.

MS Access will automatically prompt for unknown query parameters. Tesco Complete Office does not do this but it is possible to setup a small system of tables and forms to do the same.

To see how this can be done, please download and open the following example: QUERYPARAMS.ADB

In the above baseTable is the data you want to set the paramters for the query. There are two examples: a set number example (see the form: FormNumberQuery) and a date range example (see the form: FormDateQuery).

These examples are largely self explanatory. In brief, they each use a dummy table to store the query parameters and then relate with the base table, limiting the result to the values in the dummy table.

For a more in depth explanation, please email support stating what it is that is not clear.

is as the default sort order. New records will appear at the bottom of the table but on closing and re-opening the table, the records will be resorted.

 

 
Antivirus & Antispyware - Internet Security - PhotoRestyle - Personal Finance - Complete Office - EasyRecord - Forum -
Site Map - Tesco Software Support - Disclaimer - Privacy - Contact