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

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.

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

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.

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.

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.

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."

MS Access specific:

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

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..

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.

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

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 ...

  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

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');"

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

suhasheqde,
You asked ''Is the reading from a DSN or a Connectionstring?"

It is using a ConnectionString

Ohhh....That is for shared access , Multiple users ...

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

unfortunatly, I get the same error with the new execscript.
Bummer.

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

Thanks.

how to create dbf table from excel or sql table through vb code