• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1318
  • Last Modified:

Updating DBF with NDX from Visual Basic

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
xnl28
Asked:
xnl28
1 Solution
 
CarlWarnerCommented:
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
 
CarlWarnerCommented:
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
 
xnl28Author Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CarlWarnerCommented:
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
 
xnl28Author Commented:
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
 
xnl28Author Commented:
Can I assume from the lack of response that what I ask is impossible.
0
 
walterecookCommented:
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
 
PashaModCommented:
PAQed, with points refunded (50)

PashaMod
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now