I have been stumped on this problem for sometime.
NOTE- the sample code is simplified(do not take literally) and provided to give a feel for what is happening-The code works it is just the result is not what was expected
I have an Access97 database that links and can relink to SQL Server7 tables through code : strConnect= "ODBC;Driver={SQL Server}; Server=name_of_server; APP=name_prgm; Database=name_of_db;UID=ServerID;PWD=ServerPassword
This works fine, now the problem.
I created a new table in the Access database, then manually/physically used Access save/export to send the table to the SQL server database using an ODBC DSN I setup from the Windows control panel- I used the same server ID and password as my code. I then deleted the table from my Access tables and physically linked to the table now on the SQL server using Access "link table". Every thing worked fine and I was able to edit the table using this manually setup ODBC.
When I relinked my tables using code the New table was relinked properly BUT I could not edit it! When I went into the table via forms or datasheet view- it said data not updateable (almost like it was a read-only table). All the old tables work just fine. Yet if I physically link the table via the same UID and Password it works everytime (thus it could not be a permission problem).
The relinking works as follows: first it deletes the linked tables in the Access database, then for each table in the server database it creates a new table and connects it. The code uses the following steps:
1. Set tblAccess=ServerDatabase.CreateTableDef(ServerTableName)
2. tblAccess.SourceTablename=ServerTableName.name
3. tbleAccess.Connect=";Database= databasename; ODBC;Driver={SQL Server}; Server=name_of_server; APP=name_prgm; Database=name_of_db;UID=ServerID;PWD=ServerPassword
4.AccessDatabase.TableDefs.Append tblAccess
Remember the table does link OK and the correct data can be seen, but it is not updateable. I don't think this is the problem, but the new table was imported into the Access database base tables from a CD-BUT after importing it and prior to exporting it to the server it was editable in the Access database and populated. And remember the SAME table if accessed physically through ODBC is editable.
If anyone can shed light I would greatly appreciate it.
