Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unanswered: dbase III through DAO 3.51 (Jet 3.5) VB

    I use VB 6.0 to create dbase III database. I did some investigating on how to do it, and found out that speed-wise it is better to use DAo 3.5 with dbase files when working through VB. I used VB to create a dbase table and associated indexes (which must be unique) using the following code:

    Dim db As DAO.Database
    Dim strSQL As String

    Set dbvtt = DBEngine.Workspaces(0).OpenDatabase("C:\AVL\data", 0, 0, "dBase III;")
    dbvtt.Execute ("Create table VehTrans (VehID LONG, vehIP string(20));")
    dbvtt.Execute ("CREATE UNIQUE INDEX VehIDInd ON VehTrans (VehID);")
    dbvtt.Execute ("CREATE UNIQUE INDEX VehIPInd ON VehTrans (VehIP);")

    The statements above create 3 files in the source directory: .dbf and 2 .ndx(es), as expected. Than I try to add data, and this is where the problem comes:

    dbvtt.Execute ("INSERT INTO VehTrans VALUES(12,'test1');")
    dbvtt.Execute ("INSERT INTO VehTrans VALUES(12,'test1');")

    I do not get any error message if I try to add the above data, even though the second record is a duplicate of the first, and should not be allowed.

    Am I doing something wrong?

    Also, I've read about the dbf file indexing. I found out that in FoxPro, for instance, in order to disallow the records with duplicate key values, you must set unique attribute on the field in the SQL statement when creating the database -- thus creating a candidate key. When I tried to set unique attribute for both of my fields in the create table statement:

    dbvtt.Execute ("Create table VehTrans (VehID LONG Unique, vehIP string(20) Unique);")

    I've got a run-time error "Error in create table statement"; it seems that this is not supported

    Any suggestions??
    Thank you very much

  2. #2
    Join Date
    Apr 2004

    Thumbs up dBASE files via VB

    While it is possible to create and/or modify a dBase (or any ISAM) file structure on-the-fly with VB, I would strongly recommend against it. First and foremost...Microsoft swears it cannot be done, and it's wholly unsupported. That's not such a big deal, though...developers have been using "unsupported" VB features for years. The main issue is that the Jet DB engine that VB uses tends to translate field types and sizes into what it "thinks" are correct for a field...which may cause you some problems depending on exactly what you're doing with the resulting file. Character fields are no problem, but you lose the dBase field sizes and decimal formatting for numerics. For example, if you setup a database file in dBase, you can specify that the field has a length of 4, with no decimals. If you do the same thing in VB you'd have to define the field as an integer, with the end result being the same field having a length of 5 and four decimal places. There is no way to get around this behavior unless you're ready to do some low-level file IO on the completed dbase file...which is a royal pain.

    So...if the file you're creating uses only character fields, or you don't mind the field size issues for numerics, you can get away with creating the file via VB and DAO.

    Here's the code to do it:
    Dim Db as DAO.Database
    Dim Td as DAO.TableDef, Fld as DAO.Field

    Set Db = DAO.OpenDatabase("C:\Data", True, False, "dBASE III")
    Set Td = Db.CreateTableDef("TestDb.dbf")

    Set Fld = Td.CreateField("Field1",dbText,12)
    Td.Fields.Append Fld

    Set Fld = Td.CreateField("Field2",dbText,10)
    Td.Fields.Append Fld

    Db.TableDefs.Append Td
    Db.Closeet Db = Nothing

    Hope this helps.

  3. #3
    Join Date
    Feb 2011
    Hi, I've encountered this very same problem with decimal places and numeric widths in Foxpro 2.5. I'm creating a dbase table from vb6 using ADOX. And once the fields are created, fields with numeric data type have a fixed width of 20 and dec. width of 5, even if it is programmatically set to a different width.

    If it can't be done using ADOX and DAO. What can I possibly use to do this? I've tried using the FoxApplication to run a command directly to Foxpro.

    Global fx As New FoxApplication
    Set fx = Nothing

    fx.OleRequestPendingTimeout = 900000
    fx.OleServerBusyTimeout = 900000

    fx.DefaultFilePath = LPath

    'fx.OleRequestPendingTimeout = 1
    'fx.OleServerBusyTimeout = 1

    fx.DoCmd "set safety off"
    fx.DoCmd "clear all data"
    fx.DoCmd "close all data"
    fx.DoCmd ("CREATE TABLE test.dbf (FNAME C(12))")

    BUT, when opened in Foxpro 2.5, it displays the error "NOT A DATABASE FILE"

    Do you have any ideas, pls.? Thanks in advance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts