Arghhh! ODBC, MS-Access, IIS,PHP, again <sigh>

Applications Database Connections
#1

Last year I prepared a project for someone who wanted to use their MS-Access 2000 database. I had a hell of a time getting the SC7 project to connect MS Access via ODBC, but eventually got there. The project had been working fine on the customer’s Win7, IIS, PHP setup.

Last week changes were made to the project, which had been brought into SC8. The customer’s computer was then updated.

Now we are no longer able to connect to the database. The error message is a very informative “Connection Error Unable to connect:” :frowning:

The (DSN) ODBC is known to be working in other applications on the customer’s PC, so we can rule out a faulty ODBC.

Testing on my own development PC shows that the ODBC works fine in the SC8 IDE. However, it also fails in IIS/PHP.

The customer does not want to install WAMP

I’m at my wits end on this, so any guidance, tips, prayers, mojos etc to get this to work would be greatly appreciated.

Cheers

#2

IIS is a burdon to get it running right especially on W2012 server. I run on this and fortunately I have Plesk on it to ease my task. I think that this is a firewall issue on the database port but I have no idea what to do on a plain windows IIS server. If you have plesk then you will find these options under ‘server’ - around the sixth tab or something (not quite sure). Other option might be that it is a security issue that disallows php to connect which you need to correct.

#3

If possible ditch the IIS server and go for an apache setup like wamp / uniform / etc…
Anyway the main tip is to check your log files from your windows server. You should see the real reason why it is going wrong.
The thig under IIS that mostly goes wrong is that thr rights are not properly set. Under IIS you normally run as a restricted user (see for example on an old IIS version this page: https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/3648346f-e4f5-474b-86c7-5a86e85fa1ff.mspx?mfr=true).
That IIS user is normally not allowed outside the inetpub path and several network things are also not allowed. Apart from that you have to create the ODBC setup on system level, not on user level.

So if that is the case you can create a new user with a few more rights and set the IIS service to run under that new user with the new rights.
The great tip is to check your system logs…

#4

… and running with which operating system or version?

#5

Sorry, the project update was installed. The underlying OS, IIS etc was unchanged.

#6

That’s not an option at this point, as much as I’d like to :frowning:

The great tip is to check your system logs…

Yea, I did that. It’s not exactly helpful with a bunch of messages like this…

2015-02-05 23:42:31 ::1 POST /finalfiler/_lib/prod/lib/php/devel/iface/admin_sys_allconections_test.php rand=0.8305282708345331 80 - ::1 Mozilla/5.0+(Windows+NT+6.1;+rv:35.0)+Gecko/20100101+Firefox/35.0 200 0 0 2060

… when the ODBC connection is attempted.
The conumdrum is that the project prepared with SC7 worked fine for months. The SC8 project falls over on the connection attempt.
We have put the SC7 project back and it is working.
As I said initially, I am at my wits end.

#7

Look at the odbc dialog at tab “Drivers”. Which driver do you use?

#8

I actually meant to check the event viewer logs, not the iis logs.

#9

OK, after hours of hair pulling as in “out”, I will walk away pretty much convinced ODBC & Access do not work in SC8. Our SC7 project works fine, SC* falls over.
We will stick with SC7.
What a crock!

#10

Hi GuiGuy,
I’m trying to use standard ODBC connection and it seems to work .
I got problem with doing select against MSAccess tables.
For istance I got this strange error
odbc_exec(): SQL error: [Microsoft][Driver ODBC Microsoft Access] Sintax error… Query not completed…, SQL state S1000 in SQLExecDirect

This is the easy query:
$sql_customer_table="SELECT
CLI_FOR,
CodAnag,
‘Ragione sociale’,
NomeContatto,
Indirizzo,
‘Localit?’,
Provincia,
CAP,
Nazione,
NumeroTelefono,
NumeroFax,
Email,
Codag
FROM
‘QUERY CLIENTI C’ ";

#11

Those single quotes could cause problems. Sadly enough you didnt post the rest of the error, this is a specific error that normally has some text that explains the reason.