Moving Tablesfrom Access to MS SQL Server

[Aish1108]

I have a question and I'm hoping someone can help me. My website is completely dynamic with all my data stored in a Access database. I have a Deluxe Hosting W /ASP account with GoDaddy.com. I need to move all my data and tables to a MS SQL database as I now have over 500 visitors a day and would like to increase that number.
HOW DO I DO THIS? The MS SQL server is hosted on GOdaddy's server. Does microsoft have a program I can use? what is Enterprise Manager (SQL Server)? Where can i download it and is it free? Any help would be very much appreciated. Thanks

Also, What program can I use to access this data once it is up on the SQL SERVER? I would like to find a free program if possible.

Thanks!

 

[mrdenny]

Enterprise Manager is included with SQL 2000 server. It's not available as a seperate download.

Odds are that GoDaddy.com doesn't allow direct access to there SQL Servers over the internet. They should provide you with web based tools that can be used to connect to the SQL Server. You will need to look at the tools which they have provided you with in order to figure out how to migrate the data.

You may be suck creating the tables manually and then exporting the data from the Access database into CSV files and importing them via what ever tools GoDaddy.com provides you into the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Aish1108]

Thanks. You are right about GoDaddy. I finally got to speak to what must be their one tech guy who know anything. And he said I must use their control panel to export the data from the Access database into CSV files.

My problem now and maybe you can help me is I have a table with a memo field and it wont save as a CSV. Their are too commas , whitespace, newlines, quotaion marks and I'm not sure how to export it.

THanks. Ideas would be appreciated.

 

[AlexCuse]

I'm not sure what the character length is for a memo field in MS Access, but if it is under 8,000 I'd think you will be fine. You will just need to replace any characters that will cause problems for SQL Server (' mainly) and any characters that cause problems for the .csv export (whatever you are using for the text indicator, probably "). Because of this, you will need to export from a query, not your table.

set up your query like this:

Select all fields individually from your table. THen find your memo field. You will replace the memo field name with something like this:

expression: replace(replace([MemoField], """, "`"),"'","^") from tblName

This will replace your double quotes with a ` and single quotes with a ^. If you need more guidance, I suggest you try forum701, as this is really more of an access question.

Hope this helps,

Alex



Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[sathandle]

*********
You may want to consider exporting your data to a fixed length text format instead. Set the field length for the memo field to the maximum length + 100.
*********
Access also has an upsize wizard that "upsizes" the Access database to SQL Server. You may want to look into that. Look at the triggers it generates for any randomly generated ID fields.
*********
Also, to work with 'SQL formatted' databases on your local machine, look into MSDE or SQL 2005 Express, a free download from Microsoft. (Google - MSDE download) It will not work as a web-server due to its maximum connections limitations.
*********
Good luck.


Don't beat your head against the wall unless you know how to plaster.

 

[Aish1108]

Access wont let me run the following query
expr2:replace([poem], """, "`")

It gives me an error message about missing a closing parenteses

 

[mrdenny]

Yeah, it will. It should look more like this I think (change in red).

expr2:replace([poem], ""[red]"[/red]", "`")

The upsizeing wizzard won't do Aish any good as he doesn't have direct access to the databases, only via the GoDaddy.com web control page.

If I remember my MS Access correctly a Memo field is a variable length field that can hold any amount of data that you want. It's SQL euilivent would be a TEXT or NTEXT field.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com