Can't add or edit ODBC linked table in 2007 2

[MICKI0220]

I am trying to move one of my tables to An SQL database. I have created groups/profile/schemas and users with the permissions I think are needed to access this table. The link works fine and I can open the table, however I cannot edit/delete/insert or update the data. Can anyone tell me what I am missing. Is it on the SQL end or the Access end?

 

[kjv1611]

If you're reading it into SQL, and trying to edit from the SQL end, then the easiest thing to do would be to just copy the linked table to a new editable table; and thus problem averted.

 

[MICKI0220]

Access is my front end and sql is the backend. I need to add more fields to a table that already has the max fields, so I created a SQL database to use so I could increase the amount of fields...in the future all the tables from this Access database will be in this SQL database. However, I can see all the info with the linked table from the Access database, I can't edit the table at all.

 

[kjv1611]

Okay, so a SQL table linked into an Access database. I think that the issue will be on the SQL Server side. I could be wrong, though, b/c I've not had to do things this way. Frankly, I am (or was) unaware that you could edit the data in a SQL Server from Access. I understand having front ends, but I'm wondering if you have to instead have some code that runs a query on the SQL server side by loading that in VBA, using another connection.

You might want to try asking over at this forum:
forum962

 

[MICKI0220]

I am currently doing that in SQL 2000. I can't see why they would eliminate that from the 2008 version.

 

[Hap007]

HI,

What you are trying to do works fine. I have frontends linked a SQL backend and all is good.

What type of SQL backend are you connected to? MS SQL or MySQL or something else?

I have discovered that if the SQL table does not have a Primary Key defined, then Access may not allow you to change the table data.

So, does your SQL Table have a primary key?

Finally, when you change the SQL backend, you must refresh the link for any changes to be seen by the access frontend.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[MICKI0220]

That was the problem, when importing the data, it dropped the primary key and once I created it back, it works fine...Thanks for all your help.

 

[kjv1611]

Thanks, Hap007! I definitely had no clue about that one. Good to know.

 

[MICKI0220]

Sorry, next question. I am able to add another record and edit, but only if I create an SQL query on the sql database table. In the old version I could do it directly. Is there a way to change this. If a form's record source is a dbo table it gives an error saying that "The record has been changed by someone else since you started editing it etc.

 

[PHV]

No bitfield allowing null ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

I will try that, my bit fields do allow nulls. I will change it and get back with you all about it.

 

[PHV]

If you need bitfield(s) allowing null then simply add a timestamp field.
BTW, don't forget to refresh the linked table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

It doesn't accept a bit field without the "allow Nulls" box checked.

 

[PHV]

Did you add a timestamp field and then refresh the linked table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

I added a timestamp field (allow nulls is unchecked). I get a new error when I tried to update a form.

ODBC-update on a linked table 'JobMaster' failed.
[Microsoft][SQL Server Native Client 10.0][SQL Server]The incoming tabular data stream(TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): Data type 0xE7 has an invalid data length or metadata length. (#8016)

 

[Hap007]

Hi,

I have seen that error message when using an ODBC link to a MySQL database.

If your backend is in MySQL, then when you configure the ODBC link, make sure to select 'Advanced' Options and then check Flags 1 > Return Matching Rows as 'True'

This will eliminate this error when using Access linked to a MySQL Backend.

If your backend is MS SQL, you might want to check to be sure that you have no other queries opening the same table.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[MICKI0220]

It is an SQL 2008 backend and it is in a test access frontend (2007). Right now no one else is in it testing but me.

 

[Hap007]

You might want to try and delete the link and then re-link to the ODBC backend table.

I have seen this type of error when the table's key is not Unique. So, make sure that the key is unique.

Hope THis Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[MICKI0220]

Tried that and still comes up with that error when trying to edit a record.

 

[Hap007]

Well, without hands on, I can not think of anything else.

I can offer 4 more things I would try, but no guarentees

1) Try using a PassThru Query to change a field in one of the records.
If the PassThru query works, then I would think that there might be some data field defined in the backend that Access is not handling correctly.

2) I would create a backup copy of the table and either begin changing field definitions or just deleting the fields, one at a time until I was able to edit the record. This is slow and possible tedious, but you should be able to track down your problem

3) Make sure that there are no stored procedures or rules in the SQL backend that fire when you make a record change.

4) You can also search google or the MicroSoft web site for the error meesage. That might alos provide some additional suggestions.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121