Records Posted by ADODB Not Showing in Lotus Approach

Using Visual Basic and ADODB my program adds records to a '.dbf' database file used by Approach. When I run an Approach View file, '.apr', the records posted using ADODB don't appear to be in the related .dbf file. However if I open just the .dbf file in Approach those new records are there. Here's the connection string:
    Set cnDBf = New ADODB.Connection
    With cnDBf
    .ConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};Dbq=\\Amcserver3\Job Cost\"
    .CursorLocation = adUseClient
    .Open
    End With
Here's the code to open the recordset:
    Set rsSpecsAndData08 = New ADODB.Recordset
    With rsSpecsAndData08
    Set .ActiveConnection = cnDBf
    .Source = "SELECT * FROM [SPECSA~1.DBF]"
    .LockType = adLockOptimistic
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .Open
    End With
Notes: File Name is too long so MS-DOS name is used.
Code for adding records is:
rsSpecsAndData08.AddNew
rsSpecsAndData08!estno="Tst Record"
rsSpecsAndData08.Update

Why won't records added using ADODB not show when an Approach View uses the dbf file?
BruceHSanfordAsked:
Who is Participating?
 
BruceHSanfordConnect With a Mentor Author Commented:
Here's the solution: Approach's dbf databases allow field names that are very long and have spaces. This is illegal when the database is opened by ODBC (Microsoft dBase Driver). Fields can be 10 characters and have to start with a letter (I think). Anyway, the result of opening an Approach dbf is that the field names are truncated, etc. You can post to the ADODB dbf version of the Approach dbf but it will corrupt the file unless the Approach field names already obey the more restrictive ODBC field rules.
0
 
käµfm³d 👽Commented:
Are you sure that your Approach View is pointing to the same database file?  If you can see the records when you view using Approach, then they are a part of the file. Confirm that both applications are pointing to the same file name.
0
 
käµfm³d 👽Commented:
To go futher, ADODB is just a mechanism to interact with a database (SELECT, UPDATE, etc.). ADODB has nothing to do with how the data is displayed in a standalone application (e.g. Approach).
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
BruceHSanfordAuthor Commented:
Hello kaufmed,
I have checked and verified that both Approach and ADODB are pointing to the same database.
0
 
käµfm³d 👽Commented:
I'm sorry. I meant that .dbf and .apr are pointing to the same database.
0
 
BruceHSanfordAuthor Commented:
Hello kaufmed,
I'm using ADODB in my Visual Basic program to do the database work. I agree that ADODB has nothing to do with how the data is displayed in a standalone application (e.g. Approach). When i run Approach and select one of the View files it's using a .dbf database with records that were entered directly in Approach and some records that were added in a VB program which uses ADODB.
0
 
BruceHSanfordAuthor Commented:
Another puzzle piece:
The newly added (in VB) record is the last one in the .dbf database. Run the Approach program that uses that dbf and the record can't be seen, although it is counted in Approach's total record count.
Then, I take that new record that is last in the dbf and copy it over top of an earlier record. Now Approach sees the newly added record copied over an earlier record but not the one last in the database.
I hope this isn't as confusing to you as it is to me but, my best guess is that Approach's .ADX Index file for the dbf isn't being told there is a new record because it's being added by VB using ADODB.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.