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,
DianneDid you set up logons and permissions on the sql tables for your users?
You should check all your indexes and relationships as well. Did you use the
Access Upsize Wizard to convert the tables? If so, you will want to
definately go thru each table to check indexes and relationships, and make
sure you have your identity columns set up properly.
There's good book on this "Microsoft Access Developer's Guide to SQL Server"
which I recommend you read. There's a fair bit of learning required to make
this move. I did it several years ago, but read the above book first.
TomT
"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
>|||I have set the logons and permissions on the sql tables on the table level.
Do I also have to go into each column and check the boxes, or does it cover
each column on the table level?
I also could not locate the Access Upsize wizard as part of Access 97. Is
that from a newer version of Access?
And, thank you, I have already ordered the book at your suggestion.
Dianne
"TomT" wrote:
[vbcol=seagreen]
> Did you set up logons and permissions on the sql tables for your users?
> You should check all your indexes and relationships as well. Did you use t
he
> Access Upsize Wizard to convert the tables? If so, you will want to
> definately go thru each table to check indexes and relationships, and make
> sure you have your identity columns set up properly.
> There's good book on this "Microsoft Access Developer's Guide to SQL Serve
r"
> which I recommend you read. There's a fair bit of learning required to mak
e
> this move. I did it several years ago, but read the above book first.
> TomT
> "Dianne" wrote:
>|||Providing permissions at the table level is sufficient, unless you want more
granular control over specific columns within a table.
I gather from your response you did not use the upsize wizard (as I recall
it was a download from Microsoft, and didn't work all that great anyway). Di
d
you just import the tables and data within SQL Server then?
If so, you definately need to make sure you have indexes and primary keys
set up, and if you used Autonumber fields in Access, you'll need to set thos
e
columns in SQL Server as identity columns.
Access won't let you modify data in attached tables without a primary key on
the table, so that could very well be your problem.
As you learn more about SQL Server, you will likely want to move much of
your programming/data processing over to the server side, as that will be
much faster than going thru Jet. Also, check into using Pass Through queries
on the MS Access side, they are quick and often a good way to provide data
for reports, etc.
"TomT" wrote:
[vbcol=seagreen]
> Did you set up logons and permissions on the sql tables for your users?
> You should check all your indexes and relationships as well. Did you use t
he
> Access Upsize Wizard to convert the tables? If so, you will want to
> definately go thru each table to check indexes and relationships, and make
> sure you have your identity columns set up properly.
> There's good book on this "Microsoft Access Developer's Guide to SQL Serve
r"
> which I recommend you read. There's a fair bit of learning required to mak
e
> this move. I did it several years ago, but read the above book first.
> TomT
> "Dianne" wrote:
>|||Yes, I imported the tables through Enterprise Manager in SQL Server. And I
learned the hard way about the primary keys and the indexes, not to mention
the Autonumber. Wish I had asked the right questions before I started.
Thank you for the suggestions about moving the processing over to the SQL
side and using pass through queries - I will certainly need to learn how to
do that.
Dianne
"TomT" wrote:
[vbcol=seagreen]
> Providing permissions at the table level is sufficient, unless you want mo
re
> granular control over specific columns within a table.
> I gather from your response you did not use the upsize wizard (as I recall
> it was a download from Microsoft, and didn't work all that great anyway).
Did
> you just import the tables and data within SQL Server then?
> If so, you definately need to make sure you have indexes and primary keys
> set up, and if you used Autonumber fields in Access, you'll need to set th
ose
> columns in SQL Server as identity columns.
> Access won't let you modify data in attached tables without a primary key
on
> the table, so that could very well be your problem.
> As you learn more about SQL Server, you will likely want to move much of
> your programming/data processing over to the server side, as that will be
> much faster than going thru Jet. Also, check into using Pass Through queri
es
> on the MS Access side, they are quick and often a good way to provide data
> for reports, etc.
> "TomT" wrote:
>