Linked ODBC table (MSSQL7) with MS Acess97
Linked ODBC table (MSSQL7) with MS Acess97
(OP)
Hello, I am using Microsoft Access97 and using a linked table from MS SQL7.0. I can link the table with no problem, but when I try to add data or change data, 1 of two things seem to be happening. 1ST I will get a message saying that someone else has changed the current record... which is impossible, since I am the only one who has access to this db... after while it will let me add records, however, when I look at the linked table, every other row is filled across with the word #DELETED
When I look at the table using SQL Enterprise Manager, everything looks fine. Does anyone know what is going on here? I am wondering if perhaps it is just not commiting the changes.. is there anyway to force MS Access to commit the changes immediatly?
When I look at the table using SQL Enterprise Manager, everything looks fine. Does anyone know what is going on here? I am wondering if perhaps it is just not commiting the changes.. is there anyway to force MS Access to commit the changes immediatly?
Talk To Other Members
RE: Linked ODBC table (MSSQL7) with MS Acess97
To commit in a record to SQL Server via Access, you could do this via two methods:
1) Use the workspace object and use the begintrans/ committrans methods around each record insertion.
2) Write an SQL string "Commit tran .." and use the dbSQLPassThrough option of the db.execute method.
Hope this works, but I would check your SQL via the query analyser in SQL Server 7.
** I presume you are using DAO (would work for other models aswell)
Cal