Access 97 to SQL

I have an Access 97 program that for many years linked to tables in another
database through drive letter access. Recently, because of speed, we
converted the Access tables to a SQL Server 2000 database and linked the
tables via an ODBC connection.
The program can read the tables but there are several problems. The append
queries that are trying to build new records in one of the SQL tables keep
giving a key violation error. Also, a few of the screens used to append
records to some of the tables no longer let me add records. It will show th
e
records that were originally imported, and let me delete them, but not add.
The first time I converted, many of the Access queries wouldn't work and we
discovered that in the conversion, the Autonumbering in many of the tables
got lost. Since then, I have set the Identity to Yes and when viewing the
design of the ODBC tables through Access, they are now showing as Autonumber
.
Is there some incompatibility between Access 97 and SQL server 2000, or do I
have the wrong data types in the SQL tables? Is there something wrong with
the way I set up Autonumbering in SQL? When the data was all in Access, I
had no problem appending records, but now that the data is in SQL.... what
am I missing?
Please help!
Thanks,
DianneHi
You may want to ask this is an Access newsgroup.
Autonumbering is done using IDENTITY columns see Books online for more
information.
You may want to look at your tables and data through Enterprise Manager and
Query Analyser if you have a version of SQL Server other than MSDE.
Without knowing what the datatypes are I can not tell if they are wrong!
John
"Dianne" wrote:

> I have an Access 97 program that for many years linked to tables in anothe
r
> database through drive letter access. Recently, because of speed, we
> converted the Access tables to a SQL Server 2000 database and linked the
> tables via an ODBC connection.
> The program can read the tables but there are several problems. The append
> queries that are trying to build new records in one of the SQL tables keep
> giving a key violation error. Also, a few of the screens used to append
> records to some of the tables no longer let me add records. It will show
the
> records that were originally imported, and let me delete them, but not add
.
> The first time I converted, many of the Access queries wouldn't work and w
e
> discovered that in the conversion, the Autonumbering in many of the tables
> got lost. Since then, I have set the Identity to Yes and when viewing the
> design of the ODBC tables through Access, they are now showing as Autonumb
er.
> Is there some incompatibility between Access 97 and SQL server 2000, or do
I
> have the wrong data types in the SQL tables? Is there something wrong wit
h
> the way I set up Autonumbering in SQL? When the data was all in Access, I
> had no problem appending records, but now that the data is in SQL.... wha
t
> am I missing?
> Please help!
> Thanks,
> Dianne
>|||Dianne,
When using identity columns, remember to reseed the table as appropriate. If
the identity column is the primary key, Sql Server might be generating a key
that already exists, but that violates the primary key constraint (I am
assuming this is the error!).
Hope this helps.
Raj Moloye|||Thank you - I had already reseeded the tables to start with the appropriate
record. The problem ended up being a Yes/No field (not one that I was tryin
g
to append) that did not have a default value set. After I set this to zero,
I no longer had the key violation.
Dianne
"Khooseeraj Moloye" wrote:

> Dianne,
> When using identity columns, remember to reseed the table as appropriate.
If
> the identity column is the primary key, Sql Server might be generating a k
ey
> that already exists, but that violates the primary key constraint (I am
> assuming this is the error!).
> Hope this helps.
> Raj Moloye
>
>