Using DTS

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

Hi,
After spending over 8 hours of importing tables from Access into SQL and
modifying them to fit the forms and queries of Access, I realized that all
the tables had the data duplicated with their respective ID.
What have I done wrong? The DTS wizard didn't alert me of a problem.
Now, SQL doesn't even allow me to modify the data or erase it because of its
duplicity.
TIA,
Amy
SQL2k SP3a
 
Did the field that was your Access ID get defined as unique, or a PK in SQL
Server? If not, and you ran the DTS package(s) more than once, that would
explain it.
 
You can get rid of duplicates by using
SELECT Distinct FieldA, FieldB,...
INTO tblNewTable
FROM tblOldTable

Then rename the old and new tables.
 
Thanks you to you both.

Now I have an additional delema. When passing data from Access forms to SQL
tables, I receive a message stating that a value NULL cannot be inserted in
the column ID. The latter is set to primary key intencionally.
I even set the defaulf on the form to =[Max] +1 for the ID field but with no
avail.
How do I get around it?
TIA
 
My guess would be that you are using MAX incorrectly. I believe an
expression is required....something like:

=Max(
.[ID])+1

Perhaps not the correct syntax, but along those lines..
 
Is there a reason why you wouldn't set the column in SQL Server to Identity?
Is [Max] a field name? If you wanted to but an expression in the Default
Value property, it would have to use DMax(....). You may then have other
issues with multiple users creating new records at nearly the same time.
 
Hi,
you're both right. Max is Spanish as DMax is to English.
However, didn't do the trick.
How do I set my ID column to Identity?
I tried

ALTER TABLE tblwarranty ADD columnID INT NULL
CONSTRAINT exb_unique UNIQUE

but no go. In fact, it didn't do anythink.
 
I would just change it using the Enterprise Manager interface.

--
Duane Hookom
MS Access MVP


Mike said:
Hi,
you're both right. Max is Spanish as DMax is to English.
However, didn't do the trick.
How do I set my ID column to Identity?
I tried

ALTER TABLE tblwarranty ADD columnID INT NULL
CONSTRAINT exb_unique UNIQUE

but no go. In fact, it didn't do anythink.


Mike said:
Thanks you to you both.

Now I have an additional delema. When passing data from Access forms to SQL
tables, I receive a message stating that a value NULL cannot be inserted in
the column ID. The latter is set to primary key intencionally.
I even set the defaulf on the form to =[Max] +1 for the ID field but
Click to expand...
with
no
avail.
How do I get around it?
TIA
Click to expand...
Click to expand...
 
Back
Top