1 Reply by Janet Simkin (edited by Janet Simkin 10-03-2009 23:10:45)

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Linked access database

I have been using the linked Access database NBNData.mdb with Recorder 6, running on Microsoft SQL Server 2005 Express Edition, without any problems until the last few days, but now the link seems to have been broken. The error message is:

Connection failed:
SQLState: '01000'
SQL Server Error: 1326
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

I have tried setting up a new Access Project to link to the R6 database but that fails with a similar error, SQL Server does not exist or access denied.

Nothing has changed on my system over the last few weeks except that windows update has recently installed Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 3 (KB955706). The microsoft website list several known problems with SP3 but none that seem at all relevant to this problem.

I'm baffled, but I really need to get this working again quickly. Any suggestions?

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Linked access database

HI Janet,
I'm not sure, but it sounds like SP3 has altered the security settings of your SQL Server 2005 Express Edition so that Access is unable to login. It may not be considered a problem by Microsoft, as it would be a deliberate tightening of security. Does the date of this service pack being applied coincide with the change?
Best Wishes

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Linked access database

I think it probably does, but can't quite remember when I last used the access database. If it is a change to the security is there any way round it? I really need this link!!!

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

4 Reply by Lynn Heeley

  • Lynn Heeley
  • Administrator
  • Offline
  • Registered: 03-07-2008
  • Posts: 345

Re: Linked access database

Hi Janet,

We have a tool that recreates the links (and the table if necessary) from the nbndata database to the SQL server database. We have not released this widely as yet as we have not found anyone who can further test it for us (it has been tested by Dorset and here at JNCC and worked fine - but not externally due to a lack of users that needed it) - would this be useful for you to try out for us?

Lynn

5 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,628

Re: Linked access database

Janet

You don't actually say whether or not your ODBC link is working. Try setting up a new ODBC link and  run test at the end of set up. Link the R6 database to this new connection. I have seen the connection fail after changes to SQL server, and creating a new has always sorted out ths problem.   I haven't seen a situation where the ODBC  link is working and Access willl not connect.

Mike

Mike Weideli

MikeWeideli's Website

6 Reply by Janet Simkin (edited by Janet Simkin 13-03-2009 16:36:19)

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Linked access database

I tried setting up a new ODBC link, logging in as sa or NBNUser, but that failed with the same error. Uninstalling SP3 doesn't seem to be an option, so I took another look at the SQL server configuration and tried enabling TCP/IP on SQLEXPRESS. That worked in that I have now been able to set up a linked Access project, so I am up and running again.

However, there is still a problem with the linked Access database. Now it can find the server, but it can't login to it. Could I try the tool that recreates the links?

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

7 Reply by davva

  • davva
  • Veteran
  • Offline
  • From: Scotland
  • Registered: 03-11-2008
  • Posts: 55

Re: Linked access database

Hi All
We have a similar problem to Janet's. Most likely caused by a reinstall of Recorder 6.
We have two Access databases linked to the SQL server. The databases open fine but when we try to open a linked table we receive the following error messages:

Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

one one database, or

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'PC1\Guest'.

I think previously Recorder 6 was installed using Trusted Connection to login to the SQL server. Now it is using the 'sa' server login. Is there a simple way either to change the way Access connects to the SQL server (Trusted Connection appears to be the default) or to change the instance of Recorder 6 to Trusted Connection?
Thanks in advance
David

[color=blue]David Angel[/color]
[color=blue]Data Officer[/color]
[color=blue]The Wildlife Information Centre[/color]