how to use EXCEL to import data from access

[zhongwei]

In Excel I try to import data from Access file but every time the message saying" ....don't have permission to read access file " actually I have full permission to access file,I think this is related to *.mdw file but I can't figure out where is the problem,Pls give some suggestion,thanks !

 

[lameid]

In Access, export to a comma deleimted text file with a csv extension. Then you should be able to open it in Excel.

 

[zhongwei]

Thanks, but the reason I want to import data in Excel is according to someone's suggestion:"when database corrupted, using excel to import data is a way to save data of table....." so exporting from access doesn't work in this case,(the database file already corrupted, can not be opened in Ms Access),other ideas? let's share & discuss!
Thanks!

 

[Newposter]

When you have a protected Access database (with a custom .mdw file), even if you are the Admin, you cannot Import the tables into Excel. Rather, you must Export them from Access to Excel. Since your Access file is corrupt, the suggestion to Import from Excel will not work. You must find a way to repair or replicate the Access file into a good format.
This is why it's critical to back up important files frequently.

 

[lhuegele]

I don't know if this will help anyone, but we had a corrupted database that Access could not repair. What we finally did to fix the database was to use the ODBC Datasource Administrator in Windows. It should be located in the control panel under Windows 98 and under Administrative Tools in Windows 2000.

We were able to use this tool to repair the database when Access could not. Once you have this tool open, simply select the Access ODBC and configure it. Once there, select the database and press the repair button.

We did lose 4 or 5 really bad corrupted records, but it was not a problem for us.

Hope this is helpful to someone!
;-)

 

[zhongwei]

Hi,Ihuegele, thank you for you suggestion,but while I did it,the messagebox pop up say:"have no permission to '|' object...." I don't know what '|' is, do you have any idea about it? thanks

 

[lhuegele]

I'm sorry zhongwei, I have no idea what that could be. How large is the corrupted database? Is there enough free disk space where it's located to perform the repair? Is it located on a network drive? If so, perhaps you can copy it to your local drive and try again. You may not have the proper read/write access to the drive where it's located. I wish I could be more helpful. Let me know how it goes.

Lhuegele

 

[zhongwei]

Hi,lhuegele

followed your suggestion, I copyed the file to my local drive,then open Administration tools(WIN2000),under userDSN tab, choose MS Access Database,click "configure" button,select the database file,click "advanced" button, type in Id & Password,then click repair,the same thing happened,saying"no permission to use '|' object,.....",
What about your database? is it in Win2000,is it secured?
Any idea?Let me know.Thanks.

 

[lhuegele]

Howdy zhongwei,

I noticed under my ODBC data sources administrator, there are two MS Access listings under userDSN tab. One just says "MS Access Database" and the other says "MS Access 97 Database". I don't know the difference but I used the Access97 ODBC.

Our databases are in Access 97 (about to upgrade to 2k). I am running Win2000. We have user levels set-up in the workgroup and I always log in as the administrator, which gives me full rights.

I do not have a corrupt database to try to duplicate your error so I wish I could be more help :-( I wish you could send me your database to see if I can repair it for you...

Good luck,
Larry

 

[zhongwei]

Hi,lhuegele
I made a new database without security,then use DSN to repair it, it successed.I set up user-level security to this new database,try again , fail,message box pop up"...no permission for'|' object,...",I'll check workgroup info ,modify the permission ,and try again.
Thanks.

 

[lhuegele]

zhongwei,
ARe you the creator of the corrupted database you are having trouble with? If not, perhaps you can log-in as the creator and try again.

Good luck!
Larry