Dazz

I have posted before, but not found anyone to help.
I will give it one more go with this one, before I top me self.
So if you don't want the untimley demise of a young and gifted programmer (admitedly, one that can't handle FoxPro databases) hanging over you, throw me line.

I'm connecting to a very large FoxPro2.6 database using the Data object in VB6. Although I can do what I want, it is a bit slow.
How can I connect using the indexes that have already been created in the FoxPro file tospeed up the searches etc.
The speed thing really is an issue as some of the files have over 200,000 records !

whelanp

Dazz I've meaning to get back you on this one.

Try the following, it may need some tweaking..

Sub CnnToFoxPro()

' set a ref to Microsoft ActiveX Data Object x.x Library

Dim dbCnn As ADODB.Connection
Dim rsTmp As ADODB.Recordset
Dim strCnn As String
Dim strSQL As String

Set dbCnn = New ADODB.Connection

strCnn = "Driver=Microsoft Visual Foxpro Driver; " & _
"UID=;SourceType=DBC;SourceDB=C:YourData.dbc"
dbCnn.Open strCnn

strSQL = "SELECT * FROM MyTable"

Set rsTmp = New ADODB.Recordset

Set rsTmp = dbCnn.Execute(strSQL)

' manipulate you rs here

Set rsTmp = Nothing

dbCnn.Close

Set dbCnn = Nothing


End Sub


The provider/driver will take care of your indexes,
however messing around with 200,000+ recs will never be really fast.

Dazz

Thanx so far Whelanp,
I've sort of got me head around your code, but I can't get the "Select * FROM MyTable" bit to work. When I execute it I get a runtime error [microsoft][ODBC Visual FocPro Driver]file 'Pstk.DBF' does not exist.

I have various columns in a file "A_CST.DBF", with names like 'Pstk', 'Desc', 'Qty' and 'Price' etc. the Foxpro database already has 14 indexes set up on it, and I can't access them.

Sorry to go on about this one, but can't work out why i'm struggling so much.

whelanp

Exactly how did this data originate?

I think you may need to use one of the dbase drivers from seeing the name of your datafile (*.DBF).

I'll post a connect string when I get a chance to look.

In the mean time I think you should look at using DSNs to connect to your data.

The exact connect string can be determined after you make your connection.

NoahBody

Think I'll confuse the issue with something else:

Are your tables free tables or do they have a wrapper??

If they have a wrapper, it will have to be in the same directory and the DSN will have to point to it.

Hope This Helps,
->Noah

Dazz

The database files form part of my companies manufacturing and accounts software. The software is by a very well known English software house 'Pegasus Business Software'.

The files are definately FoxPro2.6 as I have an old copy of FoxProW here, and although can't program, it properly I know enough to open the files and look at the data structures etc, although I was supprised to see the .DBF extension myself.

I can open and communicate with the files using a FoxPro2.6 connection through the Data object, so I know it works, but not having direct access to the indexes and having to populate the file before using it is causing me problems.

I appreciate all the help so far, but as stated when I started out on this one, I can hold my own but no nothing about database programming.

DSN, means nothing to me. When I create install programs using the deployment wizzard the programs will not install on virgin PC's. Ive'd tried everything - making sure that the DAO350.DLL in manually put in to the setup.lst etc.

Tell me honestly, is it time for me to buy a copy of Visual FoxPro ?

NoahBody

Ok,
I think I'm catching on...

"DSN" is short for Designated Name Server. You set these up in the Windows control panel under 'OBDC connections'.

So now I bet your asking yourself "Why do I want to do this??".

Using a DSN that points to the wrapper instead of the free tables will allow you to manipulate them as though you were in Visual FoxPro. I mean the keys, indexes, and all that will work.

Then you use the DSN for connecting: objConn.Open "DSN=MyDSN,UID=,PWD="

Then once you get the connection made, you can use the connection object's .ConnectionInfo property to get the actual connection string for the wrapper and get rid of the DSN. That's so you don't have to set up DSN's on all the host machines.

The other piece to this puzzle is to lose DAO. Switch to ADO. It is MUCH mo' betta. It is easier to use, easier to make connections, and more portable. Take ten minutes and read about ADO in the MSDN. Just do a search in the VB docs for "ADO".

Hope This Helps,
->Noah

whelanp

Dazz,

A DSN "IS" going to help you on this one.

Goto to Control Panel.

Double-Click on "ODBC Data Sources 32" (the 32 is important.)


Select the System DSN Tag

Click Add

Select Microsoft Visual FoxPro Driver
(Might want to try Dbase Driver if this does not work.)


Click Finish


Give it a name "MyFirstDSN"

Browse to location of your .dbf files

Check out Options (and any other "optional" bits)


Click on OK.


You have now set up a DSN.


Back to VB..


dim db as ADODB.Connection

db.ConnectString = "DSN=MyFirstDSN;UID=sa;PWD=pwd;"

db.open

This should allow you to fully use the database and indexes.

whelanp

DSN = "Data Source Name"

& Noah yer dead right about getting the proper connect string thus losing the need for a DSN.

Dazz

Thanx for all your help, I will try this after the weekend.
I'm working all this weekend, upgrading my Novell server, so won't see light of day.

I do like the sound of the DSN thing, so I will spend time on it.

Dazz

I have been messing around and found that the Data Form Wizzard was quite a bit of help.

I have set up me DSN (thanx guys) it is faster than before, but how do I do the 'get rid of your DSN' link bit.

My code is as follows.
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=MyFirstDSN;uid=;pwd=;"

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select son,pstk,ord_qty from a_sitem", db, adOpenStatic, adLockOptimistic

I have tried looking at the adoPrimaryRS.ActiveConnection but it reports the DSN. I can't extract the Connection String that you refere 2.

I hope you don't mind me being your personal pet project on this one.

Wait till I finish this, I got something else I want to do that is completley different!

whelanp

Set a break point in your code while your connection is active. Take a look at the ConnectString property.

Dazz

Sorry!
I've done that but as you can see it points to the DSN, no mention of the FoxPro link that I expected to find.
(worked out why I couldnt find it; had not dim 'WithEvents' the db, so no nice little box with properties in!)

Provider=MSDASQL.1;Connect Timeout=15;Extended Properties="DSN=MyFirstDSN;UID=;PWD=;SourceDB=g:OPERATNSDATA;SourceTyp e=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Delete d=Yes;";Locale Identifier=2057

I know the old Data(DAO) object is a bit slow on population but atleast you only have to do it at the start of the program, and right now it is looking like a safe bet.

(I just hate it when I load FoxPro2.6, and can open and access all record immediateley but have to wait upto three minutes in VB6.0)