Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ODBC, VB, [Data Control | ADO Data Control] -- I need to connect and release when requested.

Posted on 2004-08-11
7
Medium Priority
?
1,024 Views
Last Modified: 2013-12-25
My data control hogs up access to the FoxPro database (through ODBC) and prevents my program from Zapping the records.

I am making an ActiveX Calendar Control.

Here is the order of operations

User clicks the Tab in FoxPro.
FoxPro USE EXCLUSIVE's the Calendar table, Clears it. Loads it with real data. Then tells the calendar to REFRESH based on the new data.

Everything works, except i get an error telling me that fox cant use the database exclusive because the control has a finger in it.

How can i (programically) create the connection?

I'm using a Data Control (vb6) and have been told to switch to ADO data control.

I DO NOT WANT TO SET THE CONNECTION THROUGH THE PROPERTY WINDOW BECAUSE I NEED TO START AND STOP IT VIA CODE.

Cheers, It's important : ) So answer quickly
0
Comment
Question by:Anderson_Enterprises
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 6

Expert Comment

by:PePi
ID: 11775115
I am assuming you are using VB6.

Dim oConn as New ADODB.Connection

'the code below opens a connection to your foxpro database
'
oConn.Open "Provider=vfpoledb;" & _
           "Data Source=C:\vfp8\Samples\Data\myVFPDB.dbc;" & _
           "Mode=ReadWrite|Share Deny None;" & _
           "Collating Sequence=MACHINE;" & _
           "Password=''"

The Visual FoxPro OLE DB Provider is not installed by MDAC 2.x.  You must install Visual FoxPro or download the OLE DB Provider

You also have to add Microsoft ActiveX Data Objects 2.6 Library to your References

0
 

Author Comment

by:Anderson_Enterprises
ID: 11775234
How do i get to the fields?

Data1.RecordSet.Fields("Age")

was the old way.
0
 

Author Comment

by:Anderson_Enterprises
ID: 11775357
I prefer to use an ODBC connection. i already have one made that worked previously..

How do i start an ODBC connection via code?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Accepted Solution

by:
PePi earned 2000 total points
ID: 11775372
you have to create an ADO recordset object

Dim rsRec as New ADODB.Recordset

'get your recordset
'
rsRec.Open "SELECT * FROM Employee", oConn, adOpenDynamic, adLockPessimistic

'this will loop thru the recordset if you have 1 or more records
'
While Not rsRec.EOF
    'to access the fields, you can do either by:
    '
    rsRec.Fields("Age")

    'or
    '
    rsRec!Age
Wend

P.S. After processing everyting, don't forget to close you ADO objects nad destroying them
oConn.Close
rsRec.Close
Set oConn = Nothing
Set rsRec = Nothing


have fun
0
 
LVL 6

Expert Comment

by:PePi
ID: 11775482
you mean your ODBC DSN has already been setup? if so, all you have to do is:

assuming you called your DSN connection as dsnFoxPro

Dim oConn as New ADODB.Connection

oConn.Open dsnFoxPro
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11776020
Here is a good ADO tutorial to start with:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=12050

Leon
0
 

Author Comment

by:Anderson_Enterprises
ID: 11777233
So that everyone (for future reference) knows how i got it to work:

REFERENCE: Microsoft ActiveX Data Objects 2.6

Dim rsConn as New ADODB.Connection
Dim rsRec as New ADODB.Recordset

rsConn.Open "Terms_Inventory" ' <--- That's my ODBC connection in the control panel
rsRec.Open "SELECT * FROM Inventory", rsConn, adOpenDynamic, adLockPessimistic

While Not rsRec.EOF
    MsgBox(rsRec.Fields("Date"))
    rsRec.MoveNext
Wend

rsConn.Close
rsRec.Close
Set rsConn = Nothing
Set rsRec = Nothing
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question