Click here to Skip to main content
15,310,056 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hope someone could help,

Background: Got an old machine that puts data into an MS access file and we need to import the table into an SQL table, the access database keeps a rolling 3 days of data and then deletes the older data, the idea is to run a task every morning to insert the new records to sql, ignore existing so we can build a graph over the year.

I've extracted data from MS access into a Datatable called "table" and created the column names in an SQL table which matches, i've read about SQL Bulkcopy but all of the samples i've seen are in C# but any code converters error out and im not 100% that they'll do what im after.

Could anyone help out?
Pete

What I have tried:

Dim count As Integer = 0
        Dim table As DataTable = New DataTable
        Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; Data Source='C:\Machine.mdb';User Id=admin; Password=;")
        Dim sqlConnection As New SqlClient.SqlConnection("Data Source=10.75.24.94;Initial Catalog=CTData;User ID=sql;Password=")
        Try
            'Import the Access data
            accConnection.Open()
            Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Slot_Vision_Counters_table", accConnection)
            accDataAdapter.Fill(table)
            accConnection.Close()
            'Export to MS SQL
            For Each row As DataRow In table.Rows
                row.SetAdded()
                count = count + 1
            Next


### Code for inserting the data to SQL?


        Catch ex As Exception
            If accConnection.State = ConnectionState.Open Then
                accConnection.Close()
            End If
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
            MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _
            & ex.ToString)
        End Try
Posted
Updated 27-Nov-18 7:32am
Comments
CHill60 27-Nov-18 8:57am
   
Instead of putting the data into a datatable why not just use a "INSERT INTO SQLtable VALUES ..." statement on the sqlconnection? It's not the fastest but it would work.
Alternatively set up the SQL table as a linked table to the Access database and just use an Access query along the lines of "INSERT INTO SQLTable SELECT ... insert field list ... FROM Slot_vision_counters_table"
peterjames09 27-Nov-18 10:20am
   
Problem is, i'll be importing around 20,000 entries every day from 11 of these machines im just worried of the PC hanging for ages just incase it crashes mid import.
CHill60 27-Nov-18 12:01pm
   
Good point. However you could put controls around it and transactions. Alternatively why not capture the information in the SQL database in the first place and cut out the Access database?

1 solution

   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900