[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Transfer MS Access data to GoDaddy MS SQL 2005 Database

Posted on 2009-04-08
12
Medium Priority
?
753 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 2000 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
Industry Leaders: 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!

 

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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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