Accessing and Updating Dbase IV files using ADO with Visual Basic

I'm currently working on a project that is involved in upgrading a client's old software package from DBase IV to SQL server 2000. I'm using Visual Basic 6 and ADO to communicate with SQL Server. As I'm developing new functionality I still need to communicate with the DBase IV files. I read on several sites that you can use ADO to connect to the DBase IV files. Here's the code I'm using to establish a connection:

Public objConn As ADODB.Connection
Public objError As ADODB.Error

Public Sub EstablishConnection ()
Dim sConnectString As String

On Error Goto EstablishConnection_Error

Set objConn = New ADODB.Connection

sConnectString = "Driver={Microsoft dBase Driver(*.dbf)};DriverID=277;Dbq=c:\dx\dbfs;"

objConn.Open sConnectString

 Dim sError As String
 For Each objError In objConn.Errors
   strError = strError & objError.Number & " : " & _
   objError.Description & vbCrLf & vbCrLf
 MsgBox strError, vbCritical + vbOKOnly, "Login Error"
End Sub ' END Sub -- EstablishConnection

The error that's happening: When it executes the .Open method it jumps down to the Error block and displays the following error: "SQLSetConnectAttr failed"

I believe I'm using the correct connection string because I read it on several different sites. The path in the connection string is valid. The ODBC driver is loaded. I downloaded the lastest MDAC library which I believe is 2.7. I have the latest BDE (Borland Database Engine) installed because I know UPDATE routines need the BDE installed for MDAC 2.1 and later. I'm running Windows XP Home Edition, haven't tried this on any other machine yet. Let's see what else, I've also tried different variances of the connection string as well as trying a DSN connection and nothing works. I'm at a point where I don't know what else to try. Been thinking about getting the Codebase Library to communicate with the DBase files, but I don't want to give up on ADO just yet. Especially when I'm reading everywhere that it works with Dbase. I just must be missing something. Any help or suggestions will allow me to keep my sanity. Thanks in advance.
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.

Why don't you try linking to the dbase files?  I did this for another project and it worked great!  Check the books online for sp_addlinkedserver for more information (they explain it far better than I could).  You can create the linked server right through enterprise manager if you want (right click in security I think and choose add linked server).  Or you can also create a DSN to the dbase files and connect to that pretty easily.

Oh, once you have the linked server (let's say you named it DBASE), your query would look something like:

select *
from openquery(DBASE, 'Select * from table1, table 2 where
table2.field1 = table1.field1')

Hope that helps

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
badge0913Author Commented:
You're the man!! It works great! I created a linked server through Enterprise Manager, used the OpenQuery method and accessed the data with no problem. Haven't tried doing updates or inserts yet but I'm sure that will work. Thanks again.
Glad that it worked for you!  I've used linked servers with great results for several applications that I've built.  You can usually update data too (in addition to reading it) on the linked sources.

Something that I came across recently though that you may encounter...  Sometimes my views to the linked data sources were not showing recent data that was added (in my case, a DBASE 4 database).

My solution to this was to create a very simple new stored procedure to go through and refresh all of the views, as shown below.  I then created a new job in SQL Server Agent to run this sp periodically (the client didn't want it to be run on app startup for some bizarre reason).  

Anyway, all I had to do was script this sp as well as the Agent job that I created and send that to them - problem solved.

Take it easy

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RefreshViews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RefreshViews]




declare @ViewName varchar(200), @UpdateSql varchar(1000)

declare cur_Views cursor for

Select distinct name
from sysobjects
where xtype = 'V'
and name like 'vw%'
order by name

open cur_Views

fetch next from cur_Views into @ViewName

while @@fetch_status <> -1
     Select @UpdateSql = 'sp_refreshview @viewname = ' + "'" + @ViewName + "'"
     print @UpdateSql
     Print ''


     set @ViewName = null
     Set @UpdateSql = null
     fetch next from cur_Views into @ViewName


close cur_Views
deallocate cur_Views


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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.