>Umm, why on earth would you want to do CREATE CONN via the provider?

Mike, you're not easily confused, I confuse people easily. :)

Anyway, what I am doing in my (little) project is doing some middle-tier
stuff entirely in VBScript (that's what this job calls for). I can't do a
VFP COM object in this case, so I have a lot of restraints to work
with. One way I thought I could get around the constraints is to "use VFP without using VFP" by using the
VFP7 OLEDB provider.

I am accessing a lot of databases from all over the country, and
putting the data into a VFP DBC (the data gets refreshed once a day
and having it in a local DBC means the actual web app doesn't have
to go all over the place to get the data). I wanted to originally
command the OLEDB provider to create an ODBC connection and
then execute a remote view on the MSSQL tables and then save them
to DBFs. This would have given me a quick-n-easy way to get
properly typed data in my DBF cache tables. But I realize that the
commands I want to use (Create Connection, Create SQL View)
are not supported by the VFP7 OLEDB provider.

Soooo....

What I did was attach to the remote MSSQL host, use the ADO GetRows()
method to pull everything into an array, then put "VFP
delimiters" around array columns depending on the
destination data type. So I have this good sized VBScript
Select Case statement which says if it's varchar/nvchar/char,
then the destination VFP type is c and put Chr(34) in front and Chr(34)
in back, if it's datetime then put "{" in front
and "}" in back. Also check for Null values
in the array.


When the array is pre-processed for VFP, I drop the existing VFP
table (with Recycle to keep it around just in case, I love
that feature), create it again, and then build my insert
statement:

" Insert into " & cDestTb & "(" & 
cColList & ") Values (" & arColDelim(0,0) & arResult(
0 , iSeeRow ) & arColDelim(1,0) & "," & arColDelim(0,1) 
& arResult( 1 , iSeeRow ) & arColDelim(1,1) & "," & 
arColDelim(0,2) & arResult( 2 , iSeeRow ) & arColDelim(1,2) 
& "," & arColDelim(0,3) & arResult( 3 , iSeeRow ) & 
arColDelim(1,3) & "," & arColDelim(0,4) & 
arResult( 4 , iSeeRow ) & arColDelim(1,4) & "," 
& arColDelim(0,5) & arResult( 5 , iSeeRow ) & arColDelim(1,5) & "," & arColDelim(0,6) & arResult( 6 , iSeeRow ) 
& arColDelim(1,6) & "," & arColDelim(0,7) & arResult( 
7 , iSeeRow ) & arColDelim(1,7) & ")"

and poof my DBF is now populated. So I'm OK now. Well, OK is relative
since this insert statement is a mess (will have to do some
recursive stuph later when the prototype deadline is passed).

The one last thing I have to ask about on this subject is how I
can create indexes using only what the OLEDB provider provides.
I see that "index on" is in the unsupported list.
:) I use the OLEDB provider in the actual application to query
the tables and need all the speed I can get.

Thanks for reading this far!"