ODBC persistent link

[mightycpa]

Hi,

I've looked at the ODBC via VB script on the page, and I'm going to try it out, but I already see one need for change.

The subroutine CONNECTS, QUERIES, CLOSES ... In my application, I'm going to do something like this:

BEGIN DBRECORD LOOP

Query DB
Select
Pass value back to MS

BEGIN DBCOLUMN LOOP
Do stuff to record
Put it in another application
NEXT DBCOLUMN

NEXT DBRECORD

So, rather than re-connect each time I need a new record, ideally, the connection would occur outside of the BEGIN DBRECORD LOOP, and would persist throughout the execution of the macro. I expect the # of records to be about 4000, and the number of columns to be about 15 per record.

Can it be done?

Is this as simple as taking the connection part and placing it outside of the loop (close too?)

Should I care which way I get the job done?

Tx,

George

[mightycpa]

For clarity,

BEGIN DBRECORD LOOP

--Query DB
--Select
--Pass value back to MS

--BEGIN DBCOLUMN LOOP

----Do stuff to record
----Put it in another application

--NEXT DBCOLUMN

NEXT DBRECORD

[Bob Hansen]

When passing values back to MS, maybe write them to an INI file using VB commands, and then have Macro Scheduler read the INI file contents for processing after all VB processing is done?

[support]

Can it be done?

Is this as simple as taking the connection part and placing it outside of the loop (close too?)

Yes, like you say, put the connection part into it's own Sub, the disconnection into another sub. Put the loop in another Sub. Any variables that they all need would need to be global, so Dim them above the first Sub.

VBSTART
Dim RecordsetA
Dim SomeVar

Sub ConnectProc
bla bla bla
Set RecodsetA ....
End

Sub ReadRecord
bla bla bla
End

Sub DisconnectProc
bla bla bla
End

VBEND

VBRun>ConnectProc
//Either loop here, or have the loop all in ReadRecord .. whatever you want
VBRun>ReadRecord
//Do something else then disconnect at end
VBRun>DisconnectProc

[mightycpa]

Thanks both!