Re: Access 97 to SQL

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

--
Joe Fallon
Access MVP



"Dianne" <Dianne@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:76728960-886B-4D8D-AD8C-B22848DA444C@xxxxxxxxxxxxxxxx
>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
>


.

Relevant Pages

  • Re: Recordset Update Error
    ... SQL Server bit fields need to be declared as ... Add a timestamp field to each table. ... The timestamp is a data type, ... 'Opens Recordset ...
    (microsoft.public.access.externaldata)
  • Re: Import Table Error/Access 2003
    ... You do not say what type of DB you are importing from. ... Every table in SQL Server should always have a PK and a timestamp field in ... The timestamp is a data type, ... the timestamp field does not need to be part of the SELECT ...
    (microsoft.public.access.externaldata)
  • Re: Access 95 Linked Tables with SQL Server 2000
    ... Try adding a timestamp field to a table and re-linking it. ... > The table were copied using DTS into SQL Server. ... > exception of the "Memo" data type, ... I cannot convert the front-end Access database until ...
    (microsoft.public.access.externaldata)
  • Re: Recordsetclone on SQL backend
    ... TimeStamp field in the tables? ... SQL Server, but I seem to remember that in order for the form to be ... Dim rst As DAO.Recordset ... !ShipToName = Me!ShipToName ...
    (microsoft.public.access.modulesdaovba)
  • Re: Data Types
    ... > moved to SQL Server and I don't know the best newsgroups yet. ... > Does a Unique Identifier data type take up less storage space than a 32 ... A uniqueidentifier uses 16 bytes. ... but I'm looking at the data types and think that the TimeStamp ...
    (microsoft.public.sqlserver.programming)
Loading