Import data from Access 97

• WILLIAM MITCHELL

  SSChampion

  Points: 13766

  More actions

  December 7, 2007 at 5:58 pm

  #758251

  This also works on the "good" server for both Access 97 and 2000, but only for Access 2000 on the "bad" one:

  SELECT * FROM

  OPENDATASOURCE(

  'Microsoft.Jet.OLEDB.4.0',

  'Data Source="C:\BILL\Test97.mdb";User ID=Admin;Password=;')...Client

  To get this to work, I had to go to:

  Start ~ Programs ~ Microsoft SQL Server 2005 ~ Configuration Tools ~ SQL Server Surface Area Configuration

  and then click Surface Area Configuration for Features, then under Database Engine select Ad-Hoc Remote Queries and then check the box Enable OPENROWSET and OPENDATASOURCE support.

  Still waiting for a reply from the MS Newsgroups about the server config.

• Karl Heinz Brehme

  Valued Member

  Points: 61

  More actions

  February 7, 2008 at 8:10 pm

  #776264

  Hi, I have some information to write about it (not solutions unfortunelly), because I have this problem for years...

  This problems don´t happen just with SQL Server 2005. It happens with SQL Server 2000 too.. and not always in both. Sometimes the servers execute a file in Access 97 format, sometimes not, returning this error about corrupted file.

  Two "solutions" I find with this: Convert the file to Access 2000 format or RESTART SQL Server 2000 (2005 I didn´t tried this solution because I deprecated it a log time ago...)

  And now I´m trapped because upgraded my Office 2000 to Office 2007 and get the surprise with Access 2007...: A new kind of file, format and access... And I didn´t succeed to connect to it with linked server so........ I didn´t find any solution in any place. I must get this file today to import to a production system tomorrow, hehe. Traps...

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  February 8, 2008 at 11:11 am

  #776532

  Karl -

  Like previously mentioned - I haven't had the "pleasure" of dealing with Access 2007. that being said - in 2003, there was a way to DOWNGRADE (convert a database back to a previous version).

  If you're trying to get data in in a hurry - then making a copy and pushing it back to the older version might be the ticket (so that SQL can link into it). Otherwise - you may have to resort to file-based exports which then get imported into SQL.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Karl Heinz Brehme

  Valued Member

  Points: 61

  More actions

  February 8, 2008 at 11:17 am

  #776536

  Thanks Matt... well I found a PC here with office 2000 installed, so I converted 97 to 2000 and OK, work done.... the main problem is to find why access 97 some times shows the problem about corrupted file or invalid format, when in a linked server from sql 2000 or 2005, Windows Server 2000, Windows XP Pro and Vista...