Question Writing to a table in a secondary Access database

[ggunter]

I must have missed something pretty basic because I have no idea how to proceed.

I can create a dataset/datatable and populate it. But I have NO idea how to write to a database.

I need to run a query on an Access table and write the results to a table in a different Access database. In VB6, I would have created, and run, a Make Table query on the original database and that would have created the table for me. Can we still do that?

Any suggestions would be greatly appreciated!!

 

[ggunter]

Found the answer on another forum. Also have a question but will ask that on another thread. Here's what I came up with:

        Dim strDest As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<destination dbs>;User Id=admin;Password=;"
        Dim strSource As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<source dbs>;User Id=admin;Password=;"

        Dim sqlSource As String = "SELECT tblAssocInfo.Acid, tblAssocInfo.FirstName, " & _
                "tblAssocInfo.LastName, tblAssocInfo.MgrFull, tblAssocInfo.Bucket, " & _
                "tblAssocInfo.Department " & _
                "FROM(tblAssocInfo) " & _
                "ORDER BY tblAssocInfo.Acid;"
        Dim sqlDest As String = "INSERT INTO tblAssoc ( Acid, FirstName, LastName, MgrFull, Bucket, Department ) " & _
        "VALUES (@acid, @first, @last, @mgr, @bucket, @dept)"

        Dim sourceConn As New OleDbConnection(strSource)
        Dim destConn As New OleDbConnection(strDest)

        sourceConn.Open()
        destConn.Open()

        Dim sourceCmd As New OleDbCommand(sqlSource, sourceConn)
        Dim destCmd As New OleDbCommand(sqlDest, destConn)

        destCmd.Parameters.Add("@acid", OleDbType.Char, 10)
        destCmd.Parameters.Add("@first", OleDbType.Char, 75)
        destCmd.Parameters.Add("@last", OleDbType.Char, 75)
        destCmd.Parameters.Add("@mgr", OleDbType.Char, 155)
        destCmd.Parameters.Add("@bucket", OleDbType.Char, 100)
        destCmd.Parameters.Add("@dept", OleDbType.Char, 100)

        Dim dr As OleDbDataReader = sourceCmd.ExecuteReader

        While dr.Read
            destCmd.Parameters("@acid").Value = dr("Acid")
            destCmd.Parameters("@first").Value = dr("FirstName")
            destCmd.Parameters("@last").Value = dr("LastName")
            destCmd.Parameters("@mgr").Value = dr("MgrFull")
            destCmd.Parameters("@bucket").Value = dr("Bucket")
            destCmd.Parameters("@dept").Value = dr("Department")
            destCmd.ExecuteNonQuery()
        End While

        'clean up
        dr.Close()
        dr = Nothing

        destCmd.Dispose()
        destCmd = Nothing

        sourceCmd.Dispose()
        sourceCmd = Nothing

        destConn.Close()
        destConn.Dispose()
        destConn = Nothing

        sourceConn.Close()
        sourceConn.Dispose()
        sourceConn = Nothing