Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 To: ian pano
  Where is ian pano?
 Metro Manila
 Philippines
 ian pano
 Tags
Subject: RE: ADODB - dbc
Thread ID: 112608 Message ID: 112775 # Views: 3 # Ratings: 0
Version: Visual FoxPro 8 Category: ODBC, ADO and OLEDB
Date: Monday, November 13, 2006 6:10:31 AM         
   


> > > > > hello experts,
> > > > >
> > > > > how do i open a dbf in a database using ADO?
> > > > > i have tried simon arnold's sample using .mdb. it works just fine. here's the code:
> > > > >
> > > > >
> > > > >
> > > > > loCN = CREATEOBJECT([ADODB.Connection])
> > > > > loRS = CREATEOBJECT([ADODB.RecordSet])
> > > > > 
> > > > > loCN.ConnectionString =;
> > > > >  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\Test.mdb"
> > > > > 
> > > > > loCN.Open()
> > > > > 
> > > > > IF loCN.State = 1	&& If Connection is fine, create RecordSet
> > > > > 	loRS.ActiveConnection = loCN
> > > > > 	loRS.CursorType = 1 &&adOpenKeySet				
> > > > > 	loRS.LockType   = 3 &&adLockOptimistic	
> > > > > 	loRS.Open("Select * From Customers")
> > > > > 
> > > > > 	?loRS.RecordCount	&& Display Number of Records
> > > > > 	loRS.MoveFirst		&& Start At the Top of the File
> > > > > ENDIF
> > > > > 
> > > > > ** I have also tried changing the connection string 
> > > > > ** to connect to mySQL && database. also worked fine..
> > > > > ** but when i tried to connect to a dbc using && the code below,
> > > > > ** i get the error :
> > > > > 
> > > > > ** OLE IDispatch exception code from 0 from Microsoft
> > > > > ** OLE DB Provider for ODBC Drivers: ODBC driver does not support
> > > > > ** the requested properties....
> > > > > 
> > > > > 
> > > > > &&this is the code that generates the error
> > > > > 
> > > > > oCN = CREATEOBJECT([AdoDB.Connection])
> > > > > oRS = CREATEOBJECT([AdoDB.RecordSet])
> > > > > 
> > > > > oCN.ConnectionString = ;
> > > > > "SourceType=DBF;SourceDB=C:\data1.DBC;Driver={Microsoft Visual FoxPro Driver};"
> > > > > 
> > > > > oCN.open()
> > > > > 
> > > > > IF oCN.State = 1 THEN
> > > > > 
> > > > > 	oRS.ActiveConnection = oCN
> > > > > 	oRS.CursorType = 1 &&adOpenKeySet				
> > > > > 	oRS.LockType   = 3 &&adLockOptimistic	
> > > > > 	oRS.Open("select * from test") && <<--- debugger says this line causes the error.
> > > > > 	
> > > > > 	DO WHILE NOT oRS.EOF
> > > > > 		FOR lnI = 1 TO oRs.Fields.Count
> > > > > 			IF lnI = 1
> > > > > 				? oRS.Fields(lnI-1).Value
> > > > > 			ELSE
> > > > > 				?oRS.Fields(lnI-1).Value	
> > > > > 			ENDIF
> > > > > 		ENDFOR
> > > > > 		oRS.MoveNext()		
> > > > > 	ENDDO 
> > > > > 
> > > > > ENDIF
> > > > > 

> > > > >
> > > > > TIA,
> > > > >
> > > > > ian pano

> > > >
> > > > Christian,
> > > >
> > > > I am playing with this myself. The easiest way to handle ADO recordsets is to simply use a CA. It handles everything for you.
> > > >
> > > > Ken
> > > > You shall know the truth - and the truth shall set you free. (John 8:33)

> > >
> > >
> > > thanks ken. i want to create a dll that i can use to connect to and manipulate databases. like:
> > >
> > > loDbase = CREATEOBJECT([myConDLL.gConnect])
> > > loDbase.ConnectionString = [<connection string here>]
> > > loDbase.OpenDbase()
> > > loDbase.OpenTable([myTable])
> > > loDbase.CreateRecordSet([Select * from myTable])
> > > loDbase.InsertRecords([Field1=value1, Field2=value2])
> > > loDbase.Delete()
> > > 

> > > etc....
> > >
> > > the dll will also then be used when writing application using other language like vb.
> > >
> > > any ideas?
> > >
> > > Thanks,
> > >
> > > .ian.pano.

> >
> > I started playing with doing something like that a while ago but I didn't get too far before other priorities got in the way. This would be a very good subject for an article if you get it to work. Please consider writing one.
> >
> > When I started playing with this, I created a connection object that included a .cConnectionString property and a .nConnection property. I use a custom .Connect() method to populate the .nConnection property. To connect, you will need to know the type of backend, the database name, and user credentials. I made properties to hold this info, but if I ever get back to it, I will probably create a table for these.
> >
> > Ken
> > You shall know the truth - and the truth shall set you free. (John 8:33)

>
> thanks ken. an idea of another colleague is that the class must be capable of connecting to different data sources simultaneously, like connecting to Access table while already connected to mySQL and/or probably another data source which also means that there can be multiple recordset to be handled. i need your expert advice on this. and also i'd like to know how far you have gone when you planned to do something like this, your thoughts back then about the would be benefits and cumbersome phases of the development.
>
> thanks a lot.
>
> .ian.pano.


Christian,

I was only contemplating dealing with various SQL and VFP backends, but the concept behind using ADO is that it allows you to access data from any source (including non RDB sources.) Your colleague may have an idea there.

The primary problem here is SELECT syntax. Different backends will require different SELECT syntax, so you are going to have to take that into account. I haven't played with accessing data from an MDB (I have not had the need yet) so I am unsure of the syntax issues there. One thought might be to create a series of classes (rather than a single class) to handle the differences. I was looking at a table based concept (similar to what I use for SPT) but ran out of disposable time before I got into it.

Ken
You shall know the truth - and the truth shall set you free. (John 8:33)

ENTIRE THREAD

ADODB - dbc Posted by Christian Pano @ 11/10/2006 5:01:47 AM
RE: ADODB - dbc Posted by Martin Jindra @ 11/10/2006 9:31:35 AM
RE: ADODB - dbc Posted by Christian Pano @ 11/11/2006 5:27:30 AM
RE: ADODB - dbc Posted by Martin Jindra @ 11/13/2006 10:32:57 AM
RE: ADODB - dbc Posted by Ken Murphy @ 11/10/2006 11:53:49 AM
RE: ADODB - dbc Posted by Christian Pano @ 11/11/2006 5:43:19 AM
RE: ADODB - dbc Posted by Ken Murphy @ 11/11/2006 3:52:27 PM
RE: ADODB - dbc Posted by Christian Pano @ 11/13/2006 5:03:32 AM
RE: ADODB - dbc Posted by Ken Murphy @ 11/13/2006 6:10:31 AM
RE: ADODB - dbc Posted by Michael Kopljan @ 11/11/2006 10:20:06 AM