Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2007
    Location
    College Park, Maryland
    Posts
    14

    Question Unanswered: Creating dbase table in VB 6.0

    Hello people!

    I want to creat and fill an CHEN.dbf file using vb6.
    I know that is possible to update (fill) the table only if I use Ms Dao 3.51 Object library (not 3.6) reference.

    this is the code

    Private Sub Command1_Click()
    Dim myWS As Workspace
    Dim mydb As Database
    Dim myTDef As TableDef
    Dim myRS As Recordset
    Dim myFld As Field
    Set mydb = OpenDatabase("C:\Temp\", False, 0, "DBase 5.0;")
    Set myTDef = mydb.CreateTableDef("CHEN.dbf")
    With myTDef
    .Fields.Append .CreateField("PartDesc", dbText, 8)
    .Fields.Append .CreateField("inv1", dbInteger, 4)
    .Fields.Append .CreateField("inv2", dbInteger, 4)
    End With

    mydb.TableDefs.Append myTDef
    Set SQL = mydb.CreateQueryDef("")
    SQL.SQL = "select * from CHEN.dbf;"
    Set myRS = SQL.OpenRecordset()
    With myRS
    .AddNew
    !PartDesc = "Widget"
    !inv1 = 4
    !inv2 = 3
    .Update
    End With

    Problem:
    the program stops on "Set mydb = OpenDatabase("C:\Temp\", False, 0, "DBase 5.0;") with an error: "Couldn't find installable ISAM"

    Any help or Suggestion??

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    see msdn (F1)

    http://support.microsoft.com/default.aspx/kb/90111

    CAUSE
    ISAM drivers are used by Microsoft Access to update file formats other than its own.

    In Microsoft Access 2.0, if the path to the ISAM driver in your Msacc20.ini file is invalid, or if the ISAM driver does not exist, you may receive the following error message:

    Couldn't find installable ISAM.

    Microsoft Access 7.0 and 97 do not have an .ini file. Information previously stored in the .ini files is now stored in the Microsoft Windows Registry. In Microsoft Access 7.0 and 97, if the path to the ISAM driver in your Windows Registry is invalid, or if the ISAM driver does not exist, you may receive the following error message:

    Couldn't find installable ISAM.

    This error can also occur if you enter an invalid database name for a SQL database when you are using the TransferDatabase action in a macro. When you use an invalid connect string, Microsoft Access looks for an installable ISAM for the SQL database. If an installable ISAM cannot be located, an error occurs.

  3. #3
    Join Date
    Dec 2007
    Location
    College Park, Maryland
    Posts
    14
    Its ok now. Thnx.
    but i got another problem;
    - i can't use dbNumeric it will have an error msg "INVALID FIELD DATA TYPE"
    - how can i put the number of decimal places, i only want 2 it will always have the default no. which 5.

  4. #4
    Join Date
    Dec 2007
    Location
    College Park, Maryland
    Posts
    14
    Its ok now. Thnx.
    but i got another problem;
    - i can't use dbNumeric it will have an error msg "INVALID FIELD DATA TYPE"
    - how can i put the number of decimal places, i only want 2 it will always have the default no. which is 5.

  5. #5
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    Quote Originally Posted by janlie87
    Its ok now. Thnx.
    but i got another problem;
    - i can't use dbNumeric it will have an error msg "INVALID FIELD DATA TYPE"
    - how can i put the number of decimal places, i only want 2 it will always have the default no. which is 5.
    In vb6 you have Double or currency
    See :
    dim dblValue as Double
    dim currValue as currency

    dblValue = 1
    currValue =1

    debug.print dblValue
    debug.print currValue
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  6. #6
    Join Date
    Dec 2007
    Location
    College Park, Maryland
    Posts
    14
    yeah i know. what i mean is how can i use dbNumeric in my new created .dbf table? inv1 and inv2 supposed to have a type NUMERIC with a width of 11 and 2 decimal places.. how can i do it in my program?

  7. #7
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Something like this

    Set oTable = New ADOX.Table
    With oTable
    .Name = "Priorities"
    .Columns.Append("Priority_ID", adInteger)
    .Columns.Append("Description", adVarWChar, 20)
    .Columns.Append("Number", adNumber, 11,2)
    End With

    Call catDatabase.Tables.Append(oTable)

    if you do the ".Columns.Append( " it will give you intellisence and you just have to give in the parameters.
    Here are the most common Data types:

    char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
    varchar(size) Variable-length character string. Max size is specified in parenthesis.
    number(size) Number value with a max number of column digits specified in parenthesis.
    date Date value
    number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

    See http://www.functionx.com/ado/Lesson06.htm
    for a few examples
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  8. #8
    Join Date
    Feb 2010
    Posts
    2

    Double with 'n' Decimals in dBase with VB

    Hi Janlie87, I can help you but I can't speak in english, sorry.

    Si alguien habla español e inglés por favor traduzca, sino utilicen Google Traductor

    Hola janlie 87, te cuento q desde hace meses que tenía el mismo problema y por internet sólo encontré personas que les pasaba lo mismo, pero ninguna
    solución....
    Luego de buscar y buscar dentro de las propiedades de las BD de VB llegué a la conclusión de que esta función no existe en VB.
    Así que busqué algo mejor...

    Por suerte ya disponía de un archivo .dbf que tenía campos numéricos double sin dígitos decimales y campos numéricos double con 2 dígitos decimales. A
    partir de esto generé un archivo igual desde VB.
    Como resultado obtuve una tabla igual, pero con los campos numéricos de 5 dígitos decimales

    El paso siguiente fue comparar ambos archivos con un editor hexagesimal (hay editores free por la red, yo utilicé el XVI32). La diferencia quedó muy a la vista, por lo que pude ver, la estructura de un archivo es la siguiente:

    Los primeros 32 bityes son el encabezado del dbf, ni idea que se define ahí.
    A partir del byte 33 se enumeran los campos con todas sus propiedades, comenzando por el título del campo, y cada bloque es de 32 bytes.
    ¿qué quiere decir esto? Que si en mi base de datos tengo como primer campo CANTIDAD, segundo campo DESCRIPCION y tercer campo PRECIO, el primer campo va a comenzar en el byte 33, el segundo en el byte 33+32=55 y el tercero en el byte 33+32+32=87

    Dentro de cada bloque de definición de campo de 32 bytes, el byte NRO 18 define la cantidad de dígitos decimales.
    En el ejemplo mencionado antes, debería cambiar las propiedades del campo CANTIDAD, es decir que hay q abrir el archivo y modificar el byte NRO 50 (32 de encabezado + 18 para llegar al byte de nro de decimales) al valor 0.
    Siguiendo el ejemplo, para el caso de PRECIO deberíamos modificar el byte nro 114 (32 de encabezado + 32 por CANTIDAD + 32 por DESCRIPCION + 18 para llegar al byte) a 2
    OJO! Es 2 en hexadecimal que no es lo mismo que 2, desde VB se pone &H2.

    Aclaración: desconozco si todos los archivos .dbf guardan esta relación, recomiendo primero probar con el editor hexagesimal. Para poder comparar podés abrir el archivo creado desde vb con Excel modificar la cantidad de dígitos decimales de un campo y luego guardar una copia con diferente nombre. Luego comparás ambos archivos para saber qué byte modificar.

    Una vez que tenés definido qué byte modificar, podés hacerlo por código desde visual basic, te paso a continuación una copia del procedimiento que yo utilicé:

    Sub CorregirDecimalesDbf(archDbf As String)
    'El dbf tiene un encabezado de 32 bytes
    'Luego en cada grupo de 32 bytes se define el nombre y propiedades de cada campo
    'En el 12avo byte de cada campo se define el tipo
    'En el byte 18 se define la cantidad de decimales (en caso q sea un número)
    'En el byte 17 parece q se define el ancho en q se va a mostrar la columna, x ej en excel

    Open archDbf For Binary As #1
    Put #1, 49, &H8 'corresponde al ancho de columna de CODIGO
    Put #1, 50, &H0 'corresponde a la cantidad de dígitos de CODIGO
    Put #1, 81, &H6 'corresponde al ancho de columna de CANT
    Put #1, 82, &H0 'corresponde a la cantidad de dígitos de CANT
    Put #1, 113, &H30 'corresponde al ancho de columna de DESC
    Put #1, 145, &H8 'corresponde al ancho de columna de PRECIO
    Put #1, 146, &H2 'corresponde a la cantidad de dígitos de PRECIO
    Put #1, 177, &HF 'corresponde al ancho de columna de EDITOR
    Put #1, 209, &H55 'corresponde al ancho de columna de COMENTARIO
    Close #1
    End Sub

    IMPORTANTE: ejecutá el procedimiento antes de agregar cualquier registro en la tabla, es decir, inmediatamente después de crearla, de lo contrario vas a tener problemas con los datos guardados.


    Bueno, eso es todo! Espero que les sirva!

    Quote Originally Posted by janlie87 View Post
    Hello people!

    I want to creat and fill an CHEN.dbf file using vb6.
    I know that is possible to update (fill) the table only if I use Ms Dao 3.51 Object library (not 3.6) reference.

    this is the code

    Private Sub Command1_Click()
    Dim myWS As Workspace
    Dim mydb As Database
    Dim myTDef As TableDef
    Dim myRS As Recordset
    Dim myFld As Field
    Set mydb = OpenDatabase("C:\Temp\", False, 0, "DBase 5.0;")
    Set myTDef = mydb.CreateTableDef("CHEN.dbf")
    With myTDef
    .Fields.Append .CreateField("PartDesc", dbText, 8)
    .Fields.Append .CreateField("inv1", dbInteger, 4)
    .Fields.Append .CreateField("inv2", dbInteger, 4)
    End With

    mydb.TableDefs.Append myTDef
    Set SQL = mydb.CreateQueryDef("")
    SQL.SQL = "select * from CHEN.dbf;"
    Set myRS = SQL.OpenRecordset()
    With myRS
    .AddNew
    !PartDesc = "Widget"
    !inv1 = 4
    !inv2 = 3
    .Update
    End With

    Problem:
    the program stops on "Set mydb = OpenDatabase("C:\Temp\", False, 0, "DBase 5.0;") with an error: "Couldn't find installable ISAM"

    Any help or Suggestion??

  9. #9
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Interesting to have multiple languages in one thread. I suppose Traductor means Translator. Never tried it yet. I wonder whether it is possible to translate just this response into a default language (English in this case).

  10. #10
    Join Date
    Feb 2010
    Posts
    2
    Yes AnathaP, the correct word is Translator, thanks!

    Quote Originally Posted by AnanthaP View Post
    Interesting to have multiple languages in one thread. I suppose Traductor means Translator. Never tried it yet. I wonder whether it is possible to translate just this response into a default language (English in this case).

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    A babelfish translation, with a little cleanup by yours truly...
    Quote Originally Posted by babelfish
    If somebody Spanish and English speech please translates, but they use Google Translator

    Hello janlie 87, I tell you for months that I had the same problem and by Internet I only found people who had the same problem, but no solution….

    After to searching the DB properties of VB I reached the conclusion that this function does not exist in VB. So I looked for something better…

    Luckily I already had a .dbf file that contained doubles without digits, and had decimal numeric fields and double numeric fields with 2 digits after the decimal.

    To begin, I generated an equivalent file from VB. As result of this, I obtained an equivalent table, but with the numeric fields of 5 digits decimal.

    Then, I compared both files with a hex editor (free by the network are publishers, I used the XVI32.) The difference was very in view, reason why I could see, the structure of a file is the following one: The first 32 bytes are the DBF header, nor idea that is defined there. From byte 33 the fields with their properties are enumerated, beginning by the title of the field, and each block is of 32 bytes.

    What this means is this: That if in my DBF file I have a first field named AMOUNT, the second field named DESCRIPTION and the third field named PRICE,
    • the first field is going to begin at byte 33
    • the second field at byte 33+32=55, and
    • the third field at byte 33+32+32=87
    Within each block of definition of field of 32 bytes, byte number 18 defines the number of decimal digits.

    In the example mentioned before, it would have to change to the properties of the field AMOUNT, that is to say that is to open the file and to modify byte number 50 (32 of header + 18 to arrive at the byte from number for the decimal) to value 0.

    Following the example, for the case of PRICE we would have to modify the byte number 114 (32 of header + 32 for AMOUNT + 32 for DESCRIPTION + 18 to arrive at the byte) to 2 hexadecimal (not just like 2, from VB and is written as H2.

    Explanation: I do not know if all the .DBF files have this configuration, I first recommend to examine them with the hexadecimal editor. In order to be able to compare the file created from Excel VBA to to examine the number of decimal digits of a field create a copy with different name. By comparing both files, You will know what bytes to modify. Once you have defined what bytes to modify, you can do it with VB code.

    Here is a copy for you of the procedure that I used:
    Code:
    Sub CorrectDecimalsDbf (FileDbf As String) 
      ' The DBF has heading of 32 bytes 
      ' Soon in each group of 32 bytes one defines the name and properties of each field 
      ' In 12avo byte of each field defines the type 
      ' In byte 18 the amount of decimal is defined (in case q it is a number) 
      ' In byte 17 it seems q defines the width in q is going away to show the column, x ex in excel 
    
      Open FileDbf For Binary as #1 
        Put #1, 49, & H8 ' it corresponds to the width of CODE column 
        Put #1, 50, & H0 ' it corresponds to the amount of CODE digits 
        Put #1, 81, & H6 ' it corresponds to the width of CANT column 
        Put #1, 82, & H0 ' it corresponds to the amount of CANT digits 
        Put #1, 113, & H30 ' it corresponds to the width of DESC column 
        Put #1, 145, & H8 ' it corresponds to the width of PRICE column 
        Put #1, 146, & H2 ' it corresponds to the amount of PRICE digits 
        Put #1, 177, & HF ' it corresponds to the width of PUBLISHER column 
        Put #1, 209, & H55 ' it corresponds to the width of COMMENTARY column     
        Close #1 
      End Sub
    IMPORTANT: execute the procedure to modify the file immediately before adding any data in the table, that is to say, immediately after creating it, otherwise you are going to have problems with the stored data.

    Good, that is everything! I hope that it helps!
    To paraphrase, the DBF structure contains a 32 byte file header, followed by one or more 32 byte field headers, one for each field. Each field header contains (at least) the field name, type, width/precision [and scale (number of digits to the right of the decimal)]. Within the field definition block, the field type is stored at offset 12d, the width or Precision at offset 17d and the scale at offset 18d. These three values are in hex.

    To proceed, in your code, first, create the DBF file using 'standard' VB code, then before adding any data, alter the field definitions as per the post above, and ONLY then, can you add data to the dbf file. If you add data before you change the field definitions, you will probably corrupt the file, or at a minimum, change the value of the numeric data within the file which has had the field definitions changed.

    Note that if you use byte data types to store the field width/precision/type, you would not need to convert to hex first. Just PUT the byte variable directly into the file offset as calculated.
    Last edited by loquin; 04-01-10 at 16:07.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Found a decent file format reference for DBF files here.

    Did some testing, and verified that there is an issue with Microsoft's ODBC drivers/OleDB providers for dBase. The drivers just don't properly assign the numeric field's precision and scale when creating a dBase table. They ALWAYS create a field with the maximum precision for the version of dBase file specified, and a default scale of 5.

    Verified this by creating a table with ADOX and then by issuing SQL create table statements via an ADO connection.

    As joakinic pointed out, the workaround is to
    1. Create the file
    2. Update the Field Definitions, and
    3. ONLY then, add any records to the new table.


    However, a much cleaner method to individually calculating the file offsets (as with joakinic's approach,) is to first create user defined data types and variables to 'map' the header data, then, open the data file in binary, using those previously 'mapped' UDT variables as the file buffers.

    As an example, in a code module (.bas file) place the following types and variables
    Code:
    Public Type dbfHead
       VersionNum As Byte
       ModifyYear1900 As Byte
       ModifyMonth As Byte
       ModifyDay As Byte
       NumRecs As Long
       HeaderLen As Integer
       RecordLen As Integer ' Sum of lengths of all fields +1 (delete flag)
       RFU1 As Integer
       IncompleteTrans As Byte
       EncryptionFlag As Byte
       FreeRecThread As Long
       RFU2 As Long
       RFU3 As Long
       MDX_Flag As Byte
       Language As Byte
       RFU4 As Integer
    End Type
    
    Public Type dbfFld
       FieldName As String * 10   ' padded with Null chars
       Field_ENull As Byte        ' always 00
       Field_Type As String * 1
       Field_Displace As Long
       Field_Len As Byte
       Field_Scale As Byte
       Field_Flags As Byte
       Field_AI_Next As Long
       Field_AI_Step As Byte
       Field_RFU As Long
       Field_RFU2 As Long
    End Type
    
    Public dbfHeader As dbfHead
    Public dbfFields() As dbfFld     ' dynamic array of dbfFld
    Then, in your file load subroutine...
    Code:
    '
       Dim ifh As Integer
       Dim N As Integer
       Dim iRecs As Long
       
       redim dbfFields(0)
    
       ifh = FreeFile
       
       ' assumes test_tab.dbf is located in the application folder
       Open app.path & "\TEST_TAB.DBF" For Binary As #ifh
          ' read the header
          Get #ifh, , dbfHeader
          
          ' calc the number of fields
          '(total header length - the length of the header record) / length of field record)
          iRecs = (dbfHeader.HeaderLen - Len(dbfHeader) - 1) / Len(dbfFields(0))
          ' resize the fields array to hold all the field header data
          ReDim dbfFields(iRecs)
          
          For N = 1 To iRecs
             Get #ifh, , dbfFields(N)
             Debug.Print N, dbfFields(N).Field_Name, dbfFields(N).Field_Type, dbfFields(N).Field_Len, dbfFields(N).Field_Scale
          Next N
       Close #ifh
    (Note how the dbfHead and dbfField user defined types reflect the file spec in the link above...)

    Since you're reading each 'block' of data contiguously, you don't need to explicitly calculate the file offset (the second property of the GET statement,) as the file pointer is incremented automatically when reading a block.

    Note that since the header record and each of the field records are the same length (32 bytes) you could open the file For Random instead of For Binary, I suppose. Then, you could just increment the record number when reading. I find it just as easy to use binary, though.

    The advantage to the approach of 'mapping' your file structure to UDTs as per above, is that you load all the header data into memory at one time. Then, you close the file. Only after you make your edits in memory, do you then write that memory to the file, using PUT statements. (BTW, I would strongly recommend that your app FIRST copy the file to FileName.BAK...)

    Assuming that your table has at least two fields, after reading the file header per above,
    • dbfHeader.VersionNum holds the 'signature' of the file. This gives you a good idea of what version of program created the file.
    • dbfHeader.HeaderLen contains the the length of the header, in bytes. If you added a field, you will need to increase the HeaderLen value by 32 (Len(dbfHeader(N)))for each field added.
    • dbfHeader.ModifyYear1900 contains the year that the file was last modified, minus 1900. (it's a byte, and the 'base' year is 1900.) i.e., when displaying the year, add 1900 to to the value from the header. Apparently, some dbf 'compatible' applications do NOT apply the year 1900 offset, however, and assume that years less than 50 fall in the 21st century, and years over 1950 fell in the 20th century. This approach fails after Dec 31 2049. (Never say 'Never...' Remember an event called Y2K ?)
    • dbfHeader.ModifyMonth holds the Month the file was last altered
    • dbfHeader.ModifyDat holds the day of the month the file was last altered. If you Change the file's definition, you should update the three modify date values as necessary.
    • dbfHeader.NumRecs is the number of records currently in the data file. If this is greater than zero, you should NOT edit the header.
    • dbfHeader.RecordLen holds the sum of all fields .Field_Width values, plus 1. If you edit a field width, you MUST recalculate the new .RecordLen value and update it.
    • dbfHeader.EncryptionFlag - do not alter this value.
      .
    • dbfFields(1).FieldName contains the field name of the first field. 10 characters max. Also note that the field name must be padded with null characters (chr(0)] to be 10 characters long, and it cannot contain spaces or begin with a number or underscore. Valid characters are A-Z, 0-9, and the underscore (_). There's a field, dbfFields(1).ENull which you should never change, as the field name MUST be null character terminated...
    • dbfFields(2).Field_Type holds the character which defines the field type for Field 2. "C" = CHAR, "N" = Numeric, "F"=Float, etc. Ref the link above.
    • dbfFields(N).Field_Len holds the field width for char fields (254 max,) or precision for numeric fields. Other field types generally have default values for width which cannot be changed from the defaults.
    • dbfFields(N).Field_Scale holds the number of digits to the right of the decimal for Numeric Data types, and can never be more than 2 less than the .Field_Len

      Note that the max values for Field length, field scale, vary, depending upon the version of the file. dB-II had 16/14 respectively, dB-III/dB-III+ was 19/17, and FoxPro/FoxBase/DB-IV and later allowed up to 20/18. In addition, the maximum size of Memo fields varies according to the vintage of the file. dB-II was 1000 characters, dB-III through dBV5 was 4000 characters, whereas Foxpro and Foxbase allow up to 32767 characters in memo fields. Later versions allow up to 4BG, but, you absolutely need to know what version you are dealing with, as the newer versions only use 4 bytes for the field width (as an unsigned long) to store the memo 'link' where older versions of the same app could store 10 digits of the file offset.
    • dbfFields(N).AI_Next and dbFields(N).AI_Step are only used in file versions which support Auto-Increment data types and refer to the next auto-increment value for the table, and the step size of the auto-increment field. An auto-increment is a long data type, and always has a Width of 4 bytes.)


    Hope this helps.
    Last edited by loquin; 04-10-10 at 15:17.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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