I am using the following piece of code to rotate some banners. I have recently updated from 97 to 2K access. However, using the connection string for 2K I get the error
set objConnad = Server.CreateObject("ADODB.Connection") set objAdv = Server.CreateObject("ADODB.Recordset") objConnad.Open strConn
strSQLad = "SELECT * FROM Books WHERE Zone='webmaster' AND PageName='commerce'"
objAdv.Open strSQLad, objConnad, 3, 1
________________
Two things are apparent: 1)If I use the 1st connection string I get the error on the line that opens the whole thing. If I switch to the other connection all is fine. 2)If I remove the WHERE part of the SQL statement it always works fine.
What is it about the SQL statement that doesn't like the Jet connection that doesn't like?
The error for 1 probably is due to an older version of MDAC installed on your server. Access 2000 is somewhat of a misnomer when the db is on a web server. Access itself isn't used, rather ADO is used to get to the database. The first connection string requires MDAC 2.1 (or maybe 2.0) to be installed. The other string will use the Jet 3.5x ADO drivers and works for all versions of ADO.
In addition, the first is a native OLEDB driver and the 2nd is an ODBC driver.
I'm not aware of any problems using the older connection string with an Access 2000 .mdb file. You can download the newer MDAC from www.microsoft.com/data (version 2.6 is current, but doesn't include Jet drivers needed for an Access database, you need to download them separately. Version 2.5 is fine unless you need to work with SQL Server 2000)
I have no idea why removing the Where statement changes anything.
I probably didn't phrase it right but I don't get any problems with the older connection string at all.
To reinforce the WHERE statement part I have noted the following:
The main page is called by an ID number and uses an Access2K DB with the newer connection string.
Unfortunately as I am on a virtual host and have no control on which MDAC version is installed but will try to find out. I am getting the same problem on PWS.
The whole thing seems to be down to anything that the where statement calls surrounded by quotes. - very odd!
Topic Locked
Posted - 01 March 2001 : 17:58:27
