Import data from Access 97

Question

Post reply

Import data from Access 97

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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...