4 Replies - 908 Views - Last Post: 07 May 2013 - 12:58 PM Rate Topic: -----

#1 404notfound  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 01-November 12

Importing data from access database with arguments.

Posted 07 May 2013 - 12:02 PM

I have a project at work in which I need to export from our current database and change it to the new database. This is what I've got so far:

the code comes out of my system horizontally.
AcctNum,SDI,Medicare,State,Fed,TotWages

1234,50,86,26,13,5000


I've exported it from my software, imported it into Access, and hooked Access up to a datagridview.
Now needs to have codes inserted for each type and tax, and be exported to excel looking like:

AcctNum,type,code,deduction

so...
1234,T,DD,50
1234,T,FW,86
...etc



I either need to convert it on the import from access into the datagridview, OR I need to change it on its way out to access. Either way I'm stuck. This is what I've got so far.


Public Class Form1


    Dim dbProvider As String = "PROVIDER=microsoft.ace.oledb.12.0;"
    Dim dbSource As String = "Data Source = C:\D&R\Data\DR.accdb"
    Dim conn As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String
    Dim query As String = "SELECT AcctNum,SDI,Medicare,State,Fed,TotWages FROM Earnings INNER JOIN Personel On Earnings.SS=Personel.SS"

    Dim command As New OleDb.OleDbCommand(query, conn)


    Private Sub connect()
        conn.ConnectionString = dbProvider & dbSource
        conn.Open()
        MsgBox(conn.State.ToString)
    End Sub



    Private Sub dgPopulate()
        connect()
        da.SelectCommand = command
        da.Fill(ds)
        Dim dt As New DataTable
        da.Fill(dt)

        Me.dgEmployees.DataSource = dt
        conn.Close()
    End Sub

    Private Sub export()
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")


        For i = 0 To dgEmployees.RowCount - 2
            For j = 0 To dgEmployees.ColumnCount - 1
                For k As Integer = 1 To dgEmployees.Columns.Count
                    xlWorkSheet.Cells(1, k) = dgEmployees.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = dgEmployees(j, i).Value.ToString()
                Next
            Next
        Next

        xlWorkSheet.SaveAs("C:\users\fantus\documents\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("your report is in documents\vbexcel.xlsx")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        dgPopulate()
    End Sub

    Private Sub ExportToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles ExportToolStripMenuItem.Click
        export()
    End Sub
End Class



Is This A Good Question/Topic? 0
  • +

Replies To: Importing data from access database with arguments.

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3224
  • View blog
  • Posts: 10,830
  • Joined: 12-December 12

Re: Importing data from access database with arguments.

Posted 07 May 2013 - 12:16 PM

So does your code work? If not, what errors do you receive?
If it does work, what does the output to Excel look like?

Quote

1234,T,DD,50
1234,T,FW,86

It appears that you want to create two rows in Excel for each row in Access (in the DGV) with T, DD and T, FW added to the first and second (odd and even) rows?

This post has been edited by andrewsw: 07 May 2013 - 12:17 PM

Was This Post Helpful? 0
  • +
  • -

#3 404notfound  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 01-November 12

Re: Importing data from access database with arguments.

Posted 07 May 2013 - 12:39 PM

andrew,

Using:
AcctNum,SDI,Medicare,State,Fed,TotWages

1234,50,86,26,13,5000


As an example, I need one line per sdi, medicare, state, fed, and total.

the finished project needs to look like:
AcctNum,$,01,TotWages,Total
AcctNum,T,DD,SDI,Total
AcctNum,T,MC,Medicare,Total
AcctNum,T,ST,State,Total
AcctNum,T,FW,Fed,Total



It exports to excel exactly how it looks in the dgv, in the one-line-per-employee format.
Is that a better example?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3224
  • View blog
  • Posts: 10,830
  • Joined: 12-December 12

Re: Importing data from access database with arguments.

Posted 07 May 2013 - 12:55 PM

Well, I haven't attempted to decipher precisely where you are obtaining, and positioning values, but you would still use i and j to loop through the rows and columns of the dgv, then use Cells() in Excel to position the values where you need them.

Create a separate counter variable and use this within Cells(counter + .. and just increase this counter by 5 each time, to offset the next block of data.

This post has been edited by andrewsw: 07 May 2013 - 12:58 PM

Was This Post Helpful? 0
  • +
  • -

#5 404notfound  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 01-November 12

Re: Importing data from access database with arguments.

Posted 07 May 2013 - 12:58 PM

I need to add that the:
$,01 is the code for wages,
T,DD is for sdi, and etc.


and taking the example of 1234,50,86,26,13,5000, it should look like;

1234,$,01,5000,5000
1234,T,DD,50,50
1234,T,MC,86,86
1234,T,ST,26,26
1234,T,FW,13,13

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1