How do I insert data from "table in SQL Server" to "table in Access"

[titanotam]

Hi,
I need some code to Insert data from table in SQL to table in Access.
I'm using this code, but there is an error that I don't know how to fix, please show me how:

Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data
Public Class ConvertDB
    Inherits System.Windows.Forms.Form
    Private cnn As New SqlClient.SqlConnection
    Private cmd As New SqlCommand
    Private ConnectionString As String
    Private sql As String
    Private str As String
    Private cmdd As New OleDbCommand
    Private iCount As Integer

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ConnectionString = "Server=ASUS;database=Titscompany;integrated security=SSPI;"

        MsgBox("Connection established")

        sql = "SELECT TEST_ID, TEST_NAME, TEST_DT, TEST_TYPE FROM TEST"
        Try
            cnn = New SqlConnection(ConnectionString)


            cnn.Open()
            cmd = New SqlCommand(sql, cnn)
            cmd.ExecuteNonQuery()

            cnn.Close()
            MsgBox("ExecutionNonQuery in SqlCommand executed")

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEST.mdb")
            cn.Open()

            str = "insert into TEST(TEST_ID, TEST_NAME, TEST_DT, TEST_TYPE) values (@TEST_ID, @TEST_NAME, @TEST_DT, @TEST_TYPE)IN 'D:\TEST.mdb' "


            'IN 'D:\TEST.mdb'
            cmdd = New OleDbCommand(str, cn)
            iCount = cmdd.ExecuteNonQuery
            cn = Nothing
            MsgBox(iCount)


        Catch ex As Exception

            MsgBox("Error:  " & ex.Source & "  " & ex.Message)

        End Try

    End Sub

The Button1.Click function is OK, but the Button2.Click catch an error "Microsoft Jet Database Engine Missing Semicolon ( at end of SQL statement"
Please help me.

[jmcilhinney]

Did you even read the error message? It tells you exactly what the problem is. You're missing a semicolon at the end of your SQL statement. It doesn't get much clearer than that.

[titanotam]

Did you even read the error message? It tells you exactly what the problem is. You're missing a semicolon at the end of your SQL statement. It doesn't get much clearer than that.

yeah, i know the error, but when i put the ";" at the end of statement, this error still appear

[titanotam]

my fix:

str = "insert into TEST(TEST_ID, TEST_NAME, TEST_DT, TEST_TYPE) values (@TEST_ID, @TEST_NAME, @TEST_DT, @TEST_TYPE)IN 'D:\TEST.mdb'; "

What wrongs?

[jmcilhinney]

I must have missed where you told us that you'd already tried that and it didn't work. We only know what you tell us.

Why do you have that IN clause in the SQL code anyway? You're already connected to that database so where else would the data get inserted?

Also, why call ExecuteNonQuery on the SqlCommand? You're executing a SELECT statement. That's a query, isn't it?

Also, your Access SQL has got several parameters in it but you never set the values of those parameters, so no data can get inserted.

What you should be doing is using a SqlDataAdapter to retrieve the data from SQL Server into a DataTable, then using an OleDbDataAdapter to save the contents of that DataTable to Access. I suggest that you follow the Database FAQ link in my signature and check out some of the ADO.NET resources there. One of them is a CodeBank thread of mine that includes various example that have everything you need. There's an example of how to populate a DataTable and an example of how to save a populated DataTable. The only extra thing you need to know is to set the first DataAdapter's AcceptChangesDuringFill property to False.

[titanotam]

ok i'll try, thanks

[titanotam]

Hi, my code is ok

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim i As Integer

        Try
            Dim ConnectionString As String = "Server=ASUS;database=Titscompany;integrated security=SSPI;"
            Dim sqlDA As New SqlDataAdapter
            Dim Connection As New SqlConnection
            Connection.ConnectionString = ConnectionString
            Connection.Open()
            Dim Command As New SqlCommand("SELECT * FROM TEST", Connection)
            sqlDA.SelectCommand = Command
            sqlDA.Fill(table)
            MsgBox("Number of rows inserted = " & table.Rows.Count, , "Warning")
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try


        Try
            Dim AccessConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEST.MDB;"
            Dim AccDA As New OleDbDataAdapter
            Dim AccConnection As New OleDbConnection
            AccConnection.ConnectionString = AccessConnectionString
            AccConnection.Open()
            Dim InsertCommand = New OleDbCommand
            InsertCommand.Connection = AccConnection
            For i = 0 To table.Rows.Count - 1
                InsertCommand.CommandText = "INSERT INTO TEST (TEST_ID,TEST_NAME,TEST_TYPE) VALUES ('" & table.Rows(i)("TEST_ID") & "','" & table.Rows(i)("TEST_NAME") & "','" & table.Rows(i)("TEST_TYPE") & "')"
                InsertCommand.ExecuteNonQuery()
            Next
            MsgBox("Done")
            table.Reset()
            AccConnection.Close()
        Catch ex As Exception
            Dim AccessConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEST.MDB;"
            Dim AccDA As New OleDbDataAdapter
            Dim AccConnection As New OleDbConnection
            AccConnection.ConnectionString = AccessConnectionString
            AccConnection.Open()
            Dim UpdateCommand = New OleDbCommand
            UpdateCommand.Connection = AccConnection
            For i = 0 To table.Rows.Count - 1
                UpdateCommand.CommandText = "UPDATE TEST SET TEST_NAME = ['" & table.Rows(i)("TEST_NAME") & "'] ,TEST_TYPE = ['" & table.Rows(i)("TEST_ID") & "'] WHERE TEST_ID='" & table.Rows(i)("TEST_ID") & "'"
                UpdateCommand.ExecuteNonQuery()
            Next
            MsgBox("Done")
            table.Reset()
            AccConnection.Close()
        End Try
    End Sub

The InsertCommand works correctly, but the UpdateCommand get this error:

Exception has been thrown by the target of an invocation.

Please help me

[jmcilhinney]

You said that you need to insert data. Why are you trying to update data all of a sudden? Please provide a FULL and CLEAR description of EXACTLY what you're trying to achieve. If we don't know what problem we're trying to solve then we probably won't be able to solve it.

[titanotam]

You said that you need to insert data. Why are you trying to update data all of a sudden? Please provide a FULL and CLEAR description of EXACTLY what you're trying to achieve. If we don't know what problem we're trying to solve then we probably won't be able to solve it.

In my code, when I click the button, the InsertCommand will Insert the values from TEST(SQL) to TEST(ACCESS).
But, when I click the button once more times, the InsertCommand causes an error "The values are duplicate", so that it will run the UpdateCommand to update the values in TEST(ACCESS)

Sorry for my bad English

[jmcilhinney]

You didn't say anything about updating existing data to begin with. You said that you needed to insert, implying new data. If you don't provide the whole story then we can't provide a proper solution.

So, again, please provide a FULL and CLEAR description of EXACTLY what you're trying to achieve. How is the data identified as new or existing in the Access database? Are the PK values from SQL Server being inserted into the Access table? Do you know for a fact that ALL the data will be either new or existing, or could it be a mixture of both? We need the WHOLE story in order to understand the actual problem we're trying to solve.

[titanotam]

You didn't say anything about updating existing data to begin with. You said that you needed to insert, implying new data. If you don't provide the whole story then we can't provide a proper solution.

So, again, please provide a FULL and CLEAR description of EXACTLY what you're trying to achieve. How is the data identified as new or existing in the Access database? Are the PK values from SQL Server being inserted into the Access table? Do you know for a fact that ALL the data will be either new or existing, or could it be a mixture of both? We need the WHOLE story in order to understand the actual problem we're trying to solve.

Ok, sorry I will show you
I'm making the "Time Keeping Program". The program manages the work-time of the employees. I created the table HR_EMP_WORKTIME_EVAL to store the work-time

CREATE TABLE [HR_EMP_WORKTIME_EVAL] (
	[EMPLOYEE_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[WORK_DT] [datetime] NOT NULL ,
	[SECTION_ID] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WORK_PLACE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WORK_TYPE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PROJECT_ID] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WORK_START_TM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WORK_END_TM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WORK_TM] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_HR_EMP_WORKTIME_EVAL_WORK_DUR] DEFAULT (0),
	[OVER_TM] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_HR_EMP_WORKTIME_EVAL_OT_DUR] DEFAULT (0),
	[OT_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BREAK_TM] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_HR_EMP_WORKTIME_EVAL_BREAK_TM] DEFAULT (0),
	[ABSENT_ANN] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_HR_EMP_WORKTIME_EVAL_ABSENT_ANN] DEFAULT (0),
	[ABSENT_INS] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ABSENT_SAL] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_HR_EMP_WORKTIME_EVAL_ABSENT_SAL] DEFAULT (0),
	[ABSENT_NO_SAL] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_HR_EMP_WORKTIME_EVAL_ABSENT_NO_SAL] DEFAULT (0),
	[POISON_YN] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NOTE] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PROCESS_YN] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_HR_EMP_WORKTIME_EVAL_PROCESS_YN] DEFAULT ('N'),
	[PROCESS_DT] [datetime] NULL ,
	[AUDIT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AUDIT_DT] [datetime] NULL ,
	[LUNCH_BENEFIT] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DINNER_BENEFIT] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TOXIC_BENEFIT] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PROJECT_BENEFIT] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PROCESS_NOTE] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_HR_EMP_WORKTIME_EVAL] PRIMARY KEY  CLUSTERED 
	(
		[EMPLOYEE_ID],
		[WORK_DT]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Everyday, the Boss uses this program to manage employees work-time. At the end of the day, the program auto Insert data from HR_EMP_WORKTIME_EVAL table to WORKTIME_STORE (with the same columns) table in ACCESS database for storing data.

If the table WORKTIME_STORE is empty, the InsertCommand inserts the values from HR_EMP_WORKTIME_EVAL exactly.

But when my Boss changes some old values in HR_EMP_WORKTIME_EVAL table by Update statement in SQL. How can the WORKTIME_STORE table update the values?

So that I made the UpdateCommand to do it, but it causes error

You know me? Sorry for my bad English

[jmcilhinney]

You didn't really answer my questions. Will the data always be all new or all existing or will it be a mix of the two sometimes?

[titanotam]

You didn't really answer my questions. Will the data always be all new or all existing or will it be a mix of the two sometimes?

For example, if I have 50 employees, today I have 50 records in HR_EMP_WORKTIME_EVAL table. Tomorrow, I will have 100 records (50 today + 50 tomorrow)
BUT, tomorrow, the Boss can change some values within 50 records of today, and the WORKTIME_STORE table also update the change

[jmcilhinney]

So, the answer is that it can be a mix of both. In that case you can't just insert all or update all, can you? Your approach is bound to fail because you're trying to insert every record and, as soon as you get any failure, you try to update every record. That's not what you want to do. What you want to do is know which records are new and which ones are existing and either insert or update accordingly.

The way to do that is to first query the Access database to get all the IDs of all the existing records. You would then loop through the rows of your DataTable and check whether the ID of that row is in the list of existing IDs or not. If it is then you'd call SetModified on that row, otherwise you'd call SetAdded. You now have a DataTable where every row has a RowState of either Added or Modified. You can now use a DataAdapter to save the whole table to the database as a batch. Your adapter would have an appropriate InsertCommand and UpdateCommand and each row will then be either inserted or updated automatically.

You can also forget about setting the AcceptChangesDuringFill property I mentioned earlier.

[titanotam]

So, the answer is that it can be a mix of both. In that case you can't just insert all or update all, can you? Your approach is bound to fail because you're trying to insert every record and, as soon as you get any failure, you try to update every record. That's not what you want to do. What you want to do is know which records are new and which ones are existing and either insert or update accordingly.

The way to do that is to first query the Access database to get all the IDs of all the existing records. You would then loop through the rows of your DataTable and check whether the ID of that row is in the list of existing IDs or not. If it is then you'd call SetModified on that row, otherwise you'd call SetAdded. You now have a DataTable where every row has a RowState of either Added or Modified. You can now use a DataAdapter to save the whole table to the database as a batch. Your adapter would have an appropriate InsertCommand and UpdateCommand and each row will then be either inserted or updated automatically.

You can also forget about setting the AcceptChangesDuringFill property I mentioned earlier.

Thanks, you know me.
I want to ask you 1 question more, I have 2 solutions for my program:
1) Like your direction
2) At the end of tomorrow, the program will clear all 50 records of old WORKTIME_STORE table and Insert 100 records. Can this way faster?

[jmcilhinney]

Either will do the job and, with so few records, I doubt performance would vary enough to even notice.

[titanotam]

Either will do the job and, with so few records, I doubt performance would vary enough to even notice.

Ok, i'll try, thanks