badge0913
asked on
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=27
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.
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=27
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_Refr
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
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_Refr
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
ASKER