• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

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.
0
jjnet123
Asked:
jjnet123
  • 2
1 Solution
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now