Re: VB6, DAO, Oracle and dates....

First of some more background:
The problem I have is that this application was developed a few years ago and I have been given the task of bug fixing and enhancing it. Changing ODBC drivers, or any other client configuration is not an option, the clients current environment works as is and they are not keen to change, although with enough justification may be persuaded.

_at_Jim,

The reason for converting the date field is because the field in the database has a timestamp (ie "13/06/2002 12:45:56"), so I was wanting a way to drop the time. Using the between two dates approach would work, but would also mean changing a few places in the code.

_at_Eric

I believe that the application originally used access linked tables, but this has now been changed (probably not that well) to go directly to Oracle. I am connecting to the database using the Oracle ODBC driver installed as part of the client install for 7.3.4. I have tested using the dbSQLPassThrough option, but this would appear to give me a read only recordset. The DAO library I am using is "Microsoft DAO 2.5/3.5 Compatibility Library", which I am not overly keen on changing, but if this would fix this problem might be an option we will have to use.

The database connection is made in the following code: Set myDB = OpenDatabase(ODBCDSN, False, False, "ODBC;DATABASE=" + ODBCDatabase + ";UID=" + ODBCUser + ";PWD=" + odbcpassword + ";DSN=" + ODBCDSN) Where myDB is of type database the variables are set from values in an ini file.

The queries are stored in querydef's like this: Set qdMyQuery = myDB.CreateQueryDef("", _

   "PARAMETERS P1 NUMERIC, P2 TEXT , P3 DATE; SELECT * FROM mytable WHERE PERSON_ID = P1 AND COURSE_CODE = P2 AND REQUIRED_DATE = P3") and recordset is populated like this:
Set qdThisQuery = DBLib.qdMyQuery
qdPersonCourse(1) = CourseCode
qdPersonCourse(2) = RequiredDate
Set rsPersonCourse = qdPersonCourse.OpenRecordset

I have decided to go for using a view, but any pointers to get working another way would be great. I also read somewhere the oracle holds dates in the "DD/MON/YY" format, should I ideally use this format? and has anyone got some pointers to make handling dates fool proof?

Cheers for the help

Nyami Received on Mon Jul 29 2002 - 13:04:13 CEST