Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Accessing and Updating Dbase IV files using ADO with Visual Basic

Posted on 2003-03-30
3
Medium Priority
?
1,169 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
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

578 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