Access to backend Sql server 2008 to 2017 RRS feed

  • Question

  • I have an Access front end linked to SQLServer2008 and am trying to migrate to a new windows server/ SQLServer2017. I have restored my database/tables on the new server but cannot link the tables from Access to it. I've tried to relink the tables/views in Access using the link manager and also using an ADOConnect String. In both cases I get an error that 'the server does not exist or access denied'. I've loaded a new SQL Server Driver (ODBC Driver 17 for SQL Server), I've looked at file permissions, I can ping the new server from my windows Access front end machine and I've compared the setup parameters of the two servers and SQL Server instances but so far having no luck with the connection.
    Tuesday, December 17, 2019 7:58 PM

All replies

  • Perhaps you try a copy of SQL manager studio on your working computer. 

    Are you using windows authentication, or SQL logons? I find that after a database restore, the user for that database does not work. Because it often hard to delete the existing user, re-name it. 

    Try connecting to the sql server from SQL studio, but use the SAME user + logon. Does that work?

    Also, make sure that the server has the SQL browser service running. Older versions of SQL server often did not care or need the SQL server browser service running. But later versions really do need the browser service running, especially when using a sql "instance".

    I would consider installing SQL manager studio on that computer, and see if it can see + work with that instance of SQL server.

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada


    Tuesday, December 17, 2019 8:48 PM