July 30, 2003 at 10:52 am
I'm trying to copy an Access table to a new SQl 2000 database using the import data function within SQl Server. The Access driver I'm using is version 4.00.6119. The Access table is in a database on our network and the application uses the MDW file on the C drive. In other words, it is not secured.
Everything on the GUI seems ok until I select the access table to be copied to SQl Server. I press the NEXT button after the table is selected and an error message appears that says I don't have read permission on the Access table. However, if I launch Access 2000 using the same network logon I use for SQL Server I can access the table without any gotcha's. Any help would be most appreciated.
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
July 30, 2003 at 10:56 am
Did you set userid/password for the Access database? "Admin" and no password is the default - try adding that....
quote:
I'm trying to copy an Access table to a new SQl 2000 database using the import data function within SQl Server. The Access driver I'm using is version 4.00.6119. The Access table is in a database on our network and the application uses the MDW file on the C drive. In other words, it is not secured.Everything on the GUI seems ok until I select the access table to be copied to SQl Server. I press the NEXT button after the table is selected and an error message appears that says I don't have read permission on the Access table. However, if I launch Access 2000 using the same network logon I use for SQL Server I can access the table without any gotcha's. Any help would be most appreciated.
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
July 30, 2003 at 3:56 pm
I double checked that. The error message remains the same. Perhaps I am using the wrong driver?
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
July 31, 2003 at 6:51 am
I am not usre what you mean by using the "import data function within SQl Server". For the scenario you described I would use DTS Import Export Wizard or a Package. Choose Microsoft Access Driver and give the right path to the database. If the .mdb (access) is not protected by password you can acess it without filling out the username, password fields in the Import window. If there is security setup for the Access database and you are scheduling the DTS package to run as a job and executing with user other than you then it may fail. Try to run it interactively or change the user context for the job. This is very simple.
Have a good one
-Sravan
August 4, 2003 at 2:49 pm
Solution:
Even though the Access database was secured using the default MDW file, I had to grant read access rights to the Access tables using the Admin id.
This solved the problem. LOL
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply