Hi
In your documentation regarding the ODBC driver you reccomend " that you only use the ODBC functionality in VB 6 and not the ADO->OLEDB-
>ODBC bridge driver through the ADO functionality. The bridge driver does not function correctly in most cases." As I am new to VB and have only
used DAO and ADO connection I am not sure of how I should be making the connection and then opening tables. I have been able to open a
connection with ADO using a DSN but SQLs appear slow especially where joins are involved.

Can you give me any pointers/advice please.

We only have VB6 in-house currently being a small company this has been here sometime and raely used. What would you recommed for a
replacement.

Regards
Malcolm

Malcolm,

<< In your documentation regarding the ODBC driver you reccomend " that you
only use the ODBC functionality in VB 6 and not the ADO->OLEDB-ODBC bridge
driver through the ADO functionality. The bridge driver does not function
correctly in most cases." As I am new to VB and have only used DAO and ADO
connection I am not sure of how I should be making the connection and then
opening tables. I have been able to open a connection with ADO using a DSN
but SQLs appear slow especially where joins are involved.

Can you give me any pointers/advice please. >>

Sure, see this link for information on use ODBCDirect:

http://www.avdf.com/may97/art_odbcdirect.html

It's a little old, but still applicable.

--
Tim Young
Elevate Software
www.elevatesoft.com

Many thanks Tim. I will go away and see what I can do. Will let you know.

Regards
Malcolm
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Malcolm,

<< In your documentation regarding the ODBC driver you reccomend " that you
only use the ODBC functionality in VB 6 and not the ADO->OLEDB-ODBC bridge
driver through the ADO functionality. The bridge driver does not function
correctly in most cases." As I am new to VB and have only used DAO and ADO
connection I am not sure of how I should be making the connection and then
opening tables. I have been able to open a connection with ADO using a DSN
but SQLs appear slow especially where joins are involved.

Can you give me any pointers/advice please. >>

Sure, see this link for information on use ODBCDirect:

http://www.avdf.com/may97/art_odbcdirect.html

It's a little old, but still applicable.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim

I am able to connect now using ODBCDirect, using a DSN, but am having trouble with queries with joins. I am using the evaluation driver to see
what I can extract from our converted database before we commit to upgrading our software.
The sql I am "testing" at present is -
sSQL = "select ProductDay.ProductCode Product.[Description] From ProductDay inner join Product on ProductDay.ProductCode =
Product.ProductCode Where ProductDay.ProductCode Like '1%'"
(But would like to join upto 4 maybe 5 tables to produce the reports currently generated from our paradox database)

This falls over with the error message "Run time error 3669. Execution cancelled"
ProductDay has about 3.7 million records
Product has just short of 15,000

The database is on my laptop which runs XP home Sp2 with 512mb of ram

Could you please give me some pointers as to the problem, are there some settings in the  BDE that neen to be changed, the defaults are used
currently.

Regards
Malcolm

Malcolm <Malcolm@jjwilsonltd.com> wrote:

Many thanks Tim. I will go away and see what I can do. Will let you know.

Regards
Malcolm
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Malcolm,

<< In your documentation regarding the ODBC driver you reccomend " that you
only use the ODBC functionality in VB 6 and not the ADO->OLEDB-ODBC bridge
driver through the ADO functionality. The bridge driver does not function
correctly in most cases." As I am new to VB and have only used DAO and ADO
connection I am not sure of how I should be making the connection and then
opening tables. I have been able to open a connection with ADO using a DSN
but SQLs appear slow especially where joins are involved.

Can you give me any pointers/advice please. >>

Sure, see this link for information on use ODBCDirect:

http://www.avdf.com/may97/art_odbcdirect.html

It's a little old, but still applicable.

--
Tim Young
Elevate Software
www.elevatesoft.com

Malcolm,

<< I am able to connect now using ODBCDirect, using a DSN, but am having
trouble with queries with joins. I am using the evaluation driver to see
what I can extract from our converted database before we commit to upgrading
our software.

The sql I am "testing" at present is -
sSQL = "select ProductDay.ProductCode Product.[Description] From ProductDay
inner join Product on roductDay.ProductCode =
Product.ProductCode Where ProductDay.ProductCode Like '1%'"
(But would like to join upto 4 maybe 5 tables to produce the reports
currently generated from our paradox database)

This falls over with the error message "Run time error 3669. Execution
cancelled"
ProductDay has about 3.7 million records
Product has just short of 15,000 >>

This is an ODBC connection timeout issue with the Jet engine:

http://groups.google.com/group/microsoft.public.vb.database.odbc/browse_frm/thread/c2eeeec5f49cf783/e4220f454d6e3e20?lnk=st&q=Run+time+error+3669#e4220f454d6e3e20

I would try setting the QueryTimeout connection string setting to 0.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sorry Tim,

I have been into the registery and set both ..\microsoft\jet\3.5\engines\odbc and ..\microsoft\jet\4.0\engines\odbc QueryTimeout settings to 0 and
still receive the same error.

Any other ideas greatly appreciated.

Regards
Malcolm

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Malcolm,

<< I am able to connect now using ODBCDirect, using a DSN, but am having
trouble with queries with joins. I am using the evaluation driver to see
what I can extract from our converted database before we commit to upgrading
our software.

The sql I am "testing" at present is -
sSQL = "select ProductDay.ProductCode Product.[Description] From ProductDay
inner join Product on roductDay.ProductCode =
Product.ProductCode Where ProductDay.ProductCode Like '1%'"
(But would like to join upto 4 maybe 5 tables to produce the reports
currently generated from our paradox database)

This falls over with the error message "Run time error 3669. Execution
cancelled"
ProductDay has about 3.7 million records
Product has just short of 15,000 >>

This is an ODBC connection timeout issue with the Jet engine:

http://groups.google.com/group/microsoft.public.vb.database.odbc/browse_frm/thread/c2eeeec5f49cf783/e4220f454d6e3e20?
lnk=st&q=Run+time+error+3669#e4220f454d6e3e20

I would try setting the QueryTimeout connection string setting to 0.

--
Tim Young
Elevate Software
www.elevatesoft.com

Malcolm,

<< I have been into the registery and set both
...\microsoft\jet\3.5\engines\odbc and ..\microsoft\jet\4.0\engines\odbc
QueryTimeout settings to 0 and still receive the same error.

Any other ideas greatly appreciated. >>

What happens if you simply set it to a very high value ?  The poster in that
Google Groups post could be mistaken about the 0 value.

The following KB articles imply that the value must be greater than the
default for it to "take hold":

http://support.microsoft.com/kb/153756
http://support.microsoft.com/kb/208386

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

I did a bit of digging and asking questions on a couple of bullitin boards at this was the result, altering the registry only appears to work for Access.
What needs to be done is to set it in the program i.e.

qry.ODBCTimeout = 0
   Set rs = qry.OpenRecordset(dbOpenSnapshot)

Some thing for you to file for future use.

Many thanks for your continued support.

Regards
Malcolm
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Malcolm,

<< I have been into the registery and set both
...\microsoft\jet\3.5\engines\odbc and ..\microsoft\jet\4.0\engines\odbc
QueryTimeout settings to 0 and still receive the same error.

Any other ideas greatly appreciated. >>

What happens if you simply set it to a very high value ?  The poster in that
Google Groups post could be mistaken about the 0 value.

The following KB articles imply that the value must be greater than the
default for it to "take hold":

http://support.microsoft.com/kb/153756
http://support.microsoft.com/kb/208386

--
Tim Young
Elevate Software
www.elevatesoft.com

Malcolm,

<< I did a bit of digging and asking questions on a couple of bullitin
boards at this was the result, altering the registry only appears to work
for Access. What needs to be done is to set it in the program i.e.

qry.ODBCTimeout = 0
   Set rs = qry.OpenRecordset(dbOpenSnapshot)

Some thing for you to file for future use. >>

Cool, thanks for the tip.  I knew it had to be something minor like that.


--
Tim Young
Elevate Software
www.elevatesoft.com