[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3915
  • Last Modified:

Adding column to exising dbf file

I have an existing dbf table, that I would like to add an additional column.

I have tried 2 different ways without any success.

1st way.  (gDBDIr is the directory where the dbf files exist)
        Set mydb = OpenDatabase(gDBDir, False, 0, "DBase 5.0;")
        Set myTDef = mydb.TableDefs("SHDIV")
        With myTDef
            .Fields.Append .CreateField("NewField", dbText, 1)
        End With
        mydb.TableDefs.Append myTDef
        mydb.Close

On the CreateField line I get an error:
Run-time error '3282': Operation not supported on a table that contains data.

2nd way I tried. (MyConn is a connection that is setup and opened)
    MyConn.Execute "ALTER TABLE SHDIV ADD COLUMN NewField CHAR(25) NULL;"

I get the following error
Run-time error '-2147467259 (80004005)': Operation not supported on a table that contains data.

Any help would be greatly appreciated.
Thanks in advance
Mark
0
cambridge-tech
Asked:
cambridge-tech
  • 6
  • 2
1 Solution
 
CarlWarnerCommented:
If a table is open in shared mode (i.e., not exclusively), you will not be able to Alter the structure of the existing table.  I can't tell what you're doing before all that you show, but you must get exclusive use, rather than shared access, to any table you will want to change in the manner you show.
0
 
suhashegdeCommented:
Set mydb = OpenDatabase(gDBDir, False, 0, "DBase 5.0;")


Why Dbase 5.0

Why not VFP oledb driver ?

Use the latest Vfp oledb driver with exclusive = yes parameter in the connection string.

Carl Can you please provide the link from you book mark ?


also the syntax should read

ALTER TABLE SHDIV ADD COLUMN NewField c(25) NULL
0
 
CarlWarnerCommented:
If this is Fox data, rather than dBASE specific data tha may have dBASE specific data features not in Fox stuff, certainly the following driver is the best way to go and it is backwards compatible (a quirk or two will come up that we can address if you go this way):

Microsoft OLE DB Provider for Visual FoxPro 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
cambridge-techAcct MgrAuthor Commented:
Carl: I don't do anything before that, I load the form then try the CreateField

suhashegde:
"Why Dbase 5.0
Why not VFP oledb driver ?"

Acutally I use the vfp ole for my other connections.  That was just some other code I found online.
Do you know what I need to put instead of "DBase 5.0"  I tried "Microsoft Visual FoxPro Driver" and "vfpole"  I then get an error on the open database command "Run-time error '3170 Couldn't find installable ISAM"
0
 
CarlWarnerCommented:
0
 
CarlWarnerCommented:
Another less information connection string URL:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForVisualFoxPro


BTW, that download link for the OLE DB Provider for VFP is for the SP1 version of VFP9 and not the newest SP2 version that was released on Oct. 11th, 2007.  If you have that SP2 loaded locally to your PC for VFP9, it did get installed.  It's just that the separate download link has not been updated yet.  A few minutes ago, I requested from MS that the URL I posted (that shows an older date for SP1) be updated with the SP2  version. We'll see how long that request takes. :)
0
 
CarlWarnerCommented:
Thanks.
0
 
cambridge-techAcct MgrAuthor Commented:
Thanks Carl, that got me what I needed.
I just changed my connection string and then the alter sql worked.  Here is what I did in case anyone else comes across this.

    sConnect = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\Demotemp;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;"
   
   
    Set MyConn = New ADODB.Connection
    MyConn.ConnectionString = sConnect
    MyConn.Open
    MyConn.Execute "ALTER TABLE SHDIV ADD COLUMN NewField CHAR(25) NULL;"
0
 
CarlWarnerCommented:
So, you actually used the ODBC driver rather than the OLE DB Provider for VFP.  Both support ADO commands.

I don't really know what version of VFP the original data is in.  I just want to warn you that the ODBC Driver for VFP only supports data features through VFP6.  If there was a later data feature used in the tables/dbfs that you need to connect to, you would be forced to use the OLE DB Provider for VFP either a) to get any results at all or b) to get correct results.  I have seen others ask why are they getting odd results when they have used the ODBC driver for data created in VFP8, for example.  Just a heads-up. YMMV...
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.

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