[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB6 - DBF - External Table is not in the expected format.

Posted on 2007-10-17
22
Medium Priority
?
4,938 Views
Last Modified: 2013-12-20
I needed to add a column to an existing table thru some SQL and that seems to work just fine.
Here is how I added the column:

    Set updateConn = New ADODB.Connection
    updateConn.ConnectionString =   "Provider=vfpoledb;" & _
           "Data Source=C:\DataFiles\;" & _
           "Mode=ReadWrite;" & _
           "Collating Sequence=MACHINE;"

I then execute  the following SQL:
        updateConn.Execute "ALTER TABLE SHDIV ADD COLUMN State1TI Numeric(10,4) NULL;"

The column appears to be created correctly, and if I do lookups via the Ole driver everything seems to work just fine.  My problem is there is another program that will also use this dbf file (not at the same time) and this other program is using the Jet Driver.  And when it tries to read that table it gets an error: "External Table is not in the expected format."  Unfortunatly switching this other program over to use the Ole driver is probably not an option.
The only other weird thing is that when I do view the newly modified table in a program called DBF Viewer 2000 I can see my new field, but  there is an additional column that got created "_NullFlags" of type Memo.  I do not see that field when I'm looking at the Field.Columns.Name though.  Weird huh?

Any ideas?

Thanks
Mark
0
Comment
Question by:cambridge-tech
  • 8
  • 7
  • 6
  • +1
22 Comments
 
LVL 33

Expert Comment

by:CarlWarner
ID: 20098486
It more than likely is creating the table with a newer dbf format that your DBF Viewer doesn't support (I know nothing about its capabilities).  But the Jet engine will have no chance, IMO, of opening a newer dbf, period, unless it uses a newer connedctivity driver.  

You may have better luck going back to trhe ODBC driver if you are going to have so many other dependancies that need an older dbf version.

Another possibility is just to go ahead and leave that NULL clause off when you add that new column.  Then see what the outside viewers do without tha constraint.  NULLs can get pretty tricky sometimes.  I actually avoid them in VFP most of the time.
0
 

Author Comment

by:cambridge-tech
ID: 20098538
I removed the null from the update statement with the same result.  Here is the updated statement:
        updateConn.Execute "ALTER TABLE SHDIV ADD COLUMN State1TI Numeric(10,4);"

I then restored the dbf file to the original and changed the connection string to use the ODBC
Here is the connection string I used:
    myConnString = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\DataFiles\;Exclusive=Yes; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;"

The alter worked fine, it created the new column.  But unfortunatly I still have the same problem with the Jet driver using the modified table.

Lastly I tried with the Jet driver.
Here is the connection string:
    myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\datafiles\;Extended Properties=dBASE 5.0;"   (I tried with Properties=dBase 5.0 and Properties=dBASE IV)

With the Jet when I try and run the Alter Sql I get the following error message:
Run-Time error '-2147467259 (800004005)': Operation not supported on a table that contains data.

I agree with ya Carl that its probably a version thing, that the Ole is newer and the Jet does not support the modified structure.  Does anyone know how I can get the Alter SQL to work with the Jet driver?

Thanks
Mark

0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 20098554
Jet just does not support FoxPro mostly.  It may support older FoxPro (non-visual).  And you can see you're using dBASE properties within the Jet setup and dBASE hasn't been compatible with Fox tables for a very long time.

One command within VFP that copies existing data out to an older format is COPY TO <newwtable> TYPE FOX2X.  Since I'm not totally fluent in the VB syntax and connectivity from VB, I'm not totally sure what the correct method is to do this.  But something like the following AFTER you ALTER the existing table:

oRS = oConn.Execute("COPY TO newfile TYPE FOX2X")

Sorry I can't offer more explanation.
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!

 
LVL 33

Expert Comment

by:CarlWarner
ID: 20098663
Also, I don't know what all you're doing from VB and Access and how FoxPro enters into the picture, but there really is no reason Access can't use the OLE DB Provider for VFP as well.  I can't say I've set it up for Access myself.  But, I have setup both Excel and Word to use the OLE DB Provider for VFP.  If those non-database apps can use that kind of connectivity, surely MS Access which is also an Office type application should have no problem either.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 20098677
I can't find a KB article exactly with what I want.  But, this one shows the idea:


How to use ADOX to create an SQL pass-through query in Access
http://support.microsoft.com/kb/304323/en-us

With the Data Access Object (DAO) model, you could use SQL pass-through queries to improve performance when you accessed external data. With ADO, you can use the Microsoft OLE DB Provider for SQL Server to directly access a SQL Server without the overhead of Microsoft Jet or ODBC. You can also use the Microsoft OLE DB Provider for ODBC to access data in any ODBC data source.


Obviously, the idea is the same for Visual FoxPro data as it mentioned for getting to SQL Server data directly via that OLE DB Provider.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 20098689
I'm looking at Access 2003 right now to see where the "Data Links" feature/menu choice or whatever resides in the Access product.

But, Help also says "You can also programmatically access data through an OLE DB provider in Visual Basic for Applications in Microsoft ActiveX Data Objects (ADO) (ActiveX Data Objects (ADO): A data access interface that communicates with OLE DB-compliant data sources to connect to, retrieve, manipulate, and update data.). This is accomplished by defining a connection string in the ConnectionString property of the Open method of the Connection object to pass the connection information to the OLE DB provider."
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 20098988
MS Access specific:

Introduction to OLE DB
http://www.functionx.com/vbnet/oledb/Lesson01.htm
0
 
LVL 8

Expert Comment

by:suhashegde
ID: 20102767
That can be quite troublesome.

Why not try to send a connection specific Execute string which also includes the the vFPoledb driver from Jet Connection ?
All in 1 line so that the variable created for the connection(vfpoledb) is found in the executable string of the Jet connection object ?

I have never tried because there was no need to..
0
 

Author Comment

by:cambridge-tech
ID: 20118696
Carl, I'm using VB6 talking to dbf files, that were originally created in FoxPro.

suhashqde, I'm not sure how to do that?

I'm still having issues trying to accomplish this, I think I have a workaround, but its not a pretty one.

I can create the table structure the way I want it, then send that to the clients computer, and have a maintenace program move the data from the old structure to the new.  Kind of a pain, but I don't really see any way around it.

0
 
LVL 33

Accepted Solution

by:
CarlWarner earned 1000 total points
ID: 20118724
Yes, you're using VB6 to talk to dbf files that were originally created in FoxPro.

Then you're using the OLE DB Provider for VFP to Alter the structure of a dbf and then your client can't read the file.  The OLE DB Provider for VFP is also VF, just a subset.

If your client using MS Access would use the same version of the OLE DB Provider for VFP that you used to alter the dbf, there should be no problem any longer.  I have no idea what the client with MS Access is currently doing to try and real the dbf.  I just know it won't work when you legitimately change the structure of the dbf with a legitimate OLE DB Provider gor VFP.
0
 
LVL 8

Expert Comment

by:suhashegde
ID: 20120738
Is the reading from a DSN or a Connectionstring?

If it is from a Dsn Then that would be easier.
Just install the vfpoledb driver, Modify the dsn to use the Vfpoledb driver and you are done.

If from a connectionstring , then the issue arries.

Also it is possible to modify the DBF using lowlevel filefunctions to older DBF version. Just change the header of the table
0
 
LVL 8

Expert Comment

by:suhashegde
ID: 20120770
after you execute the following line

   updateConn.Execute "ALTER TABLE SHDIV ADD COLUMN State1TI Numeric(10,4) NULL;"

Try
  updateConn.Execute "Execscript('use tablename ; copy to newtable type fox2x')"



Copy To

Creates a new file from the contents of the currently selected table

Syntax

COPY TO FileName
  [DATABASE DatabaseName [NAME LongTableName]]
  [FIELDS FieldList
  | FIELDS LIKE Skeleton
  | FIELDS EXCEPT Skeleton]
  [Scope] [FOR lExpression1] [WHILE lExpression2]
  [[WITH] CDX] | [[WITH] PRODUCTION]
  [NOOPTIMIZE]
  [[TYPE] [FOXPLUS | FOX2X | DIF | MOD
  | SDF | SYLK | WK1 | WKS | WR1 | WRK | CVS | | XLS | XL5
  | DELIMITED [WITH Delimiter | WITH BLANK | WITH TAB
  | WITH CHARACTER Delimiter]]]
  [AS nCodePage]


Select which ever suits you the best ...

0
 
LVL 8

Expert Comment

by:suhashegde
ID: 20120789
  updateConn.Execute "ALTER TABLE SHDIV ADD COLUMN State1TI Numeric(10,4);"
 updateConn.Execute "Execscript('use shdiv ; copy to newtable type fox2x');"

try to open the newtable with the jet driver.

You may have to rename the newtable before use in production.

look at the with CDX, production and ncodepage clauses to tweak the dbf

0
 
LVL 8

Expert Comment

by:suhashegde
ID: 20121545
updateConn.Execute "Execscript('use shdiv ; copy to newtable type fox2x');"

Should have been
updateConn.Execute "Execscript('use shdiv '+chr(13)+chr(10)+'copy to newtable type fox2x');"
0
 

Author Comment

by:cambridge-tech
ID: 20135652
suhasheqde,
The first statement works fine
updateConn.Execute "ALTER TABLE SHDIV ADD COLUMN State1TI Numeric(10,4);"

The second statement I tried
        updateConn.Execute "Execscript('use shdiv '+chr(13)+chr(10)+'copy to newtable type fox2x');"
and I get the following error:
Run-time error '-2147217911 (80040e09)';
[Microsoft][ODBC Visual FoxPro Driver]Syntax error or access violation
0
 

Author Comment

by:cambridge-tech
ID: 20135662
suhasheqde,
You asked ''Is the reading from a DSN or a Connectionstring?"

It is using a ConnectionString
0
 
LVL 8

Expert Comment

by:suhashegde
ID: 20136032
Ohhh....That is for shared access , Multiple users ...

updateConn.Execute "Execscript('use shdiv shared'+chr(13)+chr(10)+'copy to newtable type fox2x');"
0
 

Author Comment

by:cambridge-tech
ID: 20136197
unfortunatly, I get the same error with the new execscript.
Bummer.

0
 
LVL 8

Assisted Solution

by:suhashegde
suhashegde earned 1000 total points
ID: 20137417
updateConn.Execute "Execscript('use shdiv shared'+chr(13)+chr(10)+'copy to newtable type fox2x')"

try without the ';'


Or seperate lines
 updateConn.Execute "Execscript('use shdiv ')"
 updateConn.Execute "Execscript(''copy to newtable type fox2x')"

0
 

Author Comment

by:cambridge-tech
ID: 20144536
nope, get the same error on both/either lines.

I did a workaround for now.  When I just created the new structure I want, then in code copy from old table to new, then rename in code.  Kinda ugly, but I needed to get moving on this.

I appreciate both of ya trying so deligently on this.  I'm going to go ahead and split the points.
Mark
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 20144574
Thanks.
0
 
LVL 3

Expert Comment

by:techprocess
ID: 20688195
how to create dbf table from excel or sql table through vb code
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

873 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