How do I insert data from "table in SQL Server" to "table in Access"
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:
Code:
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.
Re: How do I insert data from "table in SQL Server" to "table in Access"
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.
Re: How do I insert data from "table in SQL Server" to "table in Access"
Quote:
Originally Posted by
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.
yeah, i know the error, but when i put the ";" at the end of statement, this error still appear
Re: How do I insert data from "table in SQL Server" to "table in Access"
my fix:
Code:
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?
Re: How do I insert data from "table in SQL Server" to "table in Access"
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.
Re: How do I insert data from "table in SQL Server" to "table in Access"
Re: How do I insert data from "table in SQL Server" to "table in Access"
Hi, my code is ok
Code:
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:
Code:
Exception has been thrown by the target of an invocation.
Please help me
Re: How do I insert data from "table in SQL Server" to "table in Access"
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.
Re: How do I insert data from "table in SQL Server" to "table in Access"
Quote:
Originally Posted by
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.
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 :)
Re: How do I insert data from "table in SQL Server" to "table in Access"
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.
Re: How do I insert data from "table in SQL Server" to "table in Access"
Quote:
Originally Posted by
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.
:) Ok, sorry :cool: 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
Code:
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
Re: How do I insert data from "table in SQL Server" to "table in Access"
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?
Re: How do I insert data from "table in SQL Server" to "table in Access"
Quote:
Originally Posted by
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?
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
Re: How do I insert data from "table in SQL Server" to "table in Access"
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.
Re: How do I insert data from "table in SQL Server" to "table in Access"
Quote:
Originally Posted by
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.
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?
Re: How do I insert data from "table in SQL Server" to "table in Access"
Either will do the job and, with so few records, I doubt performance would vary enough to even notice.
Re: How do I insert data from "table in SQL Server" to "table in Access"
Quote:
Originally Posted by
jmcilhinney
Either will do the job and, with so few records, I doubt performance would vary enough to even notice.
Ok, i'll try, thanks
