Solved

Transfer MS Access data to GoDaddy MS SQL 2005 Database

Posted on 2009-04-08
12
727 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
  • 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now