?
Solved

Updating DBF with NDX from Visual Basic

Posted on 2003-03-10
8
Medium Priority
?
1,280 Views
Last Modified: 2013-11-24
Hello,

We want to update records in a dBase III DBF file that has associated NDX indices. Does anyone there know how this can be accomplised?

We are currently accessing the DBF files using ADODB and a connection string to read the files successfully:

   DRIVER=Microsoft FoxPro VFP Driver (*.dbf); UID=; Deleted=No; Null=Yes; Collate=Machine; BackgroundFetch=Yes; Exclusive=No; SourceType=DBF;SourceDB=path

But when we update every field in every row of the DBF, the associated NDX file is not modified at all - which is NOT good at all!  Is there some way to specify the NDX index files in the connection string?

I have read about creating an .INF file to specify the NDX index files, but surely there is another way to do this. Are there any dBase III components for Visual Basic that would handle this?

Any advice appreciated,

 

-Steve
0
Comment
Question by:xnl28
[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
8 Comments
 
LVL 33

Expert Comment

by:CarlWarner
ID: 8104449
The Visual FoxPro driver is not a dBase driver.  The VFP one does not support .NDX index files, but rather than the newer .IDX and .CDX ones.  You need a dBase ODBC driver to get it to support NDX index files.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 8104469
I checked my Data Sources (ODBC) on my W2K PC and there is a Microsoft dBase driver already loaded for me.  You should have it too if you got the MDAC stuff from MS.

http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860
0
 

Author Comment

by:xnl28
ID: 8110044
Carl,

I also have the Microsoft dBase driver in the ODBC Data Sources. The problems are: 1) creating a connection string to connect to the DBF using ODBC; and 2) getting the NDX files recognised.

I used the 32bit ODBC Data Sources Administrator to create a File DSN pointing to dBase III files. Then I opened the .DSN in Notepad and used the text for a connection string, adding semi-colons after each line and removing the [ODBC] line at the beginning. The connection string I ended up with is this:

"DRIVER={Microsoft dBase Driver (*.dbf)};UID = admin;UserCommitSync = Yes;Threads = 3;Statistics = 0;SafeTransactions = 0;PageTimeout = 600;MaxScanRows = 8;MaxBufferSize = 2048;FIL=dBase III;DriverId = 21;Deleted = 1;DefaultDir=D:\ABC;DBQ=D:\ABC;CollatingSequence=ASCII"

I use this in Visual Basic to open an ADODB.Connection, but get an error straight away.

  Dim oConn As New ADODB.Connection
  oConn.Open StringAsAbove

The oConn.Open gives this error: "Run-time error '-2147418113 (8000ffff)':[Microsift][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"

So I must be getting the connection string wrong. I tried a different connection string, shown below:
"Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277;Dbq=d:\abc"

Using this connection string allows me to open the DBF and update records, but the NDX files are not updated. So I need to specify the NDX files by creating an INF file. For exmple, CUSTOMER.INF

[dBase III]
NDX1=customer.ndx

Now when I update records in the CUSTOMER.DBF, the NDX file is also updated. But surely there is some way to specify the NDX files in the connection string? Or will I have to create INF files for every DBF in every folder (there are thousands)?

Regards,  Steve
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 33

Expert Comment

by:CarlWarner
ID: 8112284
Your first example of connecting showed Visual FoxPro.  That didn't work because it's not supposed to work.

I have never seen the index file stuff setup in an ODBC string for access to a .dbf file.  A .dbf is such a widely used open format that the index stuff is also well known and handled by the ODBC driver.  In fact, I am just guessing here, but because the .ndx extension index type is the default index extension for older (pre-dBase4/5) index files, probably nothing needs to be specified.  The only time I've seen setups where you had to state the extension in a file was when you were going against the default, especially when Clipper was using special index file extensions to work with dBase files.  Anyhow, you say this is a dBase III file.  The default extension of NDX applies to it because at that time, many moons ago, there was no alternative.

I am not sure where to tell you to go from here.  
All I can do is tell you to look for the obvious, like is that NDX file set to R/O?

Sorry I can't be of more help at this end.
0
 

Author Comment

by:xnl28
ID: 8119510
Thanks for your input, Carl.

The NDX file is writable. I have seen the NDX file update after I insert a new row into the DBF file when I have the index specified in an INF file.

Using an INF file to specify the indicies is what I am trying to avoid, because I would have to create them for every DBF in every directory I intend to update, and there are thousands.

Does anyone else out there know if it is possbile (or not) to specify the NDX to use for a DBF within the connection string?

-Steve
0
 

Author Comment

by:xnl28
ID: 8211885
Can I assume from the lack of response that what I ask is impossible.
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10337148
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 10364743
PAQed, with points refunded (50)

PashaMod
Community Support Moderator
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

764 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