1

I have a database that I have recently upgraded via SSMA to use SQL Server 2005. If I open a form that is bound to a table, it will display the data from the table; however, I am unable to edit the form and save the information. Simply typing in a textbox does nothing.

I have noticed that the Access DB now has extra "tables" that begin with "SSMA$[OriginalTableName[", which appear to be the links to the SQL server, and I am able to view and edit those. I am unable to edit the original tables, only view them. I've attempted to modify those "bound" tables to the new "SSMA$", however, multiple errors ensue.

Has anyone experienced this before? Any ideas or insight would be most helpful.

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
gregcruce
  • 21
  • 4
  • What are the connect strings on those SSMA$ tables? That should tell you if those are the links you need to use. To find out, go to the Immediate Window in your front end (Ctrl-G) and type: ?CurrentDB.TableDefs("SSMA$[OriginalTableName]").Connect and hit enter. Or, just browse the MSysObjects table, which includes a column for the Connect string for linked tables. – David-W-Fenton Jan 08 '10 at 04:26
  • They appear to be blank; here's the code: "?CurrentDB.TableDefs("[SSMA$Contacts$local]").Connect" gives me a new, blank line with nothing in it. Ironically, running the same code on the standard "Contacts" table yields this: "ODBC;DSN=XYZ-Proof;APP=2007 Microsoft Office system;DATABASE=XYZ_Proof_DB;Trusted_Connection=Yes" I'm now guessing the blank connection is my problem, but I'm not sure how to change/update that? I appreciate your help. – gregcruce Jan 08 '10 at 15:51
  • That looks to me as though SSMA renamed the original local tables and created ODBC links with using those original names. – David-W-Fenton Jan 09 '10 at 00:49
  • Yeah, I've been able to deduce that much; I'm curious as to why I can't edit them even in Table view. Interesting tidbit; if I re-create the tables via ODBC, I can view and edit them. I just wonder if the connection string SSMA uses is readonly? – gregcruce Jan 11 '10 at 15:48

1 Answers1

0

Actually, I believe i just figured it out. If a table doesn't have a clearly defined primary key, the SSMA wizard will created the linked tables. Access, without a primary key on a linked table, apparently doesn't know how to update it and opts to open said linked table in "read-only" mode. I was able to reproduce the problem by creating a linked table manually, and then a popup asked me to identify the primary key. Clicking "Cancel" puts the linked table in read-only mode, but creating a primary key and using that column allows me to work with the table.

gregcruce
  • 21
  • 4
  • In addition to always having a PK defined (without it on every table you don't really have a database, just a bucket of data), I always include a timestamp field in all my SQL Server tables because this helps Access manage refreshes in bound forms. – David-W-Fenton Jan 12 '10 at 23:18