Insert Records using MS Access as front end -- sql server new users

sql server new users : Insert Records using MS Access as front end

Gal Zilberman
6/29/2005 12:00:00 AM

Thank you David

My Mistake was having another field which I didn't insert into, and
therefore was assigned a Null which it couldn't accept.
Access didn't show that, thank you for the lesson, I will remeber to use the
Query Analizer in the future.

Thanks again

Gal
[quoted text, click to view]

David Portas
6/29/2005 3:10:12 AM

There is no "autonumber" column in SQL. The IDENTITY property in SQL
Server provides similar functionality and I expect that's what you are
referring to as an auto number column but you should check that that is
the case. Use Enterprise Manager or even better use Query Analyzer to
check rather than rely on what Access tells you about the table
structure. Also check that the IDENTITY column isn't referenced in the
INSERT statement - by default that isn't permitted.

If you've designed your tables properly, an IDENTITY column should
never be the ONLY key of a table. Are you sure no other key exists? Try
the INSERT in Query Analyzer, which will tell you exactly which key was
violated.

--
David Portas
SQL Server MVP
--

Stu
6/29/2005 3:26:11 AM

You should also verify that there IS a primary key defined on SQL
Server, and not just a unique index. Access will see unique indexes
and treat them as primary keys, until it comes time to do INSERTs,
extc. Then it will blow up on you.

Stu

David Portas
6/29/2005 4:50:43 AM

Did you test the code in Query Analyzer to see if you get the same
message?

[quoted text, click to view]

That's plain wrong. The artificial key should not be the only key of
any table. In Access you can get away with this because Access allows
lots of non set-based operations which work on the insertion order
preserved in the table. That's not the case in SQL Server. There are
plenty of scenarios where this kind of flaw undermines integrity and
defeats any feasible solution or at least makes it extremely difficult
to get correct results out of your data.

In your table, why would you want to have multiple rows with the same
firstname, surname and phone number? If you don't want that to happen
then it pays to create the additional key. What's a "simple" table
after all? Isn't the integrity of ALL your data important to you?

In general do not assume a schema design from an Access application
will work in SQL. You should review the design before you port it.

Hope this helps.

--
David Portas
SQL Server MVP
--

Gal Zilberman
6/29/2005 12:05:33 PM

Hi I have a simple Insert Query that I used while the DB was in Access, I've
migrated the data in the SQL server and I get an error message saying that
Access can't Append because there is a Key Violation, the only key is the ID
which I'm not append onto as it's Autonumber is Access.

Thanks

Gal

Gal Zilberman
6/29/2005 2:24:15 PM

There's a Primary Key on the SQL server only on the ID field (there is no
need for more than one PK here as this is a simple table, i.e. ID,
FirstName, Surname, Phone)

The Identity property is set to Yes and the Seed and Increment are 1.

I'm trying to do a simple Insert query from a different table which has the
same fields, including the ID one, but I'm not including it in the Insert
Query.

A very annoying temporary workaround I found is, building an Access table,
inserting the Data into it, where the ID (PK) is Max+1 from the Table I want
to insert to, and then the Insertion from that table worlks fine.

What am I missing?

Gal
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn