Access 97 to SQL

[Guest]

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 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 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,

Dianne

 

[G. Vaught]

Some of your problems probably stems from not having the appropriate
permissions set on either the SQL Server for the datbase tables, your Access
front-end,or both. Ensure you have all appropriate permissions set at all
levels, NT, SQL, and Access.

 

[Guest]

I replied to your post in queries, check it

 

[Joe Fallon]

Every table in SQL Server should always have a PK and a timestamp field in
order to interact best with Access.

The timestamp is a data type, but it is not Date/Time!!

The timestamp is changed to a unique value whenever a row is modified.

If you add it to the table, then Access will use it "silently" to check to
see if anyone has changed the record since it was downloaded. (In other
words, the timestamp field does not need to be part of the SELECT
statement.)

Access compares the timestamp it downloaded to the current one and then
allows the update.

When there is no timestamp field, Access has to check *every* field in that
row to see if the data has changed. Not only is this slow, it often fails
due to decimal data type inaccuracies.

Also, check your table to see if you have a boolean field that is type
"Bit". If you have any Bit fields, they MUST have a default value and MUST
NOT be null.

See the following MS KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;278696

For a Microsoft Access 2000 version of this article, see:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280730

 

[Guest]

Thank you, I will incorporate the timestamp field. And you were right on the
money - my key violation error had to do with a bit field that had no default
value, hence, it couldn't append records.

Dianne

 

[Joe Fallon]

Great!
Glad to hear it worked!