How do I make an updatetable from link table access to sql server.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to use Access 2003 as front end and SQL server as back end. I have
linked tables to SQL but I can`t modify the tables.
 
cesbernal said:
I want to use Access 2003 as front end and SQL server as back end. I
have linked tables to SQL but I can`t modify the tables.
Click to expand...

Do the tables on the server have primary keys defined? If they don't you
will get a read-only link unless you tell Access which fields to use for a
local unique index. You should hav ebeen prompted for this when creating
the link, but if you later refresh the link it will lose that and revert to
read-only.
 
I am using Access 2003 with Access 2000 format with SQL Server 2000 (no SP
installed)
It is mdb, ODBC, ADO.

When I use a Query, I can visualize all records but I cannot modify it, even
though,in ACCESS database I could do it directly. For example When I execute
this command in a form (button)
currentproject.connection.execute("update field set field=55 from table")

it tells me "This must be an updatetable"

Please help me with this.

Thanks,

cesbernal
 
Yes It is defined the primary keys but First I am testing with another table
that does have no relationship and no primary key, it should not have problem
with that
The point is How I tell Access that all fields are updatable When I link
tables to SQL. I have never been prompted about that.

Please help me

Thanks,

cesbernal
 
cesbernal said:
I want to use Access 2003 as front end and SQL server as back end. I have
linked tables to SQL but I can`t modify the tables.
Click to expand...

cesbernal,

What version and service pack of SQL Server?

Is this .mdb or .adp?

If .mdb, is this ODBC or OLEDB? DAO, ADO, RDO?

What is the error message that is returned when you run your UPDATE
query?

What does your update query look like?

What is the DDL of the SQL Server tables?


Sincerely,

Chris O.
 
cesbernal said:
I am using Access 2003 with Access 2000 format with SQL Server 2000 (no SP
installed)
It is mdb, ODBC, ADO.

When I use a Query, I can visualize all records but I cannot modify it, even
though,in ACCESS database I could do it directly. For example When I execute
this command in a form (button)
currentproject.connection.execute("update field set field=55 from table")

it tells me "This must be an updatetable"
Click to expand...

cesbernal,

I notice that the table name in that SQL statement is the same as
the column name. Is that correct?

I have tried various UPDATE commands via the ADO connection object
to a test SQL Server (Express 2005) database.

They all seem to go ok (that is, they don't return error messages).

I am interested in the partial error message, "This must be an
updatetable". This appears to be the beginning of an MS Access
error message.

Can you post the .OPEN connection string that you are currently
using?


Sincerely,

Chris O.
 
I linked SQL 2005 Express table to Access 2003. It was come our as read only.
After defining a primary key it worked and I can do update/insert..

BR
Shajib
 
Back
Top