Link to home
Start Free TrialLog in
Avatar of lshane
lshane

asked on

Transfer MS Access data to GoDaddy MS SQL 2005 Database

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
Avatar of nmcdermaid
nmcdermaid

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?
ASKER CERTIFIED SOLUTION
Avatar of jmoss111
jmoss111
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sybe
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>"
Avatar of lshane

ASKER

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
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.
 
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.
.. 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!!!
You could use database publishing wizard from Microsoft which will script out the db create and data load.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lshane

ASKER

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?
Avatar of lshane

ASKER

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
Cool, have a great day. I'm glad that i was able to help out a bit.