?
Solved

Accessing and Updating Dbase IV files using ADO with Visual Basic

Posted on 2003-03-30
3
Medium Priority
?
1,151 Views
Last Modified: 2013-11-24
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

EstablishConnection_Error:
 Dim sError As String
 
 For Each objError In objConn.Errors
   strError = strError & objError.Number & " : " & _
   objError.Description & vbCrLf & vbCrLf
 Next
 
 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.
0
Comment
Question by:badge0913
[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
  • 2
3 Comments
 

Accepted Solution

by:
MaxSterling earned 340 total points
ID: 8243097
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
0
 

Author Comment

by:badge0913
ID: 8293000
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.
0
 

Expert Comment

by:MaxSterling
ID: 8303848
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]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO








CREATE PROCEDURE sp_RefreshViews

 AS

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
Begin
     Select @UpdateSql = 'sp_refreshview @viewname = ' + "'" + @ViewName + "'"
     print @UpdateSql
     Print ''

     exec(@UpdateSql)

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

End

close cur_Views
deallocate cur_Views







GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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