OLEDB connection

[pl456]

OK, I am not the best when it comes to code. I have been struggling with ODBC connections for FoxPro databases and have been forced down the route of OLEDB drivers instead.
I think I have connected with the database (the code runs) but how do I retrieve the data I want?
The only way I know is to have linked tables in order to be able set a query up and pass an sql string to it, but the only way I can link the tables is by ODBC.
This is the OLEDB connection code to link (which I found on another site), I was planning on putting an select statement in but how without working on the linked tables.

Dim objCon As ADODB.Connection
Dim objCom As ADODB.Command


Set objCon = New ADODB.Connection
Set objCom = New ADODB.Command


objCon.ConnectionString = "Provider=VFPOLEDB.1;Data Source=H:\myfoxprodatabase.dbc;Collating Sequence=MACHINE"
objCon.Open objCon.ConnectionString

 

[ecawilkinson]

you may find this link interesting, especially the middle bit about VBA applications
HTH,
Chris

 

[pl456]

Thanks for the link, this is my code now (or my best stab at it) however it is no quicker then ODBC linked tables. Any idea why? Something I am doing wrong with code or is the fact I am connecting to a .dbc rather than free tables??


Private Sub Form_Load()

Dim objCon As ADODB.Connection
Dim objRS As ADODB.Recordset

Set objCon = New ADODB.Connection

objCon.ConnectionString = "Provider=VFPOLEDB.1;Data Source=L:\mydatabase.dbc;Collating Sequence=MACHINE"
objCon.Open objCon.ConnectionString

Set objRS = New ADODB.Recordset

With objRS
Set .ActiveConnection = objCon
.source = "SELECT * FROM mytable"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Set Me.Recordset = objRS


End Sub

 

[ecawilkinson]

hi,

the fact that it is a bit slow is probably because the database is on a network drive. How fast is the network normally? There is nothing wrong with your code. To speed things up you could make objCon as public variable and put the following code in a module and have OpenDB called by the autoexec macro.:

Public objcon as ADODB.Connection

function OpenDB()
Set objCon = New ADODB.Connection

objCon.ConnectionString = "Provider=VFPOLEDB.1;Data Source=L:\mydatabase.dbc;Collating Sequence=MACHINE"
objCon.Open objCon.ConnectionString
end function

At least this way, the connection to the FoxPro Db is opened only once when you open the AccessDB. Just make sure that before you close the db that the connection is closed.

HTH,
Chris

 

[pl456]

The network speeds are fine. I shall give your suggestion a go. Thanks

 

[ecawilkinson]

If you are not sure how to run code when the application closes, you need to create a blank form and put the connection closing code in the unload event, then open this form in the hidden state when the application opens. When the db closes, the hidden form will be unloaded, thus executing the code.

HTH,
Chris