Results 1 to 7 of 7

Thread: Export to DBase

  1. #1
    Join Date
    Mar 2008
    Posts
    6

    Unanswered: Export to DBase

    Hello everyone

    How can export tables to dbase (dbf) with field sizes fixed?
    Example:
    field1 N 15.2
    field2 Ch 10

    Someone can help me?

    thanks

    (sorry my english)

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    When viewing the data in either table or query view, click File -> Export (and change the "Save File Type As" in the popup (under where you specify the name of the file) to one of the dbase types (I like to use dbase (III) .dbf type) but your options are also dBase 5 or dBase IV. If you don't see these selections, you may have to re-install the MDAC or Access - what version of Access do you have as I'm not sure if these types are available with the old versions of Access?

    Exporting as a dbase type should automatically set the field names/sizes in the export file the same as they are in the Access table.

    You can also do this when viewing the data on the form putting a button on the form with some code behind the button to do the export. If that's what you're trying to do, let me know.
    Last edited by pkstormy; 03-17-08 at 02:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2008
    Posts
    6
    Thanks for your time.

    Yes, i want to do by code like:

    sqlString = "SELECT field1, field2 INTO [dBase IV;DATABASE=D:\My Documents\dBase].[dBase1] FROM [Table1]"

    cnn.Execute sqlString

    But I can't specify the size of the fields, It is possible to do so?

    thanks

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Take a look at this example (specifically the bottom of the ReportForm which let's you export to a dbase file (select dbase as the type): http://www.dbforums.com/showpost.php...5&postcount=55

    The above example has a nice routine which lets you select any query to export to a .csv, .xls, or dbase format.

    The code in the ReportForm to specifically export the dbase part is (look at the code behind the command button called: cmdExportData):

    Private Sub cmdExportData_Click()
    .....
    .....
    Case "dbase"
    Dim strDB As Variant
    strDB = CurrentDb.Name
    Dim NExportName As Variant
    TryAgain:
    NExportName = InputBox("Since 8 character names are allowed for exporting dbf files, enter the 8 character name to export as:")
    If IsNull(NExportName) Or NExportName = "" Then GoTo NoExport
    If Len(NExportName) > 8 Then
    MsgBox "You can only enter up to 8 characters."
    GoTo TryAgain
    End If
    DoCmd.TransferDatabase acExport, "DBase III", Me!ExportFolder, acQuery, Me!ExportQuery, NExportName & ".dbf", False
    End Select
    msgbox "Export Done."
    Done:
    End if
    exit sub
    NoExport:
    msgbox "Export not done."

    You'll notice that I made it so the user enters the name of the export file (to export as a dbase III, the name must be less than 8 characters. I think it's the same for dbase IV or dbase V.)

    Once you export the dbase file, then simply re-import it into Access to check on it. Notice also that I used a query to export where I can specify the field names. The one thing though (after checking it by re-importing into Access), the field names are not the FULL field names from what I specified in the query. It's been a while since I've worked with exporting as a dbase type but I believe you're limited to 10 characters for the field names (not sure if this is a limitation of Access). So when you make your query to export using the code above (which I made so you can select which queries as a combobox on the ReportForm (me!ExportQuery) to let users select that query that they want to export), you may need to consider keeping the field names to 10 characters or try exporting as a dbase IV or dbase V format (you may get better luck with the field names lengths in dbase IV or dbase V)

    Use the code above as part of your routine to export. The actual exporting syntax part is written out like this:

    DoCmd.TransferDatabase acExport, "DBase III", "C:\FolderName", acQuery, "MyQueryName", "8CharacterdbaseExportName.dbf", False

    or (to export a table)

    DoCmd.TransferDatabase acExport, "DBase III", "C:\FolderName", acTable, "MyTableName", "8CharacterdbaseExportName.dbf", False

    If you want to see more on the parameters of the docmd.TransferDatabase command, try searching on it in the Access Help.

    Hope that helps.
    Last edited by pkstormy; 03-17-08 at 14:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2008
    Posts
    6
    thanks pkstormy

    but I need to export with fixed widths field to dbase

    field1 string 10
    field2 number 15.2
    field3 string 4

    thanks

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Using the docmd.TransferDatabase command exports to the same field widths (and type) as what is defined in the field widths for the Access table you are exporting (which you could change in the Access table design - not sure why you'd want to export to a different width than the Access table design field widths though.) It's the field name which get cuts off at 10 characters.

    Otherwise I believe you could setup an export file specification which let's you define the widths - not 100% sure though on exporting as a dbase format (read the Access help on that.)

    If it doesn't have to be a dbase format, you could try exporting to an xls format or a txt format (which you can design an export specification for and define the field widths). Why the need for a dbase export format?

    I can't recall in my dbase programming days but if you're importing it to a dbase db, can't you import a txt format? (but it's been many years since my dbase days.)
    Last edited by pkstormy; 03-18-08 at 17:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2008
    Posts
    6
    In access I can't specify the size of a numeric field as 15.2, always appears as 20.5. In the string field i have no problem.

    I need these sizes are fixed because are the formats used by the state bank.

    thanks

Posting Permissions

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