Can I use adodb and sql queries to create recordsets from dbaseIV files?

I need to know if there is anyway to use query language on dbaseIV files using VB6 and adodb (or other data access).

For instance, I would like to be able to "Select Cmaster.CustName, Cmaster.Salesman, Orders.Order_Number, Orders.Order_Value from Orders inner join Cmaster on orders.Account = Cmaster.Account".

Cmaster and Orders are both dbaseIV files.    

I do have MSSQL Server 2008 if there is a way to do it through sql server.

Thanks for you input!
lthamesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
If you use ODBC driver for dBase IV (which is a part of many tools and it is most probably installed on your machine), then you may access dBase IV files as any other data source.

Another possibility is to define Linked server for dBase IV files on your SQL Server 2008 and then you may access dBase IV data same way as you access SQL tables.
0
lthamesAuthor Commented:
what would I use for a connection string with odbc drivers and dbaseIV?

This may seem like a silly question . . . . but will it use the existing indexes and if not, how will it connect data in relationships?
0
pcelbaCommented:
MDX indexes are problematic sometimes. Some drivers report errors and they require CDX indexes only. Data in relations are connected by their values if index does not exist. The only (big) problem is speed. I don't have dBase IV tables by hand to test it at the moment.

The Linked Server definition uses OLEDB provider to connect dBase data. I've tested it for Visual FoxPro tables only and it works both with and without indexes. Unfortunately it seems indexes are not used... I am still testing.

Examples of connection strings are e.g. here: http://www.connectionstrings.com/dbf-foxpro
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

pcelbaCommented:
Interesting, the OLEDB connection used by Linked server probably ignores indexes. To retrieve 150 records out of 1200000 takes about 2 minutes independently on index presence.
0
lthamesAuthor Commented:
I'm feeling pretty clueless here . . .but could you tell me what the name of the driver would be for the ODBC drivers?
0
lthamesAuthor Commented:
WOW . . . i'll test it on my data but I'm guessing this might not be a feasible direction!  
0
pcelbaCommented:
Driver name is e.g. Microsoft dBase Driver (*.dbf)
If you look at ODBC data sources on your machine then you'll probably see it.

FYI, I am trying to retrieve some data using above driver but I am still not successful... The connection is established correctly, tables are visible but SELECT commands return "External table is not in the expected format" error.

So, I'll stop testing for now...
0
BALMUKUND KESHAVCommented:
Here is a connection string which i am using to connect to dbase/fox dbf file in vb6

Sub ConnectToDbase()
  Set cnfact = CreateObject("ADODB.Connection")
  cnfact.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & path & ";" & _
                   "Extended Properties=""DBASE 5.0;"";"
  Set OpenDBFConn = cnfact
   
End Sub

If you have dabse iv dbf and if its not working in DBASE 5.0 the change it to DBASE IV  or DBASE 4.0 in above conn.string.
Ensure you don't have memo field in your dbf otherwise it will not work.

For dbf with memo field use this :
Sub connecttodbase()
  Set cnfact = CreateObject("ADODB.Connection")
  cnfact.Open "Driver={Microsoft Visual Foxpro Driver};Sourcetype=DBF;SourceDB=" & path & ";Exclusive=yes"
  Set OpenDBFConn = cnfact
End Sub

Bm Keshav

0
BALMUKUND KESHAVCommented:
Ithames :
what happend ??

Bm Keshav
0
lthamesAuthor Commented:
BMKESHAV, Sorry, I went on a trip.  I will test your suggestions today.
0
lthamesAuthor Commented:
BMKESHAV,

When I try to connect using the 5.0, I get the following error message:

run-time error 3001
Arguments are of the wrong type,are out of acceptable range, or are in conflict with one another.

When I try to change the 5.0 to 4.0 or use dbaseIV, I get the error message:
Could not find installable ISAM.

I am using the Microsoft ActiveX Data Objects 2.7 Library (msado27.tlb).  Is this correct?

Here is my code:

  Set cnfact = CreateObject("ADODB.Connection")
  cnfact.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=c:\olademo\data\shipping;" & _
                   "Extended Properties=""DBASE 4.0;"";"
  Set cnfact = CreateObject("ADODB.Connection")
  Dim myrs As ADODB.Recordset
  Set myrs = New ADODB.Recordset
  Dim mysql As String
  mysql = "select * from shipping"
  Dim mytest As String
  myrs.Open cnfact, mysql
  While Not myrs.EOF
    mytest = myrs!tracking
  Wend

Open in new window

0
pcelbaCommented:
Just FYI, the ODBC driver Microsoft dBase Driver (*.dbf) works for me already. It really needs correct DBF format (it must not be VFP table :-).
0
BALMUKUND KESHAVCommented:
@Ithames :
I am using  Microsoft ActiveX Data Objects 2.1 Library

Try to use this.

Bm keshav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lthamesAuthor Commented:
This worked!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.