Results 1 to 6 of 6

Thread: Left join access Table and Sql Table

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    3

    Left join access Table and Sql Table

    Hello

    I try to create a left join between an access table and sql table and display the result in dataview

    Final result:
    Nopiece Qte_Bom Supplier

    This is my code:
    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim connectionString As String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=U:\JV23\JV236\JV236I.mdb"
            Dim sql As String = "SELECT Nopiece,Qte_Bom FROM Pere_Fils"
    
            Dim connection As New OleDbConnection(connectionString)
            Dim dataadapter As New OleDbDataAdapter(sql, connection)
            Dim ds As New DataSet()
    
            dataadapter.Fill(ds, "Pere_Fils")
    
            Dim Sql2 As String
            Dim da2 As SqlDataAdapter
    
            Connection_SQL("sa", "SAD", "MAN-APP1.nesting.ca\TDL", "tdl2012sql")
    
            Sql2 = "SELECT Nopiece,Supplier FROM [SAD].[dbo].[Prog_Sigmanest]   where [Job]='JV236I'"
    
            da2 = New SqlDataAdapter(Sql2, cn)
            da2.Fill(ds, "Prog_Sigmanest")
    
            Deconection_SQL()
    
    
            DataGridView1.DataSource = ??????

    Thanks
    Last edited by dday9; Jun 17th, 2021 at 08:53 AM.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    10,335

    Re: Left join access Table and Sql Table

    Hi Jackb223, welcome to VBForums! Keep in mind that your posts will need to go through the moderation queue for the first couple of posts, so please don't double post.

    I've also added code tags to your post. If you want more information on those, please visit the link in my signature.

    Finally, could you please elaborate on your problem? You did good in provided the expected results along with the code you have, but it is still difficult to understand what it is that you're trying to do. I highly recommend visiting this stickied thread: https://www.vbforums.com/showthread....our-hovercraft
    "Code is like humor. When you have to explain it, itís bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,291

    Re: Left join access Table and Sql Table

    The datasource will be the datatable that you filled with the dataadapter. You don't really need a dataset, in this case, because it looks like you only have the one table. A dataset is just a collection of datatables, so what you have is essentially a collection with one item in it. There's nothing particularly wrong with that, it's just a bit more involved than you need.

    So, your datasource could be just this:
    DataGridView1.DataSource = ds.Tables("Prog_Sigmanest")

    Or just:
    DataGridView1.DataSource = ds(0)
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    3

    Re: Left join access Table and Sql Table

    Sorry I was not enough clear
    So I have 1 access database "JV236I.mdb with a table Pere_Fils"
    and I have another SQL database "tdl2012sql with a table Prog_Sigmanest"

    and I want select all item from Pere_Fils left join with Prog_Sigmanest

    If all the table are in the same database , access or sql , I could do select with left join but
    with 2 different database , I don't know

    I hope is more clear

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,442

    Re: Left join access Table and Sql Table

    In this case, a Dataset is ideal... fill one DataTable with the data from the one Access table, fill another Datatable from the SQL Server data... put both in the DataSet... then create a DataRelation (I think that's the right name) between the two that describes the relationship - this creates a left join via code between the two DataTables... with all that you should be able then to create a Dataview that pulls the data from the two DataTables based on the DataRelation.

    DataRelations: https://duckduckgo.com/?q=vb.net+datarelation
    DataViews: https://duckduckgo.com/?q=vb.net+dataview

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    3

    Re: Left join access Table and Sql Table

    thanks

    I will try

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