Insert Records from a DataSet to MS Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following VB.Net code to read an Excel file and it works fine. How
do I take these records and insert into an Access 2000 database table?

gsDBName = "MyFile.xls"
gsProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
gsDBName & "; Extended Properties=Excel 4.0;"
sConn = New System.Data.OleDb.OleDbConnection(gsProvider)
goDA = New System.Data.OleDb.OleDbDataAdapter(sExcelQuery, sConn)
goDS = New System.Data.DataSet
Try
goDA.Fill(goDS)
Catch ex As Exception
MsgBox(Err.Source & vbCr & Err.Description)
End Try
dgExcel.DataSource = goDS
 
Assume you want to use the Automagic features of ADO.NET? If so, you can do
what is highlighted below (NOTE: This can be perf tweaked, but this is a
simple implementation):

If the table already exists in Access, run a SELECT query, with a
DataAdapter against the Access table (returning no rows), like:

1. Select no rows from access (this sets up the dataset the easiest way, ie,
fewest lines of code - there are ways to do this otherwise, but this is a one
line fill of zero rows)

2. Copy the rows from the Excel dataset into this dataset
3. Call update on the DataAdapter

The main place to tweak, sans losing automagic, is getting the DataSet
definition, but this is not necessary unless perf is an issue (some may
disagree with me here).



---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Could you please provide me a sample code?

Cowboy (Gregory A. Beamer) - MVP said:
Assume you want to use the Automagic features of ADO.NET? If so, you can do
what is highlighted below (NOTE: This can be perf tweaked, but this is a
simple implementation):

If the table already exists in Access, run a SELECT query, with a
DataAdapter against the Access table (returning no rows), like:

1. Select no rows from access (this sets up the dataset the easiest way, ie,
fewest lines of code - there are ways to do this otherwise, but this is a one
line fill of zero rows)

2. Copy the rows from the Excel dataset into this dataset
3. Call update on the DataAdapter

The main place to tweak, sans losing automagic, is getting the DataSet
definition, but this is not necessary unless perf is an issue (some may
disagree with me here).



---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
Click to expand...
 
¤ I have the following VB.Net code to read an Excel file and it works fine. How
¤ do I take these records and insert into an Access 2000 database table?
¤
¤ gsDBName = "MyFile.xls"
¤ gsProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
¤ gsDBName & "; Extended Properties=Excel 4.0;"
¤ sConn = New System.Data.OleDb.OleDbConnection(gsProvider)
¤ goDA = New System.Data.OleDb.OleDbDataAdapter(sExcelQuery, sConn)
¤ goDS = New System.Data.DataSet
¤ Try
¤ goDA.Fill(goDS)
¤ Catch ex As Exception
¤ MsgBox(Err.Source & vbCr & Err.Description)
¤ End Try
¤ dgExcel.DataSource = goDS

Wouldn't it be easier to import directly into the Access database from Excel or do you need to make
some changes to the Excel data first?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
I would like to give user a chance to review data before inserting records
into the database.
I would alos like to know how to import excel data directly into the Access
database.

Thanks
 
¤ I would like to give user a chance to review data before inserting records
¤ into the database.
¤ I would alos like to know how to import excel data directly into the Access
¤ database.
¤

Below is an example that will enable you to do this directly:

Public Sub ImportExcelToAccess()

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

Try
AccessConn.Open()

'New table
'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Data] FROM
[Excel 8.0;DATABASE=e:\My Documents\Book10.xls;HDR=NO;IMEX=1].[Sheet1$]", AccessConn)
'Existing table
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Data] (Col1, Col2,
Col3, Col4) SELECT F1, F2, F3, F4 from [Excel 8.0;DATABASE=E:\My
Documents\Book10.xls;HDR=No;IMEX=1].[Sheet1$];", AccessConn)
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
AccessConn.Close()
End Try

End Sub


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top