Copy Excel Data into Access Database Table.
-
Hi All, I have one Excel sheet "Student.xls", which has different worksheet as "Sheet1","Sheet2"..like that. Consider now "Sheet1"; it has 3 columns - "Name", "Age", "Sex". All these columns has row entries. Now I have one access database, "alldata.mdb". It has One table "Record" which has 3 fields as same as column name of Excel sheet-- "Name", "Age", "Sex". I want to populate this access database with the entries of Excel sheet from "Sheet1". Can someone let me know how to do this? I tried this: // first connected xl datasheet: Dim filename As String = "C:\Student.xls" Dim con As String con = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 8.0;" Dim connection As OleDbConnection = New OleDbConnection(con) connection.Open() The above Excel connection was succesful, but how to import the data from this excel sheet into the access database. Please suggest. Thanks, R.S.
-
Hi All, I have one Excel sheet "Student.xls", which has different worksheet as "Sheet1","Sheet2"..like that. Consider now "Sheet1"; it has 3 columns - "Name", "Age", "Sex". All these columns has row entries. Now I have one access database, "alldata.mdb". It has One table "Record" which has 3 fields as same as column name of Excel sheet-- "Name", "Age", "Sex". I want to populate this access database with the entries of Excel sheet from "Sheet1". Can someone let me know how to do this? I tried this: // first connected xl datasheet: Dim filename As String = "C:\Student.xls" Dim con As String con = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 8.0;" Dim connection As OleDbConnection = New OleDbConnection(con) connection.Open() The above Excel connection was succesful, but how to import the data from this excel sheet into the access database. Please suggest. Thanks, R.S.
Select * FROM [Sheet1$] or however your query looks like. Enter the data into a dataset. Open a new connection to the alldata.mdb and copy the data from the dataset.
"When did ignorance become a point of view" - Dilbert
-
Select * FROM [Sheet1$] or however your query looks like. Enter the data into a dataset. Open a new connection to the alldata.mdb and copy the data from the dataset.
"When did ignorance become a point of view" - Dilbert
Hi, I have copied the data in a new dataset "ds1". Please tell me how to copy this "ds1" contents into Access Database. I am trying with following: Dim da2 As New OleDbDataAdapter Dim conn As OleDbConnection Dim cmd As OleDbCommand = New OleDbCommand() conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\alldata.mdb;") cmd = New OleDbCommand("SELECT * FROM Denver", con) da2 = New OleDbDataAdapter(cmd) da1.Fill(ds1, "Denver") But not able to populate the excel sheet. Can You guide me how to do this?? Thanks, R.S.
-
Hi, I have copied the data in a new dataset "ds1". Please tell me how to copy this "ds1" contents into Access Database. I am trying with following: Dim da2 As New OleDbDataAdapter Dim conn As OleDbConnection Dim cmd As OleDbCommand = New OleDbCommand() conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\alldata.mdb;") cmd = New OleDbCommand("SELECT * FROM Denver", con) da2 = New OleDbDataAdapter(cmd) da1.Fill(ds1, "Denver") But not able to populate the excel sheet. Can You guide me how to do this?? Thanks, R.S.