Help with upsizing Access 2007 --> SQL Server 2005

Hi, thanks for reading.

I'm well versed in Access, but have very little experience with SQL, so literally the smallest sillyest thing could be the problem. Or it could be more than that.

I've set up a new blank DB in SQL, I'm listed as a user (in the owners group) using network authentication. I've set up a system DSN connection to the DB on my local machine.

When I use the upsizing wizard, all seems to go through fine (no errors) however the resulting report names each table, saying that the table was skipped. No data actually goes in to the SQL DB.

I'd be grateful for any suggestions.

Thanks!

John.
LVL 1
jjnet123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd suggest using the SQL Server Migration Assistant:

http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx

It does a much more thorough job than does the standard Upsizing Wizard.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjnet123Author Commented:
Thanks for that, it gives a little more info.

I've tried importing using it, and I now get the following:

"You do not have permission to use the specified default database. 'mydb.dbo' will be used for the default schema mapping. Continue?"

Followed by (if 'Yes' is clicked):

"One of the internal SQL statements failed. The SELECT permission was denied on the object 'schemas'. database 'mssqlsystemresource'. schema 'sys'."

I've looked at the SQL management studio and have added as many permissions as I can see for my username for the server and my database. I'm assuming it's some kind of permissions issue but can't see where I need to apply them.

Thanks again for that pointer.
0
jjnet123Author Commented:
Ok, have found where permissions needed changing. Thanks very much for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.