Importing data from Access

Question

Importing data from Access

Neville Turbit

Grasshopper

Points: 14
More actions
October 22, 2007 at 11:10 pm

#376001

I am just learning about SQL Server so please excuse what may be a silly
question. I am importing the data from an Access 2000 back end database into
SQL Server 2000 using the DTS Wizard.
The SQL Server database I created through the Access Upsizing Wizard is fine
in that I can see all the tables and columns as they should be. I can load
data manually into the tables.
The Access database I want to import from has no password, or is not
associated with any special workgroup file.
When I try to import data, I am using Windows Authentication and leave the
Username and Password of the Access database blank. If I try and preview the
data, even on just one table, I get an error:
"Record(s) cannot be read; no read permission on 'Whatever the table name
happens to be I am testing at the time'.
Context: Error calling RowSet on the provider."
I tried a compact and repair on the Access database but no difference. Any
suggestions

Viewing 9 posts - 1 through 8 (of 8 total)

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

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 1

Instead of DTS, better try to pull the data using Import/Export Data methodoly. But the only hindrance here is you need to change the data types of access so as to suit to sql data types.

Neville Turbit Grasshopper Points: 14 More actions · Answer 2

Problem is I have to repeat the exercise across many clients and there are over 70 tables. I thought DTS seemed like a simple option.

dusty-526638 Grasshopper Points: 13 More actions · Answer 3

I assume your using the DTS wizard in enterprize manager? If so make sure you are using the correct data type within the wizard. If your importing from Access, it should be set to Access. This is a drop down menu in the top of the wizard. If you have it set to Access then you would just select the file (rather than use Windows Authentication mode). Windows Authentication mode should only be an option if you were importing from another SQL server.

Hope this helps and good luck.

Neville Turbit Grasshopper Points: 14 More actions · Answer 4

I am using DTS exactly as you mentioned. Is there anything else I should be looking at?

dusty-526638 Grasshopper Points: 13 More actions · Answer 5

That's strange. I just ran a test exactly as you describe and I can preview the data fine. I'll think on this and if I come up with anything, I'll let you know.

dusty-526638 Grasshopper Points: 13 More actions · Answer 6

Did you try to run it rather than just preview it? Just curious if would run. Probably not.

Neville Turbit Grasshopper Points: 14 More actions · Answer 7

Thanks for the suggestions. After you mentioned you had tried it and it worked I imported all the tables into a new Access database and tried again. Amazingly it worked. I now have a whole stack of errors relating to relationships - Foreign key constraint failure. I will investigate a bit more and may post another question. Thanks for your help.

dusty-526638 Grasshopper Points: 13 More actions · Answer 8

Yes foreign keys can be a pain for imports and exports. Make sure you import/export the related tables first. That should clear that up. Alternately if you remove the keys from the destination database, that would definately clear it up. However, for obvious reasons that may not be a good idea.