Re: Access Database Conversion to SQL

Here are two things that I found when we converted to SQL Server:
1) All SQL Server tables need a primary key
2) If updating SQL Server records in VBA code, you need the dbSeeChanges in
the connect string.

Dan

"Dianne" <Dianne@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3CF0E2D2-509F-492C-8A08-F306416DF21C@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: Implicit conversion from data type text to nvarchar is not allowed.
    ... This is a known issue when using the SQL Server 2005 JDBC Driver to access ... SQL SERVER 2000 DATA CONVERSION LIMITATIONS ... String data longer than 4000 characters cannot be converted ... nvarchar underlying columns if the sendStringParametersAsUnicode ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Upsizing creates huge MSSQL
    ... I used the upsizing wizard for a client several years ago, and many of columns that were created in sql were char instead of varchar, and they were sometimes larger than they needed to be.... ... We discovered what had happened during the conversion, make column data type and length changes, and ensured the tables were indexed correctly, then they were good... ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ...
    (microsoft.public.sqlserver.server)
  • RE: SSMA - Query Conversion to Stored Procedures.
    ... you also want to migrate those queries with parameters into SQL Server ... However you found that none of your Access parameter queries are converted ... to stored procedures and that an undefined error came out. ... from the conversion objects list we can see that Access queries can ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Convert from Float to Varchar
    ... parameter will allow you to choose the formatting for the ... conversion. ... I support the Professional Association for SQL Server ... When I try and convert from float to varchar the ...
    (microsoft.public.sqlserver.programming)
  • Re: Implicit conversion from data type text to nvarchar is not allowed.
    ... I agree that the data conversion issue should not apply to your scenario, ... this case because I am converting String data less than 4000 characters to ... This is a known issue when using the SQL Server 2005 JDBC Driver to ... connection string property is set to false, ...
    (microsoft.public.sqlserver.jdbcdriver)