MSSQL and Access

[nux2408]

Hello to all
I have link a table from an Mssql to Access 2000 I can see the data in the Access end but I can add a record to it can some one tell me why or how I can change this event, I would like to add a record in Access into the SQL database.

Thank you all

 

[BNPMike]

I'd check two things - 1) Have you got permission to add records, 2) Is a primary key clearly defined?

 

[willir]

nux2408

Did you develope the MS*SQL database? In my experience, often, an ODBC connection will be setup by the database administrator to allow read only access. This is to ensure data integrity -- by only allowing data entry through a properly designed front end, the developer minimizes the chance of data being entered incorrectly by the end user.

Hi, Mike, thanks for your post earlier today.

Richard

 

[nux2408]

Hello to all

1 I can insert a new record from other ODBC applications into the SQL database with no problem the issue that I have is with Access I can't insert a new record from Access link

2 I Also I can insert a new record from the SQL Ent Manager

3 so I assume I have the proper permission since I can do the task from two different points but not from Access.

If any body can help I will very much appreciated.

Thank you

 

[PHV]

When you look at the linked table in Design view is a primary key or at least an unique index defined ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

When you say Access LINK do you really mean linked tables that appear in the Access front end

OR

are you using Pass Through queries to view your MySQL data ?


I ask because I never have any problems adding records to LINKED TABLES

BUT

PASS THROUGH queries are READ ONLY.



If this doesn't solve your problem - then try one more thing for us.

Can you EDIT Existing records from within your Access Front End?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

PASS THROUGH queries are READ ONLY
You can make ACTION (Insert, Update, Delete, ...) pass through queries.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nux2408]

Hello to all
Here is what I have come up with
When I select to link the tables from Access I have some tables that they are named public "dbo_Public_Item" I choose that one I can insert a record not a problem, but if I choose the one that is "dbo_item" I can't, those two files are the same
Also there is no table named "dbo_Public_Item" in the SQL database some how is created in the ODBC connection
Can any one let me know what is happeing

Thank you all

 

[PHV]

Seems that the DBA correctly did the job of protecting the data against external users by exposing public views or some similar technique and revoking permissions on tables.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

Okay PHV fair cop. - PassThrough SELECT queries are READ ONLY.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[nux2408]

I Just wish to understand why several app can open "dbo_item" and upadate the contents but Access uses the public type of file any reason why.

thank you all for trying to help

 

[MoLaker]

It may be in the userID and password you are using in your connection (DSN or DSN-less). Is it by chance different than the other apps are using that can update "dbo_Item"?

 

[nux2408]

No I have been login is as "sa" and the same password
I can't figure out the cause for this.

Thank you

 

[MoLaker]

You haven't actually said how you are connecting to SQL Server. Most likely it is that connection method that is forcing a read-only condition.

Are your other apps using the same DSN you are using with Access? If not, perhaps you need to delete and re-create that DSN.

If you are using another method (DSN-less or ADO) please post your connection string so we can evaluate what you are doing.

 

[nux2408]

I have try all possible connection but nothing.

if any of the connection were read only then I would not been able to edit the data in the "dbo_Item" but I can.

The tables the the SQL creates are in VIEWS table that is the ones that I can update, so the SQL database must have some dependencies against those table, which I am not that advanced to understand.

The puzzle is why only Access sees those public tables and others update directly to the reg tables?

Thank you