access data at web server

I have placed my sql server database files on the database webspace given for my site. I am not being able to access the tables residing in my database on web.

Same database is working well at my localhhost. Why am i getting this kind of error?

Please explain "webspace given for my site" - - is this a hosted site, etc?

what error are you actually getting?

|||

The webspace given is a www folder and a db folder. www folder to keep all my aspx pages and db to keep my sqlserver database files .

The name of my database files are dodg_data.mdf and dodge_log.ldf The database name and user id given by my webhost is different , I have changed my file names according to that before uploading.

My database is not being accessed at all. I am getting inavalid object name'detail' error . detail is my tablename.

same database is working with my localhost.

I need help...........

|||

augustwind:

Please explain "webspace given for my site" - - is this a hosted site, etc?

what error are you actually getting?

The webspace given is a www folder and a db folder. www folder to keep all my aspx pages and db to keep my sqlserver database files .

The name of my database files are dodg_data.mdf and dodge_log.ldf The database name and user id given by my webhost is different , I have changed my file names according to that before uploading.

My database is not being accessed at all. I am getting inavalid object name'detail' error . detail is my tablename.

same database is working with my localhost.

I need help...........

|||You need to work with your host on this. They have most likely NOT given you a database folder for your SQL Server database, as this would imply that they would allow you to use SQL Express 2005, which most hosts would not do. They have instead likely given you space on their SQL Server. You would need to connect to this SQL Server and transfer your database to it.|||

tmorton:

You need to work with your host on this. They have most likely NOT given you a database folder for your SQL Server database, as this would imply that they would allow you to use SQL Express 2005, which most hosts would not do. They have instead likely given you space on their SQL Server. You would need to connect to this SQL Server and transfer your database to it.

Hi Terri,

But i am not getting it.Is keeping database files into the folder given is not enough. how can i get connected to their server. I am using connection string with username and password given by them.

Access controlon line level

Is it possible to implement control access on the line level usning standard
features?
I had to implement several tables User, groupe, article ..., map users to
groups and articles as well as some Sps in order that users or group of users
acces articles they are allowed to diplay.
I would highly appreciate any help
Thanks
"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
Not sure what you mean by "line level". If you mean on a per-row level,
then yes it is.
Especially if you use stored procs.
I'd keep a table of users and values. Each row would have a corresponding
value.
Do all DML work through a stored proc. So if you want a user to view data,
the stored proc would look at their credentials and as part of the WHERE
clause would select only rows that meet that set of credentials (plus any
others required for that specific query.)
It's also possible to do this via views if you want.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
I think your question is about row-level security. Use stored procedures and
views to limit the rows that individual users can access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Access controlon line level

Is it possible to implement control access on the line level usning standard
features?
I had to implement several tables User, groupe, article ..., map users to
groups and articles as well as some Sps in order that users or group of users
acces articles they are allowed to diplay.
I would highly appreciate any help
Thanks"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
Not sure what you mean by "line level". If you mean on a per-row level,
then yes it is.
Especially if you use stored procs.
I'd keep a table of users and values. Each row would have a corresponding
value.
Do all DML work through a stored proc. So if you want a user to view data,
the stored proc would look at their credentials and as part of the WHERE
clause would select only rows that meet that set of credentials (plus any
others required for that specific query.)
It's also possible to do this via views if you want.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
I think your question is about row-level security. Use stored procedures and
views to limit the rows that individual users can access.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Access controlon line level

Is it possible to implement control access on the line level usning standard
features?
I had to implement several tables User, groupe, article ..., map users to
groups and articles as well as some Sps in order that users or group of user
s
acces articles they are allowed to diplay.
I would highly appreciate any help
Thanks"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
Not sure what you mean by "line level". If you mean on a per-row level,
then yes it is.
Especially if you use stored procs.
I'd keep a table of users and values. Each row would have a corresponding
value.
Do all DML work through a stored proc. So if you want a user to view data,
the stored proc would look at their credentials and as part of the WHERE
clause would select only rows that meet that set of credentials (plus any
others required for that specific query.)
It's also possible to do this via views if you want.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
I think your question is about row-level security. Use stored procedures and
views to limit the rows that individual users can access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Access connectivity to SQL Tables (Urgent)

Please let me know whether Microsoft Access(2000), running on our office
computers, will be able to link (over the Internet) to the tables in our SQL
Server (2000) database on your server.If the clients running Access can access the SQL Server,
including the proper ports being open, authentication issues
resolved then yes you could link the tables. Whether you
would want to or not is a different story.
-Sue
On Fri, 7 Apr 2006 02:26:03 -0700, pcsmitpra
<pcsmitpra@.discussions.microsoft.com> wrote:

>Please let me know whether Microsoft Access(2000), running on our office
>computers, will be able to link (over the Internet) to the tables in our SQ
L
>Server (2000) database on your server.

Access another database from sp

I've looked around on how to do this and haven't had any luck so far. I have a stored procedure that needs to update tables in the current database (db1) and in another database (db2) on the same server. The user executing the stored procedure on db1 does not have permissions to access db2, but does know the username, password, and connection string of db2. Is there any way to create a connection to db2 in the stored procedure using the credentials I have? I've seen sp_addlinkedserver, but I don't think I need to use that because that database is on the same server? I just can't figure out how to 'switch' users to update the other database from the one stored procedure.

If I haven't made myself clear just let me know and I'll do my best to give a better explanation. Any help would be greatly appreciated.

Option 1:

Create a credential which has access on both db1 & db2 database. It will tune your query performance, bcs it is a direct access & you need not to stick with Ad-Hoc Remote quires.

Option 2:

Using Ad-Hoc remote quires using OPENROWSET or OPENDATASOURCE.

|||

As Mani indicates, the user will, in some way, require permission to access db2. However, it may not be necessary to provide the user direct access permissions. You may wish to explore the new 'EXECUTE AS' functionality (SQL 2005).

Refer to Books Online, Topic: "EXECUTE AS'

|||

Arnie,

I am not sure EXECUTE AS will help here.

As per my understanding, EXECUTE AS will affect the entire scope, if we need to change the context then we have to use the Execute AS & revert.

I created 2 users,

user1 only have access on db1

user2 only have access on db2

Without direct access permission on both db, user1 can't able to access the db2 rite? (even with execute as). If it allows then i can create any sp with Execute As target db user name, and i can access all the data.

Am i missing something here?

|||

Mani,

As in the Example , in Books Online, Topic: 'EXECUTE AS (Transact-SQL)', inside the stored procedure, the security context can be changed, the UPDATE statement can be executed in db2, and then the security context can revert -leaving the entirety of the context switching 'hidden' from the application users' view.

And, if necessary, the sproc can be encrypted locally to obfuscate the context switching from developers.

Access add record button grayed

Hi:
I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
Back-End. I have the problem that dependent tables in the E-R model can't add
records, the add new record button is grayed, however the independent table
and catalog table have no problems, all tables have PK defined. Any ideas?
Thanks in advance
Regards
Hugo
The really depends on how you have done the data sources for
your forms. It's not really a SQL Server issue but more of a
MS Access issue. You will probably want to post this in one
of the MS Access newsgroups instead.
-Sue
On Wed, 25 Jul 2007 14:46:00 -0700, ijazbof
<ijazbof@.discussions.microsoft.com> wrote:

>Hi:
> I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
>Back-End. I have the problem that dependent tables in the E-R model can't add
>records, the add new record button is grayed, however the independent table
>and catalog table have no problems, all tables have PK defined. Any ideas?
>Thanks in advance
>Regards
>Hugo

Access add record button grayed

Hi:
I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
Back-End. I have the problem that dependent tables in the E-R model can't ad
d
records, the add new record button is grayed, however the independent table
and catalog table have no problems, all tables have PK defined. Any ideas?
Thanks in advance
Regards
HugoThe really depends on how you have done the data sources for
your forms. It's not really a SQL Server issue but more of a
MS Access issue. You will probably want to post this in one
of the MS Access newsgroups instead.
-Sue
On Wed, 25 Jul 2007 14:46:00 -0700, ijazbof
<ijazbof@.discussions.microsoft.com> wrote:

>Hi:
> I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
>Back-End. I have the problem that dependent tables in the E-R model can't a
dd
>records, the add new record button is grayed, however the independent table
>and catalog table have no problems, all tables have PK defined. Any ideas?
>Thanks in advance
>Regards
>Hugo

Access a table

Another question about SQL Server tables and access grants.
I've created an user 'user1' as Access account, ad also as db_owner of a
selected database.
Then, I've created a table as user1.table1.
When I try to access the data of the table, with the account name of user1,
I must specify the owner ('select * from user1.table1') even if I've logged
as user1 (I need to access the data just with 'select * from table1').
What's the problem?

Thank you
Federica"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:ckgq2d$a46$1@.atlantis.cu.mi.it...
> Another question about SQL Server tables and access grants.
> I've created an user 'user1' as Access account, ad also as db_owner of a
> selected database.
> Then, I've created a table as user1.table1.
> When I try to access the data of the table, with the account name of
> user1,
> I must specify the owner ('select * from user1.table1') even if I've
> logged
> as user1 (I need to access the data just with 'select * from table1').
> What's the problem?
> Thank you
> Federica

It sounds as if it should work as you expect - for users not in the sysadmin
role, MSSQL always looks for an object owned by the current user first, then
dbo (see "Database Object Owner" in Books Online), but for sysadmins it will
always look for dbo first.

Do you get error 208 (invalid object name), or does it select from
dbo.table1 instead? If you get error 208, then you can use USER_NAME() to
check that you are user1, DB_NAME() to check you're in the correct database
(you might be in master by default, for example), and OBJECT_ID() to make
sure the object exists. If it selects from dbo.table1 instead, then user1's
login is probably also in the sysadmin role on the server.

If the problem is still unclear, what do you get when you run the following
as user1?

create table user1.t1 (col varchar(100))
insert into user1.t1 select 'Owned by user1'

create table dbo.t1 (col varchar(100))
insert into dbo.t1 select 'Owned by dbo'

select
'Database: '+ db_name() + ' ' +
'Login: ' + suser_sname() + ' ' +
'User: '+ user_name() + ' ' +
'Object owner: ' + col + ' ' +
'db_owner: ' + case is_member('db_owner') when 1 then 'Y' else 'N' end + '
' +
'sysadmin: ' + case is_srvrolemember('sysadmin') when 1 then 'Y' else 'N'
end
from t1

Simon|||"Simon Hayes" <sql@.hayes.ch
> It sounds as if it should work as you expect - for users not in the
sysadmin
> role, MSSQL always looks for an object owned by the current user first,
then
> dbo (see "Database Object Owner" in Books Online), but for sysadmins it
will
> always look for dbo first.

Thank you for the answer! The problem was caused by the user that was also a
sysadmin!
Fedesql

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

Access 2007 linked tables (vs Access 2003)

We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server.

Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server.

Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does?

Thanks!

I hope someone answers this. I would really like to see the answer...

Access 2003 Linked Tables update

Hello,

I am trying to update some SQL-linked tables in my Access database by repoiting the existing linked tables to a new datasource. The problem is, when I go to select the machine data source where the table sits, I get an error message saying the MS Jet Database can't find the object. This is because when Access creates the linked table, it replaces the period in the <schema>.<table_name> with an underscore. So when I go to update the links, it is essentially looking for the new table with the wrong file name.

I have about 80 linked tables to update and I haven't been able to figure a work-around. HELP PLEASE!

Cheers,

Josh

According to http://www.microsoft.com/technet/archive/office/office97/reskit/office97/027.mspx

Renaming Linked Tables

When Access links a remote table, it prefixes the default table owner ID of the SQL Server to each table name. The period separator between the owner ID and the table name is replaced by an underscore because periods in table names are not permitted by Access. Thus, the names of linked tables no longer correspond to the original table names in your MDB file. The simplest way to correct this is to rename your tables to their original names after linking.

Access 2000 upsizing trouble

I am upgrading an Access 2000 database to SQL Server 7.0 and 2000. When I
run the upsizing wizard it works great, but when I view the tables within
SQL only the first two tables appear to have data. I deleted the SQL
database and tried again. I tried upsizing one table (has 3500 records) and
it worked.
I have many tables of varying record size but the entire database is around
6MB. I don't find any information about why it works when I do one table at
a time and doesn't work when I try to upsize all the tables. Any help is
greatly appreciated.
WB"WB" <none> wrote:
>I am upgrading an Access 2000 database to SQL Server 7.0 and 2000. When I
>run the upsizing wizard it works great, but when I view the tables within
>SQL only the first two tables appear to have data. I deleted the SQL
>database and tried again. I tried upsizing one table (has 3500 records) and
>it worked.
Dates outside the range which SQL Servers default date formats can
cause problems. For more details and other items see the Random
Thoughts on SQL Server Upsizing from Microsoft Access Tips page at my
website.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm|||Hi WB,
This problem is wired. I would like you to provide more information so that
I can narrow down it?
1. Do you mean that there is not any error message?
2. Does this problem occur on SQL Server 7.0 or SQL Server 2000? What is
the Service Pack of the SQL Server?
3. I would like you to try to upsize all the tables of the Access 2000
database to SQL Server separately. Does the same problem occur again?
4. Please try to transform data from Access to SQL Server using DTS. For
additional information regarding DTS, please refer to the following article
on SQL Server Books Online:
Topic:" DTS Basics".
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michael,
During this upsize I did have some errors. The problem (errors) weren't
specifically with the tables that didn't upsize the data.
I have experienced this with both versions.
Yes, after I posted the message I went back and upsized each table, fixed
the errors and everything upsized correctly.
I will look into the DTS.
Thanks for your response and interest.
I am unsure as to why the data isn't converting on tables that don't have
errors?
WB
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:HN61VeSfDHA.1544@.cpmsftngxa06.phx.gbl...
> Hi WB,
> This problem is wired. I would like you to provide more information so
that
> I can narrow down it?
> 1. Do you mean that there is not any error message?
> 2. Does this problem occur on SQL Server 7.0 or SQL Server 2000? What is
> the Service Pack of the SQL Server?
> 3. I would like you to try to upsize all the tables of the Access 2000
> database to SQL Server separately. Does the same problem occur again?
> 4. Please try to transform data from Access to SQL Server using DTS. For
> additional information regarding DTS, please refer to the following
article
> on SQL Server Books Online:
> Topic:" DTS Basics".
> Thanks for using MSDN newsgroup.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi WB,
Thanks for your feedback. I glad to here that everything upsized correctly
now. Based on the information you provided, I am also unsure why the data
hasn't converted. To help me find out why, I would like you to provide more
information.
1. Do you mean the same problem occurs on both versions of SQL Server? If
so, what is the version of your Service Pack on both SQL Server (SQL Server
7.0 or SQL Server 2000)?
2. As I understand, the all structures of the tables have been upsized.
Only two of the tables have data and the other tables do not have any data.
If I have misunderstood, please feel free to let me know.
3. You wrote: "During this upsize I did have some errors. The problem
(errors) weren't specifically with the tables that didn't upsize the data."
Why do you think these errors are not related to this issue? Could you
please provide me with more details about the error message? If it is
possible, please provide me with a screenshot. You can send it to me
directly at v-yshao@.microsoft.com as an attachment.
4. I am not sure if this problem is machine specific. If it is possible,
could you upsize the MDB database from another machine? Can this problem be
reproduced on another machine? If this problem cannot be reproduced on my
side, it will be hard to find the cause.
Also, I found some useful articles regarding the issues with Upsizing
Wizard, could you check to see if your database has the same problem?
225993 ACC2000: Data Not Upsized in Table with Index
http://support.microsoft.com/?id=225993
325019 ACC2000: Issues with the Access 2000 Upsizing Wizard
http://support.microsoft.com/?id=325019
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Access 2000 Frontend MS SQL 2000 backend - Locking Problems

We are using an Access 2000 project to view our SQL Tables and using Access 2000 Runtime to Access the forms in the project. We have written in a locking system in VB and removed the video controls to prevent users from accessing the same records. But of course now we need to make the video controls available. This has now thrown up the problem of multiple users accessing the same records. We have tried to write code to lock records when then video controls are used but this is not working as well as we hoped. Can anyone please suggest any way of setting up locking on SQL using triggers from the Access frontend? or any other types of locking systems that could be written in the Access front end.

The safest and easiest way to use locks having to do with SQL Server in a distributed application is to use SQL Server locks themselves. See the links below for information on how SQL Server locks work and how to use them.

http://msdn2.microsoft.com/en-us/library/ms189857.aspx

http://www.informit.com/articles/article.aspx?p=26657&rl=1

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

http://www.databasejournal.com/features/mssql/article.php/3289661

http://www.sql-server-performance.com/articles/per/advanced_sql_locking_p1.aspx

http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/

Hope that helps,

John

Access 2000 Frontend MS SQL 2000 backend - Locking Problems

We are using an Access 2000 project to view our SQL Tables and using Access 2000 Runtime to Access the forms in the project. We have written in a locking system in VB and removed the video controls to prevent users from accessing the same records. But of course now we need to make the video controls available. This has now thrown up the problem of multiple users accessing the same records. We have tried to write code to lock records when then video controls are used but this is not working as well as we hoped. Can anyone please suggest any way of setting up locking on SQL using triggers from the Access frontend? or any other types of locking systems that could be written in the Access front end.

The safest and easiest way to use locks having to do with SQL Server in a distributed application is to use SQL Server locks themselves. See the links below for information on how SQL Server locks work and how to use them.

http://msdn2.microsoft.com/en-us/library/ms189857.aspx

http://www.informit.com/articles/article.aspx?p=26657&rl=1

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

http://www.databasejournal.com/features/mssql/article.php/3289661

http://www.sql-server-performance.com/articles/per/advanced_sql_locking_p1.aspx

http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/

Hope that helps,

John