Upsizing a ms Access database to MS SQL

Question

Upsizing a ms Access database to MS SQL

JAMES UPTON

SSC Enthusiast

Points: 122
More actions
February 15, 2006 at 8:19 am

#67267

NEED HELP PLEASE!!

Need some advice how to connect to my SQL server to upsize my access db!
I have a MS SQL server online and I want to test if it works by upsizing the classic MS Access sample database JobApplication.mdb I am having trouble upsizing the database regards to what bits of information to enter at each box of the wizzard can some please tell me what IDs should go where?.Thanks
I have set up a database on my mssql server using the webspace internet service provider called oneandoneinternet.co.uk. I used one of their tools to create this database just to test if the MS SQL server was working.
database name = db152591563
username = dbo152591563
host name = mssql10.oneandone.co.uk
description = james
on that page with the above data it says: log in to the admin tool using ur user name = u39697174 and password given
So which bits of the above information do i enter into the upsizing wizzard boxes for connection?
server: = ?
login id = ?
password = ? (which 1?password given or the database password?)
please can you help me log on and connect to the MS SQL Server!...using the upsizing wizzard!
Can anyone fill in the gaps please?..so i can connect and upsize my DB?..Thanks Alot!
Thanks James

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

cwong Grasshopper Points: 18 More actions · Answer 1

I've been working on a project to get off of our Access 97 database and get onto SQL Server 2000. I just copied all the tables from the Access database and placed them into SQL server 2000 through the DTS import wizard. I also ran into problems with the user/pass of the Access database. I just went into Access 97 and opened the database exclusively, then clicked on tools -> security -> unset database password. Then I was able to run the DTS import wizard without the security issues.

Perry-300990 Default port Points: 1451 More actions · Answer 2

James, the server name should be either the actual DNS host name of your supplier's SQL Server (if it is listening on the default TCP port), or else it should be the name of a SQL Server client alias which you created on your machine and configured to point to the correct host name and port number (you can use the cliconfg.exe program in your windows system directory to create or edit SQL Server client aliases).

For the username and password, they should be what your supplier gave you.

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 3

(for cwong)

To deal with a database password without having to remove it:

In the Import data wizard, in the Choose a Data Source screen, select Microsoft Access as the Data Source. YOu then have fields where you enter the File name, Username, and Password. If you are using only a database password (as in your post) and not access security user/group security, click on the Advanced button.

In the popup Advanced Connection Properties windows, expand the Property column until you can see the property name "Jet OLDDBatabase Password". Enter the password as the Value. Click OK. Carry on.

This way you don't have to remove the password from your application and then put it back.

George Hepworth Default port Points: 1423 More actions · Answer 4

I'm in the same situtation. This is a new feature 1and1 has made available to certain types of accounts they host. They've provided a limited SQL Server db.

The problem seems to be that 1and1 doesn't provide the actual DNS host name, only the version James specifies (e.g. mssql10.oneandone.co.uk), albeit a different one for my particular server.

I can connect to the db via ASP and an ADO connection string as defined by 1and1, but it would greatly simplify maintenance if I could use enterprise manager to work directly with the db.

Any suggestions on how one would go about getting the necessary DSN info to connect as James and I would like?

Thanks

George

JAMES UPTON SSC Enthusiast Points: 122 More actions · Answer 5

George!!!...thanks for replying at last I have found someone who understands my problem!!...

Im actually trying to use a wizzard program to connect to my MS SQL database so with regards to doing connection srtings in ASP etc...doesnt really apply to me becuase the wizzard is automatic as apposed to customisable...

If the MS SQL SERVER is limited can this be changed???...also are you using oneandone? isnt the web domain address dns address as in IP address for that server constitue as the database IP address? is mssql10.oneandone.co.uk = ip address of the server holding my several webnames ips?

and lastly Iv got a server engineer looking at this problem allready he works in liverpool for a big company i dont know where your from but as soon as i have a solution I will let you know,

Have you been in touch with oneandone.co.uk yer regarding this situation??...I find their custoner support absolutly terrible!...I have emailed them several times complaining!..

Please Respond!!...Thanks James,

George Hepworth Default port Points: 1423 More actions · Answer 6

Hello, James.

Actually, I'm on a 1and1 server in the US, but the rest seems similar. So far, support has been good, if not excellent, but I always call the 800 number and whine (LOL).

"isnt the web domain address dns address as in IP address for that server constitue as the database IP address? is mssql10.oneandone.co.uk = ip address of the server holding my several webnames ips?"

I thought this should be the case, but it doesn't seem to be, at least so far. This is actually something I only get to work on from time to time, so I haven't taken a run at it for several days.

I look forward to hearing more about it. And I will definitely let you know if I get any kind of answer.

George

JAMES UPTON SSC Enthusiast Points: 122 More actions · Answer 7

I know the answer!!...lol

Basically you can not connect to the database server other than a connection string that is on your server!!..

the only access availble to the database is locally due to security restrictions. Therefore in my case I has to upsize my database using the 1&1 import db wizzard and then change the web.config connection strings to match that database with the oneandone host name and user name and password for the DB!...

this connection was fine because the web.config file was on my server and not being accessed from home on my computer!..not local on my machine but local on the server which is fine!!!

And thats that George!!

Regards James

George Hepworth Default port Points: 1423 More actions · Answer 8

Thanks for the update. I was afraid of that. Oh well, their on-line db management tool is effective, though not too sophisticated. And it does beat using an mdb for the site.

George