Results 1 to 13 of 13

Thread: Help exporting a DBF from Access using VB6

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    12

    Unhappy Help exporting a DBF from Access using VB6

    OK, hoping someone can help me out on this one....

    I have an Access database (DataBaseName) which contains a table (TABLENAME) that I need to export to a .dbf file ..... on a machine that will not have Access installed....

    I'm currently using the below code.

    VB Code:
    1. Dim appAccess As access.Application
    2.         Set appAccess = CreateObject("Access.Application")
    3.         appAccess.OpenCurrentDatabase DataBaseName
    4.         appAccess.DoCmd.TransferDatabase acExport, "dBase IV", _
    5.                         Mid(DataBaseName, 1, InStrRev(DataBaseName, "\")), _
    6.                         acTable, "TABLENAME", "TABLENAME", False
    7.         ' Close the database
    8.     appAccess.CloseCurrentDatabase


    This works fine on machines with MS Ofiice/Access but doesn't work on the machine without access.....

    So.... does anyone know of another way do do, it or how to make it work using VB6 ??????

    Any help most appreciated on this as I'm currently going round in circles!!

    Cheers

    Steve

  2. #2
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: Help exporting a DBF from Access using VB6

    You cant "run" access on a machine that doesnt have access...
    which is essentially what u are doing.

    what if u turn the DB into into and MDE file.. then try that code with it...

    i dont thin it will work.. but cant hurt to try
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Help exporting a DBF from Access using VB6

    Welcome to VBForums!

    Static is right that you can't use the method you have shown, you can however connect to the database file using ADO/DAO/etc. If you create two connections (one to Access, and one to dBase) you can simply copy the data.

    An example of ADO can be found in the ADO Tutorial (link below) in the DB forum.

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    12

    Re: Help exporting a DBF from Access using VB6

    Thanks for the replies guys, much appreciated!

    Static - yup I know why I can't do what I was trying w/o access installed.... Cheers for the suggestion tho...but not gonna work in this case - converting the db's not an option....

    But Si , like that idea! I can read out the table easy into a recordset using SQL & existing ADO connection that I use for updates/queries.. & I know how to write this out to a text file (e.g. I could write it out to .csv ) or back to another existing Access or Oracle DB... but I need to create a new export file in dbf format...(not write into an exisitng dBase DB) ... Can I do this via ADO ??
    Cheers
    Steve
    Last edited by SteveW282; Feb 22nd, 2006 at 04:34 PM.

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    12

    Re: Help exporting a DBF from Access using VB6

    Hehe !! Gottit I think!

    Right , create the first export .dbf manually on a machine with Access.
    For future exports use Filecopy to create a copy of this original.dbf
    Create a ADO dbase connection to this copy_of_original.dbf,
    Use execute/SQL/truncate table to empty it
    then refill table with the data from the Access table!! Wheyhey!! I hopefully will end up with a 'new' export of the Access table in .dbf format!

    I think that's along the lines you were meaning...if there's a better way I'm all ears!

    I'll post the code when I get it working (or not....) tomorrow!! Cheers for setting me in the right direction!!

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Help exporting a DBF from Access using VB6

    That's almost exactly the way that I would have suggested

    The only difference is that I would use a blank .dbf file if possible - but I suspect that it needs some data in order to exist, so your idea is spot on

  7. #7
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961

    Re: Help exporting a DBF from Access using VB6

    I suspect the creation of a new .DBF is possible using ADO. I have done it with .XLS and am close to doing it with .MDB
    Nobody knows what software they want until after you've delivered what they originally asked for.

    Don't solve problems which don't exist.

    "If I had eight hours to cut down a tree, I'd spend six hours sharpening my axe." --- Abraham Lincoln (1809-1865)

    2 idiots don't make a genius.

  8. #8

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    12

    Resolved Re: Help exporting a DBF from Access using VB6

    Right, just in case anyone else is ever needing to do this, the final tested code:

    VB Code:
    1. Dim DBaseFile As New ADODB.Connection
    2. Dim rsDBase As New ADODB.Recordset
    3. Dim LASIdbAs New ADODB.Connection
    4. Dim rsLASI As New ADODB.Recordset
    5. Dim DBaseConnectArgue As String
    6. Dim ConnectArgue  as string
    7.  
    8. ' copy the template dbase file
    9. FileCopy App.Path & "\LASI_SUMMLAS_Template.DBF", App.Path & "\SUMMLAS.dbf"
    10.  
    11. ' connection to the Dbase file
    12. Set DBaseFile = CreateObject("ADODB.connection")
    13. DBaseConnectArgue = "Driver={Microsoft dBASE Driver (*.dbf)};DBQ=" & App.Path & ";DriverID=277"
    14. DBaseFile.Open DBaseConnectArgue
    15.  
    16. ' empty data out of the .dbf if its not already empty
    17. SQLSTR = "select * from SUMMLAS.dbf"
    18. rsDBase.Open SQLSTR, DBaseFile, adOpenStatic
    19. If rsDBase.RecordCount > 0 Then
    20.     SQLSTR = "Delete * from SUMMLAS.dbf"
    21.     DBaseFile.Execute SQLSTR
    22. End If
    23.  
    24. ' connection to LasiDB and read data from SUMMLAS view into rsLASI recordset
    25. ConnectArgue = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\lasi.msa" & ";"
    26. Set LASIDb = CreateObject("ADODB.connection")
    27. LASIDb.Open ConnectArgue
    28. SQLSTR = "SELECT * FROM SUMMLAS"
    29. rsLASI.Open SQLSTR, LASIDb, adOpenStatic
    30. ' insert data from lasi.msa into the dbf
    31. If rsLASI.RecordCount > 0 Then
    32.     While Not rsLASI.EOF
    33.         SQLSTR = "INSERT INTO SUMMLAS.dbf (UWI, LINK_VALUE, COUNTS) values ('" & rsLASI.Fields(0).Value & "','" & rsLASI.Fields(1).Value & "','" & rsLASI.Fields(2).Value & "')"
    34.         DBaseFile.Execute SQLSTR
    35.         rsLASI.MoveNext
    36.     Wend
    37. End If
    38.  
    39. ' close recordsets & connections
    40. rsLASI.Close
    41. rsDBase.Close
    42. LASIDb.Close
    43. DBaseFile.Close

    Cheers for the pointers guys

    Steve



  9. #9
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961

    Re: Help exporting a DBF from Access using VB6

    That's cheating. I am doing it without any original files. Can't you create brand-spanking new DBFs without using existing files?
    Nobody knows what software they want until after you've delivered what they originally asked for.

    Don't solve problems which don't exist.

    "If I had eight hours to cut down a tree, I'd spend six hours sharpening my axe." --- Abraham Lincoln (1809-1865)

    2 idiots don't make a genius.

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    12

    Re: Help exporting a DBF from Access using VB6

    hehe, I know it's cheating (or a workaround as I'd prefer to call it....)

    I had wanted to do it without needing existing files (...that was my original question) - as I had been using a call to Access to create the export dbf which failed when the tool was run on a server that didn't have MS Access installed..)

    Any better suggestions then ?? I don't know how to create new dbf via ADO.....

    Steve

  11. #11
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961

    Re: Help exporting a DBF from Access using VB6

    Nobody knows what software they want until after you've delivered what they originally asked for.

    Don't solve problems which don't exist.

    "If I had eight hours to cut down a tree, I'd spend six hours sharpening my axe." --- Abraham Lincoln (1809-1865)

    2 idiots don't make a genius.

  12. #12
    Addicted Member
    Join Date
    Feb 2006
    Posts
    208

    Re: Help exporting a DBF from Access using VB6

    is your problem solved??, i've done this before....

    alexis23

  13. #13

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    12

    Re: Help exporting a DBF from Access using VB6

    Hi Alexis,

    yes, well I've got the above solution that does the job .... i.e. I can get my dbf exports without requiring Access installed...

    But.. if you know a neater way e.g. how to create a new dbf via ADO I'm all ears ..!
    Cheers
    Steve

Posting Permissions

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



Click Here to Expand Forum to Full Width