Help with access db to sql ?

Discussion in 'Databases' started by RichHikins, Jul 13, 2010.

  1. Hi All,

    I am new here and it has been a longtime sine I worked on a MS SQL website. So please bare with me.

    I have just started hosting with Winhost and need some help:

    My preivous host went bust and it has taken a while to recover everything.

    They sent me my SQL database as an Access Database I think they must have exported the information to an access file so I could see it?

    I have uploaded my site now to www.richhikins.net and you can see by the error messages it looks like it needs a connection to SQL ???

    I need to get my Access database to a SQL database. Can someone give me some guidence or do it for me?

    thanks in advance.
    Rich
     
    Last edited by a moderator: Oct 14, 2015
  2. Ray

    Ray

    How did you code your connection string.

    The error...

    (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

    ...is typically a connection string problem.
     
  3. It's because I haven't done anything with the database... I do not know what to do with the database at the moment.. I need help getting my access database which is on my laptop to my website in my control panel as a SQL database...

    Site worked fine on old host.

    Can you help?
     
  4. Hi Ray,

    In answer to your quesiton: The code is as follows:

    <?xml version="1.0"?>
    <ModuleSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <ConnectionString>server=81.3.74.40;database=RichHikins;uid=RichHikins;pwd=*********;</ConnectionString>
    </ModuleSettings>

    obviously I will have to change this for Winhost as this is my old host details... but how do I do this!?

    Thanks
     
    Last edited by a moderator: Oct 14, 2015
  5. Ray

    Ray

    You have two issues here. One is that you need to move your data from Access to SQL. The other is updating the connection string so that your application is going to look at the SQL server.

    Both will require some coding from you. This is something that is not set on the server.

    First log into your Control Panel and make sure you create the SQL 2008 database.

    Try using this link.

    https://cp.Winhost.com/sites/mssql2008.aspx

    Next you need to migrate your Access database to SQL 2008. I don't know what version of Access you have on your computer, but I am going to assume you have Access 2007 so I found this link for you to use.

    http://office.microsoft.com/en-us/access-help/use-the-upsizing-wizard-HP005273009.aspx?redir=0

    Read through the page, it will give you instructions on how to upsize Access 2007 to SQL 2008. If you don't have Access 2007 on your computer, you are going to need to purchase one and install it on your computer. Like I said this is something that is not set on our end. This will have to be done manually on first on your end.

    Once you have made sure you uploaded the tables and data to the SQL server, then you need to change the connection string.

    When you created the SQL database in the control panel you will see a link that reads "Manage". Click on that. It will pull up information about your database. The name of the SQL server where the database exist, the database name, and the db login. In the bottom it will also have an example of the connection string to your database on our SQL server.

    If you want to manage or view the database you created on our SQL server you will need to download SQL Server 2008 Management Studio. This is free from Microsoft and set it up so it connects to our SQL Server remotely. Try reading this kb.

    http://support.Winhost.com/KB/a689/how-to-connect-to-sql-server-2008-using-sql-server.aspx
     
    Last edited by a moderator: Oct 14, 2015
  6. Hi Ray.

    OK.. I am using Office 2007 the access file is in access 2000 format but opening ok in 2007.

    I am using the upsizing but getting stuck... I have created the new SQL database in my account at Winhost but when I choose use exisiting database in the upsizing wizard it asks for a File Data Source... DSN NAME... and opens a box to browse to a file on my computer??

    What do I do now?

    Thanks
     
    Last edited by a moderator: Oct 14, 2015
  7. Ray

    Ray

    With this Window there should be a button that reads New...

    Choose that and scroll down until you see SQL Server Native Client 10.0. You'll be choosing that.

    Then when you click on next it should ask you to the data source file name. It really doesn't matter where you save it on your computer but to make things simple save it on your desktop.

    Then when you click on Finish it will open a new window to define the values for your datasource name. Type the server to be the SQL server assigned to you. This is inside the control panel under MS SQL 2008 Manager. When you click next choose the SQL server authentication and type your db login and password. In the next window you should have an option to choose the default database. Make sure you choose your database you created on our SQL server. The rest of the settings you should be able to leave as its default state. Click finish and now you can connect to our SQL server with Access. The rest of the way just follow the instructions provided by you by the Wizard.
     
  8. Hi Ray.

    I do appreicate your help... and I am getting there... I went through everything and then it said " The upsizing wizard does not work with the version of Microsoft SQL Server to which your Access project is connected. "

    Not sure what to do now?

    Thanks
     
  9. Ray

    Ray

    Try upsizing your Access database. I believe it may not be a full Access 2007 version. Go to the top left corner of your Access database. Click on the Office button and choose convert. Make sure you convert it to Access 2007 and try running the Upsize wizard again.
     
  10. Hi Ray... Tried that... but same problem.. you were correct it wasn't a 2007 file.. but it is now, however same error!

    Any other ideas?? Do you fancy doing it for me?

    Thanks
    Rich
     
  11. Ray

    Ray

  12. Hi Ray.

    I think I am getting there.. I have now managed to do this and it seems to have worked... I have also changed the connection string to the correct settings... however I am still getting errors...

    www.richhikins.net (I'm not sure what I am doing wrong now!)

    It's hurting my head.. can I just pay someone to fix it?

    Thanks
     
  13. Ray

    Ray

    I don't think it loaded everything. Check your Access database again, look for any stored procedure inside the Access database. If you do not find any, then your old hosting provider didn't give you everything in the database. They could have only given you the tables, which is not good because your website is looking for store procedures and God knows what other things. If this is important to your business function then you may want to consider acquiring the services of a web developer who is adapt in databases and SQL. They maybe able to help you better when it comes to getting the things you need from your old hosting provider.
     
  14. Hi Ray,

    I have managed to get a database now in .mdf and .log format which I think will have all the information I need... can you advise on how to get this on to my site?

    Thanks
     
  15. Ray

    Ray

    First you'll need to download SQL 2008 Express on your computer. Once you installed it, run through the process of attaching the MDF file to a database on your SQL 2008 Express. Once that is attached, go through the process of backing up your local database. Your local database should have all the data, objects, and sp that your .mdf file carries. You will run the backup procedure on your local machine and then you use the .bak file to restore it to our server. Try looking at this kb article. This kb article explains how to use the Restore feature in the Winhost control panel to restore your database to our SQL server.

    http://support.Winhost.com/KB/a771/how-do-i-generate-or-restore-a-backup-of-my-database.aspx

    Try also looking a this link to attach a mdf file on your local database.

    http://www.kodyaz.com/articles/sql-server-attach-database-mdf-file.aspx
     
    Last edited by a moderator: Oct 14, 2015

Share This Page