Re: SQL from access

"John Fu" <zenki198_at_yahoo.com.au> wrote in message news:<8byB7.216251$bY5.958168_at_news-server.bigpond.net.au>...
> I've been developing ASP pages whilst using MS Access. However, recently,
> my clients changed their minds and wanted it to be implemented in Oracle.
> Now I have around 130 odd pages, and each one of them has Access SQL
> statements in them.
>
> Now I was just wondering, instead of rewriting all the SQL statements into
> ANSI SQL statements, are there any ways that could speed up the process? So
> I guess I'm just asking, are there any short cuts to this? If so, please
> reply. Or if you have any good ideas that you can spare, please tell me.
>
> Thanks very much.
>
> John.
john
You should set up a global variable in your web to contain your ODBC connection string to the database of your clients choice. Today it's Oracle, tomorrow sql server. Write very generic sql. If they change dbs, you change you connection string at the global level. Unfortunately, you will still have to slightly modify every page that has sql on it, but you'll be ready when they switch databases again next month.

This will also prepare you to have only one change when you "go live" and you change your connection string to point to your production db server. Here's some sample code you should exec when you logon to your app

good luck

Session("ConnectionTimeout") = 30

        Session("CommandTimeout") = 30         

<!-- 	  change to dev db  -->	
	Session("BratsConn") = "DSN=BRATS_DSN;" & _
	        "DATABASE=BRATSMcLeod;" & _
		    "UID=BRATSAPPL;PWD=BULKMATIC"		
-->
'change db on the fly based on iis server		      
IF Ucase(Request.ServerVariables("SERVER_NAME")) = "BTC_IIS" THEN
	Session("DBServ")   = "BTC-BRATS"
ELSE
	IF Ucase(Request.ServerVariables("SERVER_NAME")) = "BTC_DEV1" THEN
			Session("DBServ")   = "BTC-DEV1"
		ELSE
			SESSION("DBServ")   = Request.ServerVariables ("SERVER_NAME") 
		END IF
	END IF
	
	Session("DBName")   = "BRATSMcLeod"	
	Session("DBUID")    = "sa"
	Session("DBPWD")    = "sa"