Fog Creek Software
Discussion Board




SQL Server -> Access 2000 Linking

OK, two quick questions that have been driving me nuts:

1) I've got a SQL Server 2000 System and have linked the
tables to an Access 2000 front end for data input. When I link in any tables with the bigint data type, it shows up as Binary in Access 2k. Moreover, the bloody thing won't let me append to the table because of a type conversion failure. I believe this worked before in Access 97, but failed when I upgraded today. Anyone familiar with the Binary data type or this issue?

2) Said database, above, returns #Deleted entries for all records entered before a database change. The catch is, I completely dropped my SQL Server Database, rebuilt it from a Transact-SQL script, and used a new access database. Now whenever I enter a record with the same primary key as one that was in the previous version of the dbase, #Deleted entries show up in access. Note that Enterprise Manager, Query Analyzer, and ADO Recordsets all show the data as well and good.

Anybody ever encounter these issues?

Dustin Alexander
Thursday, September 18, 2003

You need to have timestamp fields exposed to ms-access. Ms-access will use the time stamp to determine if a record needs to be updated. (otherwise, it is forced to check each field).

As for the update problem? Access may be creating Null bit fields, which causes a type mismatch.

Check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;280730

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. kallal
Thursday, September 18, 2003

*  Recent Topics

*  Fog Creek Home