Last post May 27, 2013 10:14 AM by lucsky8
May 24, 2013 07:27 AM|lucsky8|LINK
Hi i need to take data from a SQL Server table and copy the data in an access 2003.
I am using vb.net
I have Stored procedure that return around 20 000 rows.
I create a Datset that conain all the info from the stored prcedure.
Is there an easy way to copy all the data from my Dataset into my access table.
Right now it working but i am looping throught all my row i'am sure thre a better way of doing that?
Is there a way to do something like :
insert into xxx IN 'Datset or Stored Procedure Name'
Here what i have :
Dim myconnection As SqlConnection
Dim sqlGetEleveFromStaging As SqlDataAdapter
Dim oDs As New DataSet
myconnection = New SqlConnection(ConfigurationManager.ConnectionStrings("xxx").ConnectionString)
sqlGetEleveFromStaging = New SqlDataAdapter("Info", myconnection)
sqlGetEleveFromStaging.SelectCommand.CommandType = CommandType.StoredProcedure
Dim connectionStudDemo As OleDbConnection
connectionStudDemo = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source= " + MapPath("xxx.mdb"))
For a = 0 To oDs.Tables(0).Rows.Count - 1
strSQl = "insert into eleves (xxx) VALUES ('" & oDs.Tables(0).Rows(a).Item("xxx") & "')
May 25, 2013 07:01 AM|luckyforu2006|LINK
why there is a requirement for dataset.
retrive the data from sql using datareader and form insert script of returned data from stored procedure and execute the generated script to access 2003(create connection and then execute)
May 25, 2013 10:45 AM|dplenty|LINK
Do you have the answer now
May 27, 2013 07:11 AM|lucsky8|LINK
Hi i'am not sure what you mean by insert script of returned data?
You mean create datareader for the SP, connect to access and loop the datareader?
Sorry for my bad english
May 27, 2013 08:43 AM|luckyforu2006|LINK
May 27, 2013 08:55 AM|lucsky8|LINK
Hi i had to seprate stored prceure one with about 15 000 and one with 50 000.
First one is list of name
second one a list of their contact up to 4 contact each.
So instead of looping throught 50 000 and 15 000 i combine the 2 SP and did a pivot for the contact.
So instead of looping 65000 i am now looping 15 000 so it much faster now.
Tks for your help!
I just wish SQLBulkCopy would work with acces
May 27, 2013 09:02 AM|luckyforu2006|LINK
your logic will get improved if you do the said changes.
whatever manuipulation you want to do, do it inside stored procedure.
Stored procedure advantages:
1. easy to change the code. no need to upload dll for changes done.
2. precomplied( no network congestion)
3.can be used mutiple times and avoid writing bulky queries in c#.
May 27, 2013 10:14 AM|lucsky8|LINK
My page load when from 130 sec to 50S :)