Hi,

I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with one-to-one relationship but on this
occasion
I must keep username/password details within a seperate table.

Here's the basic specs and database schema:
-------------------------------------------

Database: Basic MS Access 2000 MDB
Table Relationships: Both tables are joined one-to-one on their ID fields.

TableMembers
ID: Long Integer; Required; Indexed(Unique); PrimaryKey;
Firstname: Text 50; Required;
Lastname: Text 50; Required;
Telephone: Text 50; Required;

TableAccounts
ID: AutoNumber; Required; Indexed(Unique); PrimaryKey;
Username: Text 50; Required;
Password: Text 50; Required;


The problem:
------------
In an ideal world I would like to use a single SQL INSERT on both tables to
insert
the required data. But I know this isn't possible as INSERT works on single
tables only, right ?

I cannot use an SQL INSERT on TableMembers as the ID field is created by the
AutoNumber
in TableAccounts.ID.

I could INSERT into TableAccounts first, which would auto-create the ID, but
how do I use the
ID in the second SQL INSERT on TableMembers ???

When I open the TableAccounts Table in Access, I can manually input all data
for both Tables
without any problems. TableMembers Fields are displayed as a dropdown record
from TableAccounts
and the ID field is automatically taken care of.

Can Anyone here can offer some advice or solutions, all replies greatly
appreciated.

Carl.

"Carl" wrote

First, unless you have beaucoup data fields that you didn't list, why would
you want to have these two tables with a one-to-one relationship? Obviously
the simplest, easiest solution is to include all the information in a single
table and you don't have to worry about matching keys.

Retrieve the record you just entered*, and create the INSERT SQL with code,
using that value for the key field just as you use values for any other
field.

* this may not be as easy as it seems, which
may be a good argument, in some cases, for
using a "natural key" rather than using AutoNumber
as a surrogate key.

Larry Linson
Microsoft Access MVP

Do you have any experience with visual basic? If so, you could do at least a
couple different things:
1) you could create your own routine to generate the ID value so that you
don't have to use an autonumber field, and then you could insert into each
table with your ID value.
2) you could use vb to insert the fields into the Accounts table, then read
the ID of the newly inserted ID value, then insert that ID into the Members
table.

"Carl" <mail2carl (AT) _remove_yahoo (DOT) com> wrote

Larry....the tables need to be seperate because they will be accesed by
another third-party with strict rules on the fields. Maybe I should of
mentioned
that I would be using a Perl script to run the SQL INSERT.

Retrieving the record after the INSERT may work if I could somehow have the
SQL return
the ID after INSERT as completed.

I like the idea of using an AutoNumber as it functions as you expect it to
without
the need to create possible problems with your own AutoNumbering routines.

Just seems such a much more simple solution would be to INSERT into both
tables
using the same SQL query....I dont understand why this isn't possible !

thanx...




"Larry Linson" <bouncer (AT) localhost (DOT) not> wrote

"Larry Linson" <bouncer (AT) localhost (DOT) not> wrote in
news:ZB_we.24799$Ff6.8496@trnddc09:

Basically, I agree with Larry's reservations about the data
structure. However, I've had applications where I needed to break
down single tables into 1:1 sub-tables because the application
needed what amounted to record subtypes.

There are two basic ways to do this.

1. have a field in main side of the 1:1 join that has only one
purpose, to hold a value that tells you "this record is being
inserted right now." You'd put something like -10000000 in it during
the insert, then use that value to identify the single record you've
just added so you can select its PK value to insert into the second
table. After you populate the second table, you set this special
field in the first table to Null. The problem with this approach is
that it doesn't work well in multi-user applications, unless you
make the user name part of the ID field you're using to find the
record that was just inserted.

2. write VBA code to insert the record in an AppendOnly recordset,
so you can grab the PK value, then use that to write INSERT SQL to
insert the record in the second table. You'd probably want to wrap
this in a transaction to insure that the whole process completes.
That is, if part of it fails you probably want the whole thing to
fail.

The 2nd choice is much more complex, but far more robust.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Hi John.....I do but I prefer Perl as it will be a internet based
database running on Unix and Win32.

It looks like the only solution is to create my own ID generation routines.
I cannot rely on a INSERT then a SELECT to get the last ID entered.
There may be a situation where after the INSERT to the AccountsTable,
another client may create a new Account which would trigger another
INSERT.

How do you "read" the ID of the newly inserted ID ??? What do you mean by
this ??
What happens if another INSERT is triggered before I've "read" the previous
ID ??

I was under the impression that INSERT querys don't return anything.

thanx for reply.


"John Welch" <jwelch (AT) fred (DOT) com> wrote

Hi David.....

Your Solution 1 seems a good idea, it's a bit of a "hack" but you got
me thinking that this could work well. Maybe I could use the time
as another identifer.......I'll give it a try on the test server.

Your other solution I will look into.....not quite sure of a recordset,
will look into this though, if it's more preferred robust method.

thanx again..Carl.

"David W. Fenton" <dXXXfenton (AT) bway (DOT) net.invalid> wrote

Hi Carl, what I mean by "reading" the previous ID is similar to David's idea
#2: use a recordset (which is an Active X data Object (ADO)thing - not sure
what you'd do with perl) to append your new record, then get the Id of that
new record (using a method of the recordset object), making sure you account
for multiusers, i.e. either using a transaction or a timestamp or something
to make sure you're getting the ID from the record you just added.

"Carl" <mail2carl (AT) _remove_yahoo (DOT) com> wrote

Thanx all for feedback, I decided use your suggestions by creating a another
field to
identify a new account record....below is the SQL I am using to update both
tables.

I am using a combination of the Date and Time to create the DateTimeStamp
which is processed by the perl script.

TableAccounts Insert Query
--------------------------
INSERT INTO TableAccounts ( DateTimeStamp, Username, [Password] )
SELECT 123456 AS Expr1, 'JoeBloggs' AS Expr2, 'Dumbo' AS Expr2 FROM
TableAccounts
INNER JOIN TableMembers ON TableAccounts.ID = TableMembers.Account_ID;

TableMembers Insert Query
--------------------------
INSERT INTO TableMembers ( AccountID, Firstname, Lastname, Telephone)
SELECT DISTINCT TableAccounts.ID AS Expr1, 'Joe' AS Expr2, 'Bloggs' AS
Expr3, '012233' AS Expr4
FROM TableAccounts WHERE (((TableAccounts.DateTimeStamp)=123456));

Not sure if really need to us the INNER JOIN on INSERTS. Access adds them by
default.
I much prefer to use INSERT INTO tablename (field1,field2,field3) VALUES
(1,2,3)
but Access converts it all into SELECT AS Expr1 etc...no idea why ???

Thanx again for the ideas guys.

"Larry Linson" <bouncer (AT) localhost (DOT) not> wrote