Solved

Transfer MS Access data to GoDaddy MS SQL 2005 Database

Posted on 2009-04-08
12
740 Views
Last Modified: 2012-06-27
Classic ASP VBScript
MS Access
SQL 2005

Hello.  I have an MS Access db from which I now want to transfer the data to a SQL 2005 db on a shared hosting provider:  GoDaddy.

They have a Query Analyzer that I can use to CREATE tables and INSERT data; however; I don't want to manually reINSERT over 2,000 records.

So, how can I take the data from the Access db table and transfer it to the destination SQL 2005 db table?

Thank you so much,
Shane
0
Comment
Question by:lshane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24103934
When you use Query Analyzer, do you need to log into GoDaddy in some way first (i.e. remote console, remote control, or VPN)
Are you able to paste from your screen into the query analyzer screen? Are you able to copy files on the the SQL Server?
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 24103936
Use SQL Server Migration Assistant for Access to convert the Access tables and then move the converted db.


http://www.microsoft.com/downloads/details.aspx?FamilyId=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en
0
 
LVL 28

Expert Comment

by:sybe
ID: 24104302
Copy/paste works.

You can use SQL Management Console from your desktop to connect to the remote server. Or you can use MS Access as a frontend for the remote database (create an adp). In both cases you can copy the whole table from the original database and paste it into the new.

Alternatively you can create a linktable in your MS Access database and do a "SELECT INTO <remote_table>"
0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 

Author Comment

by:lshane
ID: 24104384
nmcdermaid:
Yes, I login on the GoDaddy console and am able to run scripts in a Query Analyzer to CREATE, INSERT, UPDATE, etc.

jmoss111:
Thank you for the link.  I downloaded the wizard and ran it.  It seemed to convert OK and loaded to my SQLSERVEREXPRESS db.  I was able to right-click and save the database as a SCRIPT, and then loaded that file in the GoDaddy Query Analyzer and EXECUTED it... and it created the database OK; however... the data did not transfer to GoDaddy.  The script seemed to only be the db structure, but not the actual data; however, the data IS in my local SQLSERVEREXPRESS db.  How can I get the data, along with the table(s) to load into the remote Query Analyzer?

sybe:
These seem like viable options; however, I am definitely a newbie to SQLSERVER/EXPRESS, and so some of the tools are not familiar to me, such as the SQL Management Console or "ADP".  Not sure what these are.  

So, the main obstacle, at this point, seems to be getting the data to the remote database on GoDaddy, since the Migration Wizard seemed to work fine in converting the db from Access to SQL Server.  It just only created the db structure on the remote GoDaddy db when I executed the "Database Script"; I need th actual data in it to also be loaded/copied onto the remote db.

Is there a way to do this?  If so, how?

Thanks so much so far,
Shane
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24104606
To also generate the INSERT statements you can probably use TABLEDIFF.EXE
1. Run your creation scripts on your database to create a new blank database. Don't overwrite your existing one
2. Go to a command prompt and type
TABLEDIFF -sourceserver <yourserver> -sourcedatabase <your db with data> -sourcetable <yourtable> -destinationserver <yourserver> -destinationdatabase <your empty db> -destinationtable <youremptytable> -f C:\INSERT.SQL
This should compare your two local databases: your converted one with data and your converted one without data, and generate a script with required insert statements.
If you can actually get a direct database connection to the database server then it would be easier to use the MS Access method, but I'm not sure that you will be able to get a direct connection over the internet - its usually firewalled off.
 
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24104617
oh.... by this:
1. Run your creation scripts on your database to create a new blank database. Don't overwrite your existing one

I mean run you creation scripts on your local server (not the godaddy one) in a NEW database (not your upgraded one). The idea is to get a blank database so that when TABLEDIFF compares them, it generates all the required insert statements.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24104624
.. and be aware that your creation scripts will probably try and overwrite your upgraded database - take a look in the script.
Gotta go - long weekend coming up!!!
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24105426
You could use database publishing wizard from Microsoft which will script out the db create and data load.
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 500 total points
ID: 24105429
SQL Server Publishing Wizard
0
 

Author Comment

by:lshane
ID: 24123152
Hi, jmoss111.  I downloaded the SQL Server Publishing Wizard,and it seemed to create the ".sql" file OK; however, when I accessed the GoDaddy Query Analyzer and browsed to the ".sql" file to open it, it continued to timeout.  Not sure if the contents of the ".sql" file were too much, or not.  In the Publishing Wizard, it only gave me the option to select the entire SQL database to publish, not individual tables.

Any thoughts how to get around this?
0
 

Author Comment

by:lshane
ID: 24219150
Sorry for the delay.

Thanks, jmoss111.  I was able to get both of your suggestions to work (Migration Asst and Pub Wiz).

Works great!

Thanks so much,
Shane
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24219853
Cool, have a great day. I'm glad that i was able to help out a bit.
0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question