SQL to ACCESS via VB.NET/ADO.NET

  • Thread starter Thread starter Marc Scirri
  • Start date Start date
M

Marc Scirri

I have populated a data table (dtHeader) from a SQL database using a SQL
Data Adapter. I have an Access table (header) with the same fields as the
SQL table. I want to populate the Access table with the data in the data
table. The data table contains information from multiple SQL tables with teh
same schema.

I am currently attempting to run an InsertCommand from an OleDB DataAdapter
for each row of the data table to get teh data into Access 1 row at a time.
I ahve to do this for 5 tables andit is going to be a lot of code to create
and assign values to all of the parameters.

Is there an easier way to populate the Access table, with all of the data
tables rows at once even?

----------------------------------------



Dim sdaROW as new SqlClient.SqlDataAdapter, cmdSQL as New
SqlClient.SqlCommand
Dim conOLE as new Data.OleDb.OleDbConnection, cmdOLE as new
OleDb.OleDbCommand
Dim odaROW as new OleDb.OleDbDataAdapter, odrROW as OleDb.OleDbDataReader

conOLE.ConnectionString = sConString
conOLE.Open()

' Populate the data table with the SQL data
sdaROW.SelectCommand = new SqlClient.SqlCommand

with sdaROW

.SelectCommand.Connection = scnFD
.SelectCommand.CommandText = "SELECT * FROM " & sSelectSQL
.Fill(dtHeader)
.SelectCommand = nothing

end with


sdaROW = nothing

odaROW.InsertCommand = new OleDb.OleDbCommand
odaROW.InsertCommand.Connection = conOLE
odaROW.InsertCommand.CommandText = "INSERT INTO header (guid, parentguid)
VALUES (?,?)"


odaROW.InsertCommand.Parameters.Add("guid",OleDb.OleDbType.Guid,64,"guid")
odaROW.InsertCommand.Parameters.Add("parentguid",OleDb.OleDbType.Guid,64,"pa
rentguid")

for iCounter = 0 to dtHeader.Rows.Count - 1

odaROW.InsertCommand.Parameters.Item("guid") =
dtHeader.Rows(iCounter).Item("guid")
odaROW.InsertCommand.Parameters.Item("parentguid") =
dtHeader.Rows(iCounter).Item("parentguid")
odaROW.InsertCommand.ExecuteNonQuery()

next iCounter
 
Hi Marc,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to put data from several SQL
Server tables to an Access table. If there is any misunderstanding, please
feel free to let me know.

To achieve this, there are many ways. Here I listed some of them.

1. The simplest way to do this, is to use the Data Transformation Service
(DTS) in SQL Server. DTS in SQL Server 2000 provides a set of graphical
tools and programmable objects to help administrators and developers solve
data movement problems, including the extraction, transformation, and
consolidation of data from disparate sources to single or multiple
destinations. Sets of tasks, workflow operations, and constraints can be
collected as DTS packages that can be scheduled to run periodically or when
certain events occur. This white paper introduces DTS, shows some of the
components and services that can be used to create DTS solutions,
illustrates the use of DTS Designer to implement DTS solutions, and
introduces DTS application development.

Here is a link for more information about DTS.

http://msdn.microsoft.com/data/technologyinfo/sqlserver/SQLKeyTechs/default.
aspx?pull=/library/en-us/dnsql2k/html/dts_overview.asp

2. Since you have 5 tables to merge, you can also use 5 SqlDataAdapters to
fill the data into one DataTable and use another OleDbDataAdapter to update
the data in the DataTable to the Access file. When filling the DataTable,
please make sure to set the AcceptChangesDuringFill property of the
SqlDataAdapter to false. It will make all the filled rows' RowState
property to Added, then the update method will work properly.

Here is a code snippet. However it can only be used when there is no
primary key in the table.

sda1.AcceptChangesDuringFill = False
sda2.AcceptChangesDuringFill = False
sda3.AcceptChangesDuringFill = False
sda4.AcceptChangesDuringFill = False
sda5.AcceptChangesDuringFill = False

sda1.Fill(dtHeader) 'Put all the data in a single table.
sda2.Fill(dtHeader)
sda3.Fill(dtHeader)
sda4.Fill(dtHeader)
sda5.Fill(dtHeader)

Dim conOLE As New Data.OleDb.OleDbConnection
Dim odaROW As New OleDb.OleDbDataAdapter

conOLE.ConnectionString = sConString

odaROW.InsertCommand = New OleDb.OleDbCommand
odaROW.InsertCommand.Connection = conOLE
odaROW.InsertCommand.CommandText = "INSERT INTO header (guid,
parentguid) VALUES (?,?)"


odaROW.InsertCommand.Parameters.Add("guid", OleDb.OleDbType.Guid,
64, "guid")
odaROW.InsertCommand.Parameters.Add("parentguid",
OleDb.OleDbType.Guid, 64, "parentguid")

odaROW.Update(dtHeader)

For more information about OleDbDataAdapter.Update method, please check the
following link for reference.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatacommondbdataadapterclassupdatetopic.asp

HTH. Does this answer your question? If anything is unclear, please feel
free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
When I did this I received an error reading "Parameter _1 does not have a
default value". I know that all of the values for that parameter are NULL
for this table. I looked for a way to set the default value for a parameter
but only found the VALUE property.

I currently am populating the tables row by row. I have created functions
that build the DataTable objects based on the structure of the SQL table(s)
that the data is being drawn from. I then build the Access tables 'on the
fly' based on the structure of the DataTable. I then cycle through each row
of the data table, assign the values of that row to the parameter and run
the InsertCommand.ExecuteNonQuery(). It is only a few more steps because I
was able to write functions that loop through the columns of the data table
to create and populate the parameters generically.

If you know how to handle the error mentioned above that would be great. I
was able to get my code working taking a slightly longer route but I would
like to be able to handle this in the future. I am still pretty new to .NET
programming and want to learn everything that I can.

Thanks again for all of your help.
 
Hi Marc,

I'm not quite sure why the program throws such an exception says "Parameter
_1 does not have a default value". As you can see, in the code snippet, I
didn't add parameters named "Parameter_1". Could it be a parameter in
another DbCommand that hasn't been assigned a value? If you have trouble to
debug, could you please paste the code here?

You can also try my suggestion to use DTS. It draws data from SQL Server to
Access directly without dumping data to local memory. So it's faster and
more reliable than using DataTable.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Marc,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top