Access 97 to SQL

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

  • Access Database Conversion to SQL
    ... database through drive letter access. ... queries that are trying to build new records in one of the SQL tables keep ... discovered that in the conversion, the Autonumbering in many of the tables ...
    (microsoft.public.data.odbc)
  • Access 97 to SQL Conversion
    ... database through drive letter access. ... queries that are trying to build new records in one of the SQL tables keep ... discovered that in the conversion, the Autonumbering in many of the tables ...
    (microsoft.public.sqlserver.odbc)
  • DBConvert for FoxPro & MSSQL 2.0.2
    ... DBConvert for MS FoxPro & MS SQL is a database migration tool for data ... Unicode Support, Primary keys and Indexes conversion, ...
    (comp.software.shareware.announce)
  • DBConvert for Firebird and MS SQL 1.0.0
    ... performs two-way conversion from Firebird databases to MS SQL server ... database migration to another MS SQL database and is quite adept at ... conversion of InterBase databases too. ...
    (comp.software.shareware.announce)
  • Re: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)