Quick question - MS Access upsizing to SQL server

[101287]

Does anyone know how to debug the errors when upsizing an access database (2010) to SQL server using the wizard? I tried to upsize the table but fail with no explanation to address the failure.
Platform: Windows 7 OS/Office 2010/Ms Access 2010

Suggestions or guidance will be appreciated.

Luis

 

[1DMF]

When we moved to SQL from MS Access as the backend DB, I used the import facility in MS SQL.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[101287]

The tool that I'm using is Microsoft SQL Server Management Studio. Not sure that the tool has that capability but not sure.

Thanks for help.

Luis

 

[1DMF]

In SQL 2008R2 do the following...

1. Create a new Database
2. Right click on the database and hover over 'tasks', from the fly-out menu choose 'Import Data'.
3. The import wizard will start, from the data source drop down choose 'Microsoft Access'
4. Use the 'browse' button to select your access DB (only works with .mdb file types - so you may need to save to correct format first!)

Follow the wizard options to select your tables, mappings etc.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[101287]

Did what you asked. However, get permission denied. Any suggestions? Does not make sense because the 2008RS is on my client.
Thoughts or ideas.

Thank you, Luis

 

[1DMF]

Does the DB have a user and password associated with it, did you provide those credentials in the wizard?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[101287]

In the wizard I selected to use NT authentication. Wonder if that's the problem.

Luis

 

[101287]

I tried the recommendation to create a new database. However, now I'm getting a permision error. I'm using the Management Studio SQL and I'm making the assumption that when I open it, it creates an instance of SQL server in the client. This assumption looks like is not correct. Wonder if I need to get the DBA to create the instance for my client in order to star using the SQL to create new dataases. Does anyone know? This is the first time that I am trying to use the access 2010 upsizing and never has use SQL server.

Suggestion or feedback. Thanks, Luis

 

[1DMF]

Your MS Access DB uses NT Authentication for user access, doesn't sound right to me?

Also if you have MS SQL Studio Manager on a Windows SQL server , surely you have access to the SQL DB, how else are you logging on to the SQL server and running the studio manager?

However, if you have installed SQL Studio Manager on your Windows 7 desktop PC and don't have proper access to the SQL server, then you are going to have problems.

Where is MS SQL Server Installed? You cannot do this without full access to the SQL server, or access to the database instance created especially for this data import.

If you have a DBA, either give them the MS access DB and they can create and import it for you.

Or you need them to create the database instance and give you full admin (dbo) access to it.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[101287]

Thank you. The problem was that the technician that install SQL server did not provided me the "sa" account password. Therefore, I have the DBA to provide my NT ID the proper access. Thank you all for collaboration and helping me identify the root cause.

Luis

 

[1DMF]

No problem glad I was able to help and you resolved the issue.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads