Help - Problem appending new table

Help - Problem appending new table

Post by ADRIAN REILL » Sat, 08 Mar 1997 04:00:00



Hello,
I am tring to store data entered at run time to a database so that it can
be recalled at a later point. The database and table creation code is shown
below:

Set vMyDataBase = Workspaces(0).CreateDatabase("Password.mdb",
dbLangGeneral, dbEncrypt)
Set vMyDataBase = Workspaces(0).OpenDatabase("Password.mdb")
Set vTable = vMyDataBase.CreateTableDef("User Details")

Set vField = vTable.CreateField("User Name", dbText, 25)
vField.Attributes = dbAutoIncrField
vTable.Fields.Append vField

Set vField = vTable.CreateField("Password", dbText, 25)
vTable.Fields.Append vField

'Appending the new TableDef to the database objects collection.
vMyDataBase.TableDefs.Append vTable

However, the CreateTableDef doesn't seem to be working. An error is
generated when I try to append the new TableDef to the database. As such
when I try and 'AddNew' items to the database having specified the table
that I want to work with the object is not found.

I'm new at this and desperately need help. Am I doing something stupid or
just not doing something?  Any help or suggestions would be most welcome.

Thanks.
Adrian  

 
 
 

Help - Problem appending new table

Post by Jim Fergus » Sun, 09 Mar 1997 04:00:00


Adrian,

Without going over your code line by line I can see several problems.
For example:

Quote:>Set vField = vTable.CreateField("User Name", dbText, 25)
>vField.Attributes = dbAutoIncrField

An AutoIncrementing field must be a long integer, not a text field

-- Jim Ferguson, FMS
   http://www.fmsinc.com

 
 
 

1. Problem with appending one table to two tables - referential integrity - help please

Hi there,

First of all, we are using Access 97 clients and SQL 6.5 backend.

What I'd like to do is use an append query to append data from one table to
two other tables. I've already setup a query to show all the fields from
the two tables so I can use that query in the append to destination.

Problem: CustomerID in the Customers table is linked to CustomerID in the
Bookings table - and it is not an auto-number, it is entered by hand from
firstname/lastname fields. I've gotten around that problem by using an
autonumber in the table where I want to append from - and include that
autonumber field in the append query. It seems to work alright if I have
only fields from the Customers table (where customerid is the primary key),
but whenever I add fields from the booking table (which is linked from
customers table via customerid, but the primary key is bookingid - not
really used much) I get the Access 97 key violation error.

There must be some way to add the customerid to both tables at the same
time without the key violation / referental integrity error message. I've
tried already to put no customerid in the append query - doesn't work.
Append CustomerID to both CustomerID fields in the append query - doesn't
work. Append CustomerID only to the Customers table - doesn't work. Append
CustomerID only to the Bookings table - doesn't work, but at least the
error message is different - field not updateable - which is logical of
course.

I tried to define the relationship as cascade update related fields, which
I believe should fix that problem, but I can't seem to select it. My guess
is that's because it comes from the SQL server. On the server side the
Customers.CustomerID is the primary key in the Customers table and a
foreign key in the bookings table.

Thanks for your time & help! :-)

        Thomas

--
***********************************************************************
Thomas Fischer             The Road goes ever on and on
Planet Holiday               Out from the door where it began.
                                    Now far ahead the Road has gone,
Anacott Travel Network   Let others follow it who can!
Internet Reservations      Let them a journey new begin,
Tel:  +66-76 263-239      But I at last with weary feet
Fax: +66-76 263-586      Will turn towards the lighted inn,
                                    My evening-rest and sleep to meet.
http://www.planetholiday.com                      - J.R.R Tolkien
***********************************************************************

2. memory violation on 9.1C for Linux

3. Help on appending new records in SQL Tables

4. JDBC for Sybase Anywhere 5.5

5. HELP, please: Error by appending a new table

6. Array indexes & MouseMove Event Problem

7. How to append new column to table

8. Problems with SQL 7.0 trial install

9. Append new generated data into existing database table

10. Select into new table and append

11. Appending Tables to a new Database

12. Appending new records to existing table

13. Can't append new records to a table of