Two ODBC questions

[Beverly Howard]

Thanks to everyone's help, I have a dbc setup to connect to a test copy
of an access mdb file and the code is working to select data as needed.


During my reading on this subject I found that VFP contains odbc
drivers, where, with FPDos it was necessary to set up windows odbc
drivers in order to access foreign data... is this still necessary or
are the VFP odbc drivers all that is needed to establish data connections?


Since I have been learning and coding using a small test mdb file, I now
need to move the dbc file I have setup and modified to the client's
location to point to the final data source.

What is the best way to proceed... I found vfp "dbc editors" such as
http://www.fyxm.net/foxpro-dbc-editor-14275.html plus I opened the dbc
file only to find that the file location information is encoded and
wasn't able to decode it... i.e. unicode, etc.

I have made a number of field type changes (memo to 254 character) plus
the client does not (yet?) have vfp installed so creating and setting up
a new dbc file is problematic since I am working remotely.


Thanks in advance for any pointers,
Beverly Howard

[Beverly Howard]

Withdraw the questions for the moment... going back into the windows
odbc setup is offering some hints.

Beverly Howard

[Dan Freeman]

Kind of a weighty question. <s>

First, VFP does not "contain" ODBC drivers. It ships with the *VFP*
ODBC driver, but that is for other applications to access VFP data
using ODBC. Nothing whatsoever to do with going the other direction.

At its simplest, you should create a Connection in the ODBC Control
Panel (in the Windows Control Panel) on the client workstation. That
connection points to the physical location of the Access MDB. The
client needs the *Access* ODBC driver, not the VFP ODBC driver.

As long as the Connection has the same name as the one you used in
development, it doesn't matter where it points for data. That's what
Connections are for -- your application needn't know or care where the
data resides physically. Let the Connection resolve that.

The Connection becomes a "name" out in the ooze, and VFP uses whatever
it finds in that "name".

There are many ways to add on to that simplistic approach. You can
actually create "connectionless" connections, etc., and there are many
and varied reasons to do so. It doesn't sound like you've hit them.

Dan

Thanks to everyone's help, I have a dbc setup to connect to a test copy of an
access mdb file and the code is working to select data as needed.
During my reading on this subject I found that VFP contains odbc drivers,
where, with FPDos it was necessary to set up windows odbc drivers in order to
access foreign data... is this still necessary or are the VFP odbc drivers
all that is needed to establish data connections?
Since I have been learning and coding using a small test mdb file, I now need
to move the dbc file I have setup and modified to the client's location to
point to the final data source.
What is the best way to proceed... I found vfp "dbc editors" such as
http://www.fyxm.net/foxpro-dbc-editor-14275.html plus I opened the dbc file
only to find that the file location information is encoded and wasn't able to
decode it... i.e. unicode, etc.
I have made a number of field type changes (memo to 254 character) plus the
client does not (yet?) have vfp installed so creating and setting up a new
dbc file is problematic since I am working remotely.
Thanks in advance for any pointers,
Beverly Howard

[Beverly Howard]

Post by Dan Freeman
As long as the Connection has the same name as the one you used in

development, it doesn't matter where it points for data. That's what
Connections are for -- your application needn't know or care where the
data resides physically. Let the Connection resolve that.

The Connection becomes a "name" out in the ooze, and VFP uses whatever
it finds in that "name". <<

That's _exactly_ the piece I was looking for... it crystallized the
answers that I needed... thanks!

Post by Dan Freeman
vfp odbc drivers <<

It's obvious I got the direction reversed when I was originally trying
to get back up to speed on odbc searching for access odbc driver info
and thought that I was installing access drivers for vfp.

Thanks... things are beginning to fall together in phase two of this
project... just finished a property photo import and renamer today as well.

Beverly Howard

[Dan Freeman]

That's _exactly_ the piece I was looking for... it crystallized the answers
that I needed... thanks!

You're very welcome! Glad I could help!

Dan

[tom knauf]

Hello,


we read from an MDB like that :

cMdb = cPath + "lohndaten.MDB"
cdbf = cPath + "lohndaten.DBF"

cconn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" + cMdb +
";Exclusive=1;Uid=admin;Pwd="
nConn=Sqlstringconnect(cconn)

If nConn>0
=SQLExec(nConn,'Select * from tablename_in_mdb','dummy2')
Select dummy2
Copy To (cdbf)
SQLDisconnect(nConn)
Select dummy2
Use
Else
Messagebox("No Connection")
Endif
Endif
Next

HTH
Tom

[Beverly Howard]

Thanks Tom... excellent example.

Since you seem to be well versed with access files, a related question;

I have things working, but this is the first time I've worked with an
access database (I have worked with Oracle & SQLServer databases in the
past)

The access mdb file that I need to reference (not mine and not in my
control) is about 800mb and contains about 350,000 records with around
200 fields where most of the character fields are 255 in length.

I need access to 23 of the fields and have built a table in the dbc
accordingly, reducing the field sizes to what I need.

As I sorta expected, a query for a single record in this mdb across a
100mb unloaded network takes several minutes to execute... about 45
seconds if the mdb is in a local usb external drive.

Is there any way to optimize this type of connection for faster queries?
I _think_ the "where" field is an indexed field in the database but
don't know how to verify that or set it in the dbc.

Thanks in advance,
Beverly Howard

[tom knauf]

Dear Beverly,

there are 2 strategys :

1) one time access
If possible, just get all records into a foxpro cursor (see my example), but
size may not exceed 2 GB !
In rv we set fetch all to true to get all record at once, it takes time, but
only once
Then index the cursor and do you querys there = MUCH, MUCH faster

2) real time access
Setup a dbc, build a rv (or use CA) with a where clause and maybe set order
Now the ODBC driver does all possible optimizations according to your query
(thats why normally a sql-server source performs better then access)
For update the resulting cursor (the rv) see requery and use it when a
filter changes or data may have changed.

HTH
Tom

Post by Beverly Howard
Thanks Tom... excellent example.
Since you seem to be well versed with access files, a related question;
I have things working, but this is the first time I've worked with an
access database (I have worked with Oracle & SQLServer databases in the
past)
The access mdb file that I need to reference (not mine and not in my
control) is about 800mb and contains about 350,000 records with around 200
fields where most of the character fields are 255 in length.
I need access to 23 of the fields and have built a table in the dbc
accordingly, reducing the field sizes to what I need.
As I sorta expected, a query for a single record in this mdb across a
100mb unloaded network takes several minutes to execute... about 45
seconds if the mdb is in a local usb external drive.
Is there any way to optimize this type of connection for faster queries? I
_think_ the "where" field is an indexed field in the database but don't
know how to verify that or set it in the dbc.
Thanks in advance,
Beverly Howard

[Beverly Howard]

Thanks.

For the moment, I'm going with the former since the data is static, but
glad to learn about the second option.

(or use CA) <<

Understand "rv" but would you clarify "CA" ...that went over my head.

Beverly Howard

[Dan Freeman]

Post by Beverly Howard
Understand "rv" but would you clarify "CA" ...that went over my head.

CursorAdapter. New baseclass in VFP8 and later.

Dan

[Beverly Howard]

CursorAdapter. New baseclass in VFP8 and later. <<

Thanks,
Beverly Howard