UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Import Accdb Tables To SQL Azure, SQL Server 2012    
 
   
johnpdmccall
post Apr 20 2017, 05:14 AM
Post#1



Posts: 1,678
Joined: 14-March 00
From: Scotland


Hi Folks

What I'm trying to do is: Export tables from Access.accdb to a SQL database on my Azure account. Or import them to SQL Azure
I've created a database with no tables on Azure.

Question 1: What's the best way to export or import tables to my SQL Azure database?

Question 2: I've tried exporting a single table from Access to SQL using a dsn file for the connection but I'm getting the error:
"General error: Invalid file dsn '' (#556)"

Here is the connection string with the personal stuff removed:
CODE
Driver=ODBC Driver 13 for SQL Server;Server=tcp:mydb.database.windows.net,1433;Database=mydb;Uid=user@mydb;Pwd=mypassword;En
crypt=yes;TrustServerCertificate=no;Connection Timeout=30;


Thanks for any help

--------------------
Cheers,
John
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 07:18 AM
Post#2


UA Admin
Posts: 29,730
Joined: 20-June 02
From: Newcastle, WA


Good morning. I prefer the SQL Server Migration Assistant for this task.

--------------------
Go to the top of the page
 
johnpdmccall
post Apr 20 2017, 01:26 PM
Post#3



Posts: 1,678
Joined: 14-March 00
From: Scotland


Thanks George, I managed it using SSMS apart from one table which it doesn't seem to like. I'll try the migration assistant.
Still no joy with Microsoft fixing my sharepoint/AWA issue!
So I've set up an Azure account and will move the more vital stuff to it.

--------------------
Cheers,
John
Go to the top of the page
 
jleach
post Apr 22 2017, 06:00 AM
Post#4


UtterAccess Editor
Posts: 9,698
Joined: 7-December 09
From: Staten Island, NY, USA


In some cases, it might be easier to migrate from Access to a local SQL Server copy, then once that's solidified move it from the local SQL Server to SQL Azure.

It's been a while since I've done a direct Access -> SQL Azure migration, but in the past, depending on the number of records, there could be some utilization issues. Also, if you have anything more than a handful of records and are going direct to SQL Azure, I recommend increasing your SQL Azure service tier for the import process (you can lower it again after you're done).

hth

--------------------
Go to the top of the page
 
GroverParkGeorge
post Apr 22 2017, 08:38 AM
Post#5


UA Admin
Posts: 29,730
Joined: 20-June 02
From: Newcastle, WA


That's a good point, Jack. Practice locally before venturing out onto the "internet highway".

Getting your data out of one SQL Azure database and into another might be somewhat different, but I still might do that same thing with the dacpac: locally first, clean up the schema, then back to the new SQL Azure (or other hosted SQL Server).

In my blog post yesterday, I also go over the process of moving the data from the AWA's SQL Azure into a local accdb, which could be a good way, I suggest, to get your data into a local storage container for refinement, testing, etc. as you suggest.

--------------------
Go to the top of the page
 
johnpdmccall
post Apr 22 2017, 08:59 AM
Post#6



Posts: 1,678
Joined: 14-March 00
From: Scotland


Thanks Jack - I'll do that

--------------------
Cheers,
John
Go to the top of the page
 
johnpdmccall
post Apr 22 2017, 08:59 AM
Post#7



Posts: 1,678
Joined: 14-March 00
From: Scotland


Thanks again George, all good suggestions!

--------------------
Cheers,
John
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th June 2017 - 10:39 PM