Results 1 to 9 of 9

Thread: Save query into dbase table?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2006
    Posts
    205

    Question Save query into dbase table?

    In Access I could do this...

    DoCmd.TransferDatabase acExport, "dBase III", "c:\envrpt\", acTable, "MATRIX", "MATRIX"

    How would I go about it in VB.NET?

    Thanks.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Save query into dbase table?

    I have no idea what that would accomplish in Access so I have no idea how to do the same in VB.NET. It makes absolutely no difference what code you used in Access because VB.NET is not Access. All that matters is what you're trying to achieve. Describe that and then anyone with decent VB.NET knowledge can tell you how to achieve it without having to know a thing about Access. I'm guessing that you want to transfer data from one database to another but that is just an educated guess. If you want to use ADO.NET to achieve that then it's too separate operations. There's links in my signature to ADO.NET tutorials that will show you how to get data from a database and save data to a database. There is no reason that they have to be the same database, so that's a transfer.

  3. #3
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Save query into dbase table?

    Something like this...

    Add a reference to MS Access xx.0 Object Library

    VB Code:
    1. Dim oApp As Access.Application
    2. oApp = DirectCast(CreateObject("Access.Application"), Access.Application)
    3. oApp.OpenCurrentDatabase("D:\RobDog888.mdb")
    4. oApp.DoCmd.TransferDatabase(Access.AcDataTransferType.acExport, "dBase III", "c:\envrpt\MyDB3DB.mdf", Access.AcObjectType.acTable, "Table1", "Table1", True)
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Save query into dbase table?

    That's assuming that Access is installed on the system Rob. If the desire is to do this in VB.NEt then there is no guarantee that Access will be present I'm thinking, although I could be quite wrong. If the desire is to use Office Automation then the question should have been posted in the Office Development forum, for future reference. If the desire is to use ADO.NET with no specific dependence on the Access application then that's a different story.

  5. #5
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Save query into dbase table?

    Why all the fuss John? We dont even know if he wanted to use Access or not. I'm just giving one option of many on how to accomplish the same as he was doing before.

    Not every thread gets posted in its apporpriate forum but until more is known on the technology desired to be used then its all just a guess of where to place the thread or how best to help. If a member does not know what options are available for use then they may not be able to make the best possible choice for their particular scenerio.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    May 2006
    Posts
    205

    Re: Save query into dbase table?

    I would prefer not to use Access but as a last resort it could be an option.

    I want to save the recordset resulting from my query as a dbase file (in dbase format).

    I'm not sure how to be more descriptive than that. I provided the Access code because I thought someone with Access knowledge would recognize it and be able to suggest an equivilent in VB.NET.

    I'm providing all of the Access code just because it might shed some more light on what I'm trying to do...

    VB Code:
    1. DoDialog "Save Matrix File As", ".dbf", "dbf", OFN_EXPLORER, sfile, 2, Forms!frmAnalysis!WORKORDER & ".dbf"
    2. If sfile <> "" Then
    3.     DoCmd.SetWarnings False
    4.     On Error Resume Next
    5.     Kill "c:\envrpt\matrix.dbf"
    6.     'On Error GoTo 0
    7.    
    8.     DoCmd.RunSQL "delete * from MATRIX"
    9.     DoCmd.RunSQL "select * into MATRIX from qryMatrixExport"
    10.     DoCmd.TransferDatabase acExport, "dBase III", "c:\envrpt\", acTable, "MATRIX", "MATRIX"
    11.     On Error Resume Next
    12.     Kill sfile
    13.     On Error GoTo 0
    14.    
    15.     FileCopy "c:\envrpt\matrix.dbf", sfile
    16.    
    17.     MsgBox "Export File Created in " & sfile, vbInformation
    18.  
    19. End If

  7. #7
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Save query into dbase table?

    I havent worked with DBase III but to use ADO.NET and make it independant of Access you could connect to the access db and to the db3 file and fill a dataset with the desired data. Then insert it into the dbf file connection.


    VB Code:
    1. 'Connect to your access database
    2.  
    3.         Dim oCnn As New OleDb.OleDbConnection
    4.         Dim oDS As New DataSet
    5.         Dim sCnn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & _
    6.         "\RobDog888.mdb;User Id=admin;Password="
    7.         Dim sSQL As String = "SELECT * FROM Table1;"
    8.         oCnn.ConnectionString = sCnn
    9.         oCnn.Open()
    10.         Dim oDA As New OleDb.OleDbDataAdapter(sSQL, oCnn)
    11.         oDA.Fill(oDS, "Table1")
    12.         oCnn.Close()
    13.  
    14. 'Then connect to your db3 database file and populate from the dataset.
    15.     '...
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    May 2006
    Posts
    205

    Re: Save query into dbase table?

    Quote Originally Posted by RobDog888
    VB Code:
    1. 'Then connect to your db3 database file and populate from the dataset.
    2.     '...
    Thanks for the reply robdog. I guess this is where I am confused. I don't have a db3 database file to connect to. I need to dynamically create one and populate it. With the Access command I provided you could specify the database type you wanted to transfer the database into. The "TransferDatabase" method in Access is a beautiful thing. I was kind of hoping that .NET had something similar where you take a SQL table and convert it into another format.

    Just to recap, these are the steps I want...

    1. Query database (Oracle)
    2. Put those results into a temporary table (if necessary)
    3. Call a "TransferDatabase" type function in .NET to convert that table (or recordset) into db3 format.

    We used to do with with Access (dblink to Oracle) but have moved everything to .NET now.

    Sorry if I wasn't clearer earlier. Thanks.
    Last edited by ferrethouse; Oct 19th, 2006 at 12:51 PM.

  9. #9
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Save query into dbase table?

    Sorry that I cant help more but I dont know and havent used DBIII.
    Perhaps someone with DB III experience/knowledge can jump in here?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

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