SQL Server Backend is Read Only

I have upsized my access program backed to SQL Server Express 2008 using the
SQL Server Migration Assistant. Everything seems to be working fine in query
mode only. That is, my Access program can access the data in the database.
However, if I try to do an Update or Add Record. I get an error message that
the Backend SQL Server Database is Read-Only.

I looked at the Backend database from SQL Server Management Studio and
ReadOnly is set to False.

It looks like the problem may be the way I linked the Backend or the way
Access opens the SQL connect.

Any ideas what I need to do to make Access 2007 access the SQL Server
Express backend database not in a ReadOnly mode?

Thanks in advance for your help. I am totally stuck right now.
Mike P

How are you connecting to the backend? With ODBC?
--
Milton Purdy
ACCESS
State of Arkansas


"Mike P" wrote:

I have upsized my access program backed to SQL Server Express 2008 using the
SQL Server Migration Assistant. Everything seems to be working fine in query
mode only. That is, my Access program can access the data in the database.
However, if I try to do an Update or Add Record. I get an error message that
the Backend SQL Server Database is Read-Only.

I looked at the Backend database from SQL Server Management Studio and
ReadOnly is set to False.

It looks like the problem may be the way I linked the Backend or the way
Access opens the SQL connect.

Any ideas what I need to do to make Access 2007 access the SQL Server
Express backend database not in a ReadOnly mode?

Thanks in advance for your help. I am totally stuck right now.
Mike P

Does every table have a primary key defined and does Access recognize the PK?

--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at http://social.answers.microsoft.com/...ddbuz/threads?


"Mike P" wrote:

I have upsized my access program backed to SQL Server Express 2008 using the
SQL Server Migration Assistant. Everything seems to be working fine in query
mode only. That is, my Access program can access the data in the database.
However, if I try to do an Update or Add Record. I get an error message that
the Backend SQL Server Database is Read-Only.

I looked at the Backend database from SQL Server Management Studio and
ReadOnly is set to False.

It looks like the problem may be the way I linked the Backend or the way
Access opens the SQL connect.

Any ideas what I need to do to make Access 2007 access the SQL Server
Express backend database not in a ReadOnly mode?

Thanks in advance for your help. I am totally stuck right now.
Mike P

Yes, I Linked to the SQL Server tables by using the ODBC data source.

"golfinray" wrote:

How are you connecting to the backend? With ODBC?
--
Milton Purdy
ACCESS
State of Arkansas


"Mike P" wrote:

I have upsized my access program backed to SQL Server Express 2008 using the
SQL Server Migration Assistant. Everything seems to be working fine in query
mode only. That is, my Access program can access the data in the database.
However, if I try to do an Update or Add Record. I get an error message that
the Backend SQL Server Database is Read-Only.

I looked at the Backend database from SQL Server Management Studio and
ReadOnly is set to False.

It looks like the problem may be the way I linked the Backend or the way
Access opens the SQL connect.

Any ideas what I need to do to make Access 2007 access the SQL Server
Express backend database not in a ReadOnly mode?

Thanks in advance for your help. I am totally stuck right now.
Mike P

No, ever table does not have a Primary Key. Access does recognize the tables
that do have PK's though.

"Duane Hookom" wrote:

Does every table have a primary key defined and does Access recognize the PK?

--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at http://social.answers.microsoft.com/...ddbuz/threads?


"Mike P" wrote:

I have upsized my access program backed to SQL Server Express 2008 using the
SQL Server Migration Assistant. Everything seems to be working fine in query
mode only. That is, my Access program can access the data in the database.
However, if I try to do an Update or Add Record. I get an error message that
the Backend SQL Server Database is Read-Only.

I looked at the Backend database from SQL Server Management Studio and
ReadOnly is set to False.

It looks like the problem may be the way I linked the Backend or the way
Access opens the SQL connect.

Any ideas what I need to do to make Access 2007 access the SQL Server
Express backend database not in a ReadOnly mode?

Thanks in advance for your help. I am totally stuck right now.
Mike P

You need to put PKs on every table. You will not be able to delete from any
table that does not have a PK defined.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Mike P" wrote in message
...
No, ever table does not have a Primary Key. Access does recognize the
tables
that do have PK's though.

"Duane Hookom" wrote:

Does every table have a primary key defined and does Access recognize
the PK?

--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at
http://social.answers.microsoft.com/...ddbuz/threads?


"Mike P" wrote:

I have upsized my access program backed to SQL Server Express 2008
using the
SQL Server Migration Assistant. Everything seems to be working fine in
query
mode only. That is, my Access program can access the data in the
database.
However, if I try to do an Update or Add Record. I get an error
message that
the Backend SQL Server Database is Read-Only.

I looked at the Backend database from SQL Server Management Studio and
ReadOnly is set to False.

It looks like the problem may be the way I linked the Backend or the
way
Access opens the SQL connect.

Any ideas what I need to do to make Access 2007 access the SQL Server
Express backend database not in a ReadOnly mode?

Thanks in advance for your help. I am totally stuck right now.
Mike P

You MAY need to put PKs on every table. You will not be able to
UPDATE OR INSERT from any
table that does not have a PK defined.





On May 25, 7:50*am, "Douglas J. Steele"
wrote:
You need to put PKs on every table. *You will not be able to delete from any
table that does not have a PK defined.

--
Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Mike P" wrote in message

...

No, ever table does not have a Primary Key. *Access does recognize the
tables
that do have PK's though.

"Duane Hookom" wrote:

Does every table have a primary key defined and does Access recognize
the PK?

--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at
http://social.answers.microsoft.com/...ddbuz/threads?

"Mike P" wrote:

I have upsized my access program backed to SQL Server Express 2008
using the
SQL Server Migration Assistant. *Everything seems to be working fine in
query
mode only. *That is, my Access program can access the data in the
database.
However, if I try to do an Update or Add Record. *I get an error
message that
the Backend SQL Server Database is Read-Only.

I looked at the Backend database from SQL Server Management Studio and
ReadOnly is set to False.

It looks like the problem may be the way I linked the Backend or the
way
Access opens the SQL connect.

Any ideas what I need to do to make Access 2007 access the SQL Server
Express backend database not in a ReadOnly mode?

Thanks in advance for your help. *I am totally stuck right now.
Mike P

"a a r o n . k e m p f @ g m a i l . c o m" wrote

You MAY need to put PKs on every table.
You will not be able to UPDATE OR
INSERT from any table that does not
have a PK defined.

Who are you and what have you done with Mr. Kempf? I know you're not him
because you aren't giving his standard advice!