Linked ODBC table (MSSQL7) with MS Acess97

Guest_imported · Jun 10, 1999

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?

calahans · Jun 14, 1999

Check your SQL (particularly values for variables). 
 
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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Linked ODBC table (MSSQL7) with MS Acess97

Guest_imported

New member

calahans

Programmer

Similar threads

Part and Inventory Search

Sponsor