Cant establish ODBC connection from Access to database in Dev

[jajwilson]

When I try to connect to the 'Irish Surgeons 6' database in the project 'Standard Test', I get the following error:

I have turned odbc on for 'Standard Test', and I'm using the strings that I am given on the My Account page. Standard Test is an old project, having been set up back in January 2015, but this shouldn't prevent ODBC from working.

[jajwilson]

I'm getting the same problem with newly created projects too.

[thestoat]

The problem here is that you have specified the wrong database name! I don't see main_1399469_1397627 referenced anywhere, which is odd seeing as how you are referencing it. Anyway, To access your database you need to use main_1399515_1397627.

There is a problem in how ORDS reports the same of the database to access via ODBC. I'll fix that under this issue.

[jajwilson]

Ah. That's (partly) because the ORDS is misbehaving. 'Irish Surgeons 6' isn't being listed as under the ODBC index any more, but it was when I first found the problem, and I sweat that I used the connection string it was suggesting at that point. Moreover, Irish Surgeons 6 DOES still exist as a database when you look at the dbs in the Standard Test db list.

Other databases in the 'Standard Test' project are also not being displayed in the list of ODBC connection details - such as 'oxrep upload test 20th November' for example, and indeed the other 'Irish Surgeons' numbers.

That said, the new database name you provide in your email does indeed work - I can now establish an ODBC connection between Access and 'Irish Surgeons 6', so that's good news!

[jajwilson]

I should of course have sworn, rather than sweating.

[thestoat]

Yes ... closing in on why the names aren't being properly reflected on the preferences page ...

[thestoat]

Please try now ...

[jajwilson]

This is very close to working, but it's inconsistent. I can now see the connection details for all of the projects in the 'Standard Test' project - so that's good.

I can establish an ODBC link from Access to 'Irish Surgeons 6', but I cannot establish a link to one of the other databases in the same project - 'oxrep upload test 20th November'. I'm getting a similar error message to the one at the top, just with the db name changed to 'main_1399417_1399415'.

[jajwilson]

I've now tried connecting to 'oxrep upload test 20th November' using the 'jameswilsonitoxacuk' username string - but that doesn't work either. I get the following message:

[jajwilson]

I should add that when I successfully connected to Irish Surgeons 6, I used 'james.wilson@it.ox.ac.uk' as my username, but couldn't use this to connect to 'oxrep upload test 20th November'.

[thestoat]

Ah - that is fine. Navigate to https://dev.ords.ox.ac.uk/ordsFrontEnd/preferences.jsp and change your password. When the user is first created (and since you presumably haven't used this user before, it has been "first created" to all intents and purposes), a random password is assigned to the user. You need to set your own via the page above ...

[thestoat]

You should not have been able to connect to the Irish Surgeons db using the creds above (though I can see why you were able to). I'll sort that.

[jajwilson]

Hoorah! I can now establish an ODBC connection to 'oxrep upload test 20th November'. I'll just try Irish Surgeons 6 again... Brilliant! that works too. I guess that we will have to explain the new regime to existing ODBC users, however. And I'm a bit concerned about the effects on the 19th Century Professions Project, who seem to struggle to work out how to change the connection string in their PHP - especially given that Kevin has moved to pastures new. Is there any possibility that we can preserve their existing connection string when this code migrates to the app?

[thestoat]

I could configure retrofit to email each project user explaining what they need to do to contact their DB via ODBC.

Wrt the Professions project, The new app will standardise their stuff and thus break what they currently do. However, since it isn't many people and a single db we can reinstate it by hand.

[jajwilson]

Okay. I don't think many projects are actually using ODBC connections at present, so could we just email those with ODBC switched on?

[thestoat]

I think regardless of retrofit, each ODBC-enabled project should be switched off then on again anyway. At that stage, project members should get an email notifying them of this, but I am not sure it has been implemented so I shall sort that now.

[thestoat]

All should be ok. If you need more word changes to the email then let me know.

[jajwilson]

Great. The one thing I would add to the email text is a brief description of what ODBC is/does, otherwise we'll have confused researchers contacting us. Something along the lines of: "ODBC enables users to connect to their databases via 3rd-party software, or from websites."

[thestoat]

Done ;-)