primary key programatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to use a make table query from time to time as the data in the table
changes (payments made against original debits). I tried an append query with
the warnings off (to avoid the duplicates in primary key problem) but this
doesn't work. The make table query is the only solution.

The table is on the _be so I don't want users anywhere near it!

Is is possible to inherit the primary key from the original table?

If not, how do I programatically set he primary key after the make table
query has run?
 
I need to use a make table query from time to time as the data in the table
changes (payments made against original debits).
Click to expand...

Ummm...

Why does this involve a MakeTable query? Most accounting applications
use a single credits/debits table with credits positive and debits
negative (or vice versa, just be consistant). At the worst you'ld have
a table of credits and debits?
I tried an append query with
the warnings off (to avoid the duplicates in primary key problem) but this
doesn't work. The make table query is the only solution.
Click to expand...

Please explain.
The table is on the _be so I don't want users anywhere near it!
Click to expand...

Since users should never see any tables at all, I agree.
Is is possible to inherit the primary key from the original table?
Click to expand...

With VBA code or with a DDL "Create Table" query followed by an Append
query. MakeTable queries cannot set indexes.
If not, how do I programatically set he primary key after the make table
query has run?
Click to expand...

It's possible... but I'm not at all convinced you're not going all
around the barn to do something that doesn't need a new table at all!

John W. Vinson[MVP]
 
Can I ask what is meant by, "The table is on the _be"?
Click to expand...

I'm guessing "New To Access" has a split database with the tables in
the "backend"; the database splitter wizard names the new backend
database by appending the characters "_be" to the database name.

John W. Vinson[MVP]
 
John Vinson said:
Ummm...

Why does this involve a MakeTable query? Most accounting applications
use a single credits/debits table with credits positive and debits
negative (or vice versa, just be consistant). At the worst you'ld have
a table of credits and debits?


Please explain.


Since users should never see any tables at all, I agree.


With VBA code or with a DDL "Create Table" query followed by an Append
query. MakeTable queries cannot set indexes.


It's possible... but I'm not at all convinced you're not going all
around the barn to do something that doesn't need a new table at all!

John W. Vinson[MVP]
I agree and the db is actually structured with separate tables for the debits and credits. The user wants a "mega ledger Table" which incorporates the data I mentioned and other data from other tables. This is used as an audit trail and is also exported and delivered to the Directors of the Company who want to "see it all in one place". I really don't have a choice.
Click to expand...
 

It is possible - preferable, in fact - to export a file from a Query.
You do have a choice, if that's the only issue! A UNION query will let
you string together the debits and credits into one recordset for
instance.

But if you MUST do it as a MakeTable, then you really have no option
other than to create the table (in the user interface or in code) with
the desired field types, sizes, indexes, primary keys, etc. and then
run an Append query. A MakeTable query (as you know) cannot set a
primary key. You can create a unique Index programatically, and even
name it PrimaryKey, but that's going to run into real problems if
there is nonunique data; creating the primary key first in the empty
table will at least let you trap the errors as they go in, rather than
too late.


John W. Vinson[MVP]
 
Can I ask what is meant by, "The table is on the _be"?
I'm guessing "New To Access" has a split database with the tables in
the "backend"; the database splitter wizard names the new backend
database by appending the characters "_be" to the database name.

John W. Vinson[MVP]
Click to expand...

Ah! Thank you. :)

Chris O.
 
Back
Top