Access 97 to SQL Conversion

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
Is your front-end Access 97? If so, then you'll solve a lot of your
problems by upgrading to Access 2003. Access 97 is no longer
supported, and never worked smoothly with SQLS 2000, mostly due to
data type incompatibilities.
--Mary
On Fri, 20 May 2005 11:49:37 -0700, Dianne
<Dianne@.discussions.microsoft.com> wrote:

>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
|||You are so right! I am upgrading the program as we speak. Thanks!
Dianne
"Mary Chipman [MSFT]" wrote:

> Is your front-end Access 97? If so, then you'll solve a lot of your
> problems by upgrading to Access 2003. Access 97 is no longer
> supported, and never worked smoothly with SQLS 2000, mostly due to
> data type incompatibilities.
> --Mary
> On Fri, 20 May 2005 11:49:37 -0700, Dianne
> <Dianne@.discussions.microsoft.com> wrote:
>
>
|||"Dianne" wrote:
[vbcol=seagreen]
> You are so right! I am upgrading the program as we speak. Thanks!
> Dianne
> "Mary Chipman [MSFT]" wrote:

Access 97 to SQL Conversion

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,
DianneIs your front-end Access 97? If so, then you'll solve a lot of your
problems by upgrading to Access 2003. Access 97 is no longer
supported, and never worked smoothly with SQLS 2000, mostly due to
data type incompatibilities.
--Mary
On Fri, 20 May 2005 11:49:37 -0700, Dianne
<Dianne@.discussions.microsoft.com> wrote:

>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 t
he
>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 Autonumbe
r.
>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|||You are so right! I am upgrading the program as we speak. Thanks!
Dianne
"Mary Chipman [MSFT]" wrote:

> Is your front-end Access 97? If so, then you'll solve a lot of your
> problems by upgrading to Access 2003. Access 97 is no longer
> supported, and never worked smoothly with SQLS 2000, mostly due to
> data type incompatibilities.
> --Mary
> On Fri, 20 May 2005 11:49:37 -0700, Dianne
> <Dianne@.discussions.microsoft.com> wrote:
>
>|||"Dianne" wrote:
[vbcol=seagreen]
> You are so right! I am upgrading the program as we speak. Thanks!
> Dianne
> "Mary Chipman [MSFT]" wrote:
>|||I am experiencing the same issues with SQL Server 2005 and Access 97. I don'
t
think it is cost effective for us to convert to Access 2003 because we would
have to purchase 12+ liscenses and we are re-writing the app in ASP.Net afte
r
we get the backends converted and stable.
So ... is there any workarounds for this incompatibilities? What are they in
more detail, I am just testing and finding the same issues Dianne is having
(certain forms will not allow edits, some DOA recordsets will not not add
records?).
Thanks ahead of time, Rick
"Mary Chipman [MSFT]" wrote:

> Is your front-end Access 97? If so, then you'll solve a lot of your
> problems by upgrading to Access 2003. Access 97 is no longer
> supported, and never worked smoothly with SQLS 2000, mostly due to
> data type incompatibilities.
> --Mary
> On Fri, 20 May 2005 11:49:37 -0700, Dianne
> <Dianne@.discussions.microsoft.com> wrote:
>
>|||From a SQL Server perspective, you can TRY running the SQL
Server 2005 user database is a lower compatibility mode but
if you are using a front end that's no longer supported, it
would probably just minimize some of the issues - if even
that.
But you'd probably find more information on working around
the Access 97 issues in one of the Microsoft Access
newsgroups.
-Sue
On Fri, 29 Sep 2006 14:12:01 -0700, Rick Vooys
<RickVooys@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am experiencing the same issues with SQL Server 2005 and Access 97. I don
't
>think it is cost effective for us to convert to Access 2003 because we woul
d
>have to purchase 12+ liscenses and we are re-writing the app in ASP.Net aft
er
>we get the backends converted and stable.
>So ... is there any workarounds for this incompatibilities? What are they i
n
>more detail, I am just testing and finding the same issues Dianne is having
>(certain forms will not allow edits, some DOA recordsets will not not add
>records?).
>Thanks ahead of time, Rick
>"Mary Chipman [MSFT]" wrote:
>|||Thanks Sue. Can you elaborate on the lower capability mode? What would we
lose? Is this something you can just switch on and off? How would one go int
o
one of these modes?
"Sue Hoegemeier" wrote:

> From a SQL Server perspective, you can TRY running the SQL
> Server 2005 user database is a lower compatibility mode but
> if you are using a front end that's no longer supported, it
> would probably just minimize some of the issues - if even
> that.
> But you'd probably find more information on working around
> the Access 97 issues in one of the Microsoft Access
> newsgroups.
> -Sue
> On Fri, 29 Sep 2006 14:12:01 -0700, Rick Vooys
> <RickVooys@.discussions.microsoft.com> wrote:
>
>|||You'd loose the functionality introduced in the higher
version. You can change between different compatibility
levels and change back to 9.0. To do so, you would executed
sp_dbcmptlevel. You can find a lot of information on this
system stored procedure and the behavioral, functional
differences in books online under sp_dbcmptlevel.
-Sue
On Mon, 2 Oct 2006 09:53:02 -0700, Rick Vooys
<RickVooys@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Sue. Can you elaborate on the lower capability mode? What would we
>lose? Is this something you can just switch on and off? How would one go in
to
>one of these modes?
>"Sue Hoegemeier" wrote:
>

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 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
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 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 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
>
|||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 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:
|||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). 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 those
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 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:
|||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 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). 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 those
> 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:
sql

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,
DianneHi
You may want to ask this is an Access newsgroup.
Autonumbering is done using IDENTITY columns see Books online for more
information.
You may want to look at your tables and data through Enterprise Manager and
Query Analyser if you have a version of SQL Server other than MSDE.
Without knowing what the datatypes are I can not tell if they are wrong!
John
"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
>|||Dianne,
When using identity columns, remember to reseed the table as appropriate. If
the identity column is the primary key, Sql Server might be generating a key
that already exists, but that violates the primary key constraint (I am
assuming this is the error!).
Hope this helps.
Raj Moloye|||Thank you - I had already reseeded the tables to start with the appropriate
record. The problem ended up being a Yes/No field (not one that I was tryin
g
to append) that did not have a default value set. After I set this to zero,
I no longer had the key violation.
Dianne
"Khooseeraj Moloye" wrote:

> Dianne,
> When using identity columns, remember to reseed the table as appropriate.
If
> the identity column is the primary key, Sql Server might be generating a k
ey
> that already exists, but that violates the primary key constraint (I am
> assuming this is the error!).
> Hope this helps.
> Raj Moloye
>
>

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 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,
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 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
>|||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:
> 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 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 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
> >|||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). 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 those
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:
> 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 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 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
> >|||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:
> 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). 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 those
> 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:
> > 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 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 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
> > >

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

about using osql BACKUP/RESTORE commands

Hi!
I'm using MSDE 2000 SP4 on local computer.
I backed up database into local hard drive using osql command like...
OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'" >
LogBackupDatabase.log
...and it's working fine. But when I try to save backup file directly
into network drive, it's not working. "LogBackupDatabase.log"-file
contains error message...
"Msg 3201, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
Cannot open backup device 'Z:\MyFolder\MYDB.bak'. Device error or device
off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
BACKUP DATABASE is terminating abnormally."
Z: is mapped network drive.
Is it possible to save backup file into network drive somehow? Maybe my
osql is not correctly formed to save into backup file into network
drive, or am I missing something?
Also restore command using osql is not working if I try to restore
backup file from network drive.
Thanks in advance!
Mika
Hi,
Use UNC (REMOTESERVER\SHARE\PATH\) path along with Backup or Restore
commands.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharenameX\dbname.bak' with
init
Perform the same for Restore Database
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
SQL Server MVP
"Mika M" <mahmik_removethis@.luukku.com> wrote in message
news:OASY$mStFHA.3452@.TK2MSFTNGP14.phx.gbl...
> Hi!
> I'm using MSDE 2000 SP4 on local computer.
> I backed up database into local hard drive using osql command like...
> OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'" >
> LogBackupDatabase.log
> ...and it's working fine. But when I try to save backup file directly into
> network drive, it's not working. "LogBackupDatabase.log"-file contains
> error message...
> "Msg 3201, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
> Cannot open backup device 'Z:\MyFolder\MYDB.bak'. Device error or device
> off-line. See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
> BACKUP DATABASE is terminating abnormally."
> Z: is mapped network drive.
> Is it possible to save backup file into network drive somehow? Maybe my
> osql is not correctly formed to save into backup file into network drive,
> or am I missing something?
> Also restore command using osql is not working if I try to restore backup
> file from network drive.
> --
> Thanks in advance!
> Mika
|||hi,
Mika M wrote:
in addition to Hari valuable hint, please have a look at
http://support.microsoft.com/?id=283811 in order to modify the account SQL
Server and SQL Server Agent are running on...
unfortunately this article is no longer alligned with modifications applied
with sp4.. and you will no longer be able to start and run SQL Server and
the Agent only using the reported info..
I'm still trying troubleshooting it..
try "propagating" file permissions to all sub folders as described, as long
as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
make those accounts member of the local sysadmins WinNT role
but i definitevely hope kb article 283811 get updated..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

About how many writes per sec for a single drive?

Deos anybody have a rough estimate for how many writes per second a
single drive system (hardware mirrored to a second drive) should be
able to handle? (ie: Win2000, IIS, apps, SQL, db + log files -- all on
one drive)
I know that's a slippery question, but I'm just looking for an order
of magnitude... Should I expect to max out at about 1-5 writes/sec?
20-50? 100-200?
On the other hand, if I were to bump that up to a RAID-10 with 3
stripped drives, mirrored to a second set of three -- in that case,
about how many writes per second should I expect?
I'm trying to build a relatively inexpensive box to do the job,
without going crazy and over-engineering it. Even a rough sense of
writes/sec should help me better understand what sort of disk system I
might need...
Many Thanks,
Scott> I know that's a slippery question, but I'm just looking for an order
> of magnitude... Should I expect to max out at about 1-5 writes/sec?
> 20-50? 100-200?
The hardware drives themselves can do in the order 100 write/second.
Depends a bit on how near the writes are. Most drives nowadays
have a 'seek' time around 8 milliseconds. So if the drive has to
do a 'seek' for each write and one complete rotation (depending
on the structure it could be less or more). A rotation (7200 pm =around 8 millisecond for one rotation) is also 8 milli's this would
be 16 milli's for a write. This comes to 62 writes a second. If
the drive is a bit intelligent and the writes occure close to eachother
or are sorted by the drive 100 writes per second is a fair number.
For raid devices were 3 disks can be used at the same time.
the number of 'small' writes can be 3 times as high as a single
disk. (Using 3 disks). (But only if data is needed from the 3 different
devices). This is seldom the case.
Cheap raid solutions can not handle reads or writes at the
same time on different positions on the three disks. With cheap
read devices you only win when accessing large chunks.
If accesses are short, raid wil perform better than a single drive,
but this is often expressed in a percentage and not in a factor.
This report is produced from memory, and as we all know memory
is not build to keep data accurately but to survive. So reading my
numbers this should be taken into account.
ben brugman
> On the other hand, if I were to bump that up to a RAID-10 with 3
> stripped drives, mirrored to a second set of three -- in that case,
> about how many writes per second should I expect?
> I'm trying to build a relatively inexpensive box to do the job,
> without going crazy and over-engineering it. Even a rough sense of
> writes/sec should help me better understand what sort of disk system I
> might need...
> Many Thanks,
> Scott|||There is a great book on I/O performance, its called SQL
Server 2000 performance tuning from MS Press by Edward
Whalen, et al. -- ISBN 0-7356-1270-6. Go to page 73 and
it discusses all that you are asking about. For a general
ball park answer on a RAID 10, you should track the
following with System Monitor:
Disk Reads/sec
Disk Writes/sec
Avg. Disk Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write
I/Os per disk = [Reads + (2 * Writes)]/Number of Disks
For example, a 9GB/10,000 RPM disk should have the
capacity of 100 I/Os per sond for random I/O. Your milage
may vary depending on you disk subsystem.
Later,
Chris
>--Original Message--
>Deos anybody have a rough estimate for how many writes
per second a
>single drive system (hardware mirrored to a second drive)
should be
>able to handle? (ie: Win2000, IIS, apps, SQL, db + log
files -- all on
>one drive)
>I know that's a slippery question, but I'm just looking
for an order
>of magnitude... Should I expect to max out at about 1-5
writes/sec?
>20-50? 100-200?
>On the other hand, if I were to bump that up to a RAID-10
with 3
>stripped drives, mirrored to a second set of three -- in
that case,
>about how many writes per second should I expect?
>I'm trying to build a relatively inexpensive box to do
the job,
>without going crazy and over-engineering it. Even a rough
sense of
>writes/sec should help me better understand what sort of
disk system I
>might need...
>Many Thanks,
>Scott
>.
>|||An excellent article here.
http://research.microsoft.com/research/pubs/view.aspx?
tr_id=686
>--Original Message--
>Deos anybody have a rough estimate for how many writes
per second a
>single drive system (hardware mirrored to a second drive)
should be
>able to handle? (ie: Win2000, IIS, apps, SQL, db + log
files -- all on
>one drive)
>I know that's a slippery question, but I'm just looking
for an order
>of magnitude... Should I expect to max out at about 1-5
writes/sec?
>20-50? 100-200?
>On the other hand, if I were to bump that up to a RAID-10
with 3
>stripped drives, mirrored to a second set of three -- in
that case,
>about how many writes per second should I expect?
>I'm trying to build a relatively inexpensive box to do
the job,
>without going crazy and over-engineering it. Even a rough
sense of
>writes/sec should help me better understand what sort of
disk system I
>might need...
>Many Thanks,
>Scott
>.
>|||Hi
If you want to read up on this sort of thing then Microsoft SQL Server 2000
Performance Tuning Technical Referencby Whelan, Garcia, DeLuca and Thompson
ISBN 0-7356-1270-6 is worth reading.
The actual numbers will be dependent on the drives specifications. The
examples quoted in the above book work out at about 125 I/Os per second, but
reading and writing would both have to be considered for as system (assuming
that data is retrieved) therefore the distribution of these will effect the
overall figures. This factor will be the same for both RAID1 and RAID10, but
the number of discs will reduce this value proportionaly for RAID10.
John
"Scott Matthews" <scott@.turnstyle.com> wrote in message
news:f11b846.0311280630.78dd010c@.posting.google.com...
> Deos anybody have a rough estimate for how many writes per second a
> single drive system (hardware mirrored to a second drive) should be
> able to handle? (ie: Win2000, IIS, apps, SQL, db + log files -- all on
> one drive)
> I know that's a slippery question, but I'm just looking for an order
> of magnitude... Should I expect to max out at about 1-5 writes/sec?
> 20-50? 100-200?
> On the other hand, if I were to bump that up to a RAID-10 with 3
> stripped drives, mirrored to a second set of three -- in that case,
> about how many writes per second should I expect?
> I'm trying to build a relatively inexpensive box to do the job,
> without going crazy and over-engineering it. Even a rough sense of
> writes/sec should help me better understand what sort of disk system I
> might need...
> Many Thanks,
> Scott