Connecting to SQL Server 2000 w/ Access 97 Front End

[Tweir]

Hi all,

I am trying to hook up my Access97 Front End forms to a new SQL Server 2000 back end.
I do this by:
1. Setting up a System DSN using ODBC drivers to hook up to the SQL Server Db.
2. Creating a new Linked table in Access 97 using the System DSN.

This all works fine, I can see all the tables in the SQL Server db, but -

when I go to open the table, all of the data is replaced by "#DELETED". The table returns the right columns and number of rows, but the data itself is overwritten. This is highly problematic.

Microsoft acknowledges that this is a problem with the new versions of sqlsrv32.dll. See

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q248602

They do not, however, offer any solution....

Our shop is moving to Windows 2000 (50% done) and will be using Access 97 for a while until our ASP front end is complete. Anybody got any ideas? The newest MDAC 2.7 is no help. I'm trying to get it to work on some old 98 boxes with old drivers, but that is posing other problems and we're not going to downgrade to W98...

Help!

Thanks,

Terry Weir

 

[Tweir]

Here's the deal:

I read this link:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q128809.

Then I went back and looked at my table design. I had been using as a Primary Key a bigint field designated as the Identity Column. This apparently is not unique enough. I tried creating a Unique Index on this Identity Column as well - that didn't work.

So I tried the brute force way of adding in a RowID column as type Uniqueindentifier, with a default value of NewID() and that worked!

I'm not really sure why Access didn't like the way I had it earlier, but now I know how to make it work for certain. This is all done with the latest MDAC 2.7.

 

[tlbroadbent]

You can create a primary key or add a timestamp column to the SQL table. Either method should also work. The timestamp is particulary effective when working with Access and SQL Server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.