Retrieve data from Excel and store it in Access through VB

Hi,

I am using VB express 2005 to create an interface to retrieve data from Excel spreadsheet then store it into my Access database.

I am able to retrieve data and store it in a dataset then display it in a dataGridView. But I am not sure how can i store the data into an Access database. I tried to connect VB to Access by opening a connection and then create a table in Access. I created a DataGridView and bind it to this table. I can do normal insertion, deletion to the table. But when i change the content of the dataGridView to my spreadsheet by writing some code then do the normal save as usual, the content in the datagrid is not saved to the Access like when we insert one record at a time.

Here is part of my code:

Private m_sConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Documents and Settings\yzhang\My Documents\intern\Sales Analysis Report\Input.xls;" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

Public Sub Retrieve_Records()

Dim conn2 As New OleDbConnection(m_sConn2)

' Dim grid As New DataGrid

conn2.Open()

Dim da As New OleDbDataAdapter("Select * From [Sheet1$]", conn2)

Dim ds As DataSet = New DataSet

Try

da.Fill(ds)

ImportSalesDataGridView.DataSource = ds

ImportSalesDataGridView.DataMember = ds.Tables(0).TableName

Me.Refresh()

Catch OleDbExceptionErr As OleDbException

MsgBox(OleDbExceptionErr.Message, "Access")

End Try

conn2.Close()

End Sub

Private Sub MainForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

Me.Validate()

Me.ImportSalesBindingSource.EndEdit()

Me.ImportSalesTableAdapter.Update(Me.AnalysisDataSet.ImportSales)

End Sub

The updates should be saved on form closing, but it turns out that no change is saved

Please help. Thank you

Best regards,

Basara

Answer this question

Retrieve data from Excel and store it in Access through VB

MyP3uK

Hi,

What i wanted to do is: I have a table in Excel and have some data in it. The table is very simple consisting only 4 fields, distributor, partnumber, date and sales. I want to transfer this table to an Access database using VB code.

I know how to connect to Excel and how to create an Access database. The thing i need to know is how can i store the information from Excel to the table in my Access.

The SQL code i wrote above is not working. If there is a way that does not involve sql but able to acomplish this job, it will be fine too. Could you please show me how can i transfer data from Excel to Access

Thanks a lot.
Blastobi

Well, actually i just need some code that will transfer the data in my Excel spreadsheet to my Access database. I know how to do it in Access, but not sure now can i achieve in VB. Better if i can create that database on demand.

Is it possible

Regards
DannoMontano

Hi

Imports System.Data.OleDb

Public Class Form2

Private m_sConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Documents and Settings\yzhang\My Documents\intern\Sales Analysis Report\Input.xls;" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

Private m_sAnalysis = _

"C:\Documents and Settings\yzhang\My Documents\intern\Analysis\Analysis.mdb"

Private m_sAction As String

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim conn2 As New OleDbConnection(m_sConn2)

' Dim grid As New DataGrid

conn2.Open()

Dim da As New OleDbDataAdapter("Select * From [Sheet1$]", conn2)

'Dim ds As DataSet = New DataSet("Workbooks")

'da.Fill(ds, "try_table")

'Dim ds As DataSet = New DataSet()

Dim ds As DataSet = New DataSet

Try

da.Fill(ds, "ImportSales")

ImportSalesDataGridView.DataSource = ds

ImportSalesDataGridView.DataMember = ds.Tables(0).TableName

MsgBox(ds.Tables(0).TableName)

ImportSalesDataGridView.BackgroundColor = Color.WhiteSmoke

Me.Refresh()

Catch OleDbExceptionErr As OleDbException

MsgBox(OleDbExceptionErr.Message, "Access SQL")

End Try

End Sub

End Class

This is all i have now to retrieve data from Excel, and I am planning to create Access database with a table called ImportSales and copy whatever in the dataset ds now into this table.

Thanks for your patience and your help.

Best regards

Basara
Liebethal

Hi Basara,

I was planning to give you a little at a because it's easy to go down the wrong path with this.

So here is what something that may help: (I'm sorry that the board is going to insert linefeeds where they shouldn't be but here it is.)

First... an ADOX Class....

Public Class AccessTableCreator

' This class is an ADOX class for Access database creation and table creation

'

' This class depends upon two COM MDAC v2.8 References made in Project References:

' Microsoft ActiveX Data Objects 2.8 Library

' Microft ADO V2.8 for DDL and Security

Protected Const cNoConn As String = "No Connection has been Opened."

Public Connection As ADODB.Connection

Public Catalogue As ADOX.Catalog

Public Table As New ADOX.Table

Protected m_databaseOpen As Boolean = False

Public Sub New()

Connection = New ADODB.Connection

Catalogue = New ADOX.Catalog

Table = New ADOX.Table

m_databaseOpen = False

Dim v As New ADOX.Table

End Sub

Public Function OpenDB() As Boolean

' Adox.DeleteTable - Called by Common.DeleteTable

OpenDB = False

Dim connectionstring As String = GetConnectionString()

If Me.ConnectionIsOpen Then

MsgBox("OpenDB - Failed to Establish Connection", MsgBoxStyle.Exclamation, "Connection State is Open")

Exit Function

End If

Try

Connection.Open(connectionstring)

Catalogue.ActiveConnection = Connection

OpenDB = True

m_databaseOpen = True

Catch e As Exception

Dim a = connectionstring.Substring(connectionstring.IndexOf(""""))

MsgBox("OpenDB - Failed to open File: " & a, MsgBoxStyle.Exclamation, e.Message)

End Try

End Function

Public Function CreateTable(ByVal TableName As String, ByVal KeyName As String, ByVal PrimaryKeyDataType As ADOX.DataTypeEnum) As Boolean

' ADOX.CreateTable Called by CreateNewTable

CreateTable = False

If Not Me.ConnectionIsOpen Then

MsgBox("CreateTable - Failed to Create table : " & TableName, MsgBoxStyle.Exclamation, cNoConn)

Exit Function

End If

Table.Name = TableName

If KeyName <> "" Then

Table.Columns.Append(KeyName, ADOX.DataTypeEnum.adInteger)

Table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, KeyName)

End If

Try

Catalogue.Tables.Append(Table)

Catch e As Exception

MsgBox("CreateTable - Failed to Create table : " & TableName, MsgBoxStyle.Exclamation, e.Message)

Exit Function

End Try

Return True

End Function

End Class

Then some calls calls to it.....

Dim a As New AccessTableCreator

Try

a.OpenDB()

CreateNewTable = a.CreateTable(TableName, "RecordNo", ADOX.DataTypeEnum.adInteger)

If CreateNewTable Then Phase += 1

If CreateNewTable Then CreateNewTable = a.CreateColumn("RecordType", ADOX.DataTypeEnum.adVarWChar, 12)

If CreateNewTable Then CreateNewTable = a.CreateColumn("ParentCategoryName", ADOX.DataTypeEnum.adVarWChar, 70) 'text

If CreateNewTable Then CreateNewTable = a.CreateColumn("Name", ADOX.DataTypeEnum.adVarWChar, 80) 'text

If CreateNewTable Then CreateNewTable = a.CreateColumn("Author", ADOX.DataTypeEnum.adVarWChar, 20)

If CreateNewTable Then CreateNewTable = a.CreateColumn("Level", ADOX.DataTypeEnum.adInteger)

If CreateNewTable Then CreateNewTable = a.CreateColumn("URL", ADOX.DataTypeEnum.adVarWChar, 255)

If CreateNewTable Then CreateNewTable = a.CreateColumn("DateCreated1", ADOX.DataTypeEnum.adVarWChar, 27)

If CreateNewTable Then CreateNewTable = a.CreateColumn("DateModified1", ADOX.DataTypeEnum.adVarWChar, 27)

If CreateNewTable Then CreateNewTable = a.CreateColumn("ReferenceCount", ADOX.DataTypeEnum.adInteger)

If CreateNewTable Then CreateNewTable = a.CreateColumn("Document", ADOX.DataTypeEnum.adLongVarBinary) 'Oleobject

Catch e As Exception

MsgBox("Common.CreateCategory Error: " & e.Message, MsgBoxStyle.Information, "Category Creation Error")

If Phase > 0 Then a.DeleteTable(TableName)

Finally

a.CloseConnection()

a.dispose()

End Try
Dariusdm

Hi there,

Thanks for your code, i am able to create my Access database now. More questions coming up now.

I tried to save some information from my dataset to Access and this is my code below, I think there is problem with the sql String.

Public Class Form2

Private m_sAction As String

Private m_sAnalysis = _

"C:\Documents and Settings\yzhang\My Documents\intern\Analysis\Analysis.mdb"

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim conn As New System.Data.OleDb.OleDbConnection( _

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & m_sAnalysis & ";")

conn.Open()

Dim cmd As New System.Data.OleDb.OleDbCommand()

cmd.Connection = conn

cmd.CommandText = "INSERT INTO [ImportSales] IN 'C:\Documents and Settings\yzhang\My Documents\intern\Analysis\Analysis.mdb'" & _

"SELECT DistributorName AS DistributorName, EndCustomer AS EndCustomer, PartNumber As PartNumber, Date As Date, Sales As Sales FROM ds.Tables(0) "

cmd.ExecuteNonQuery()

conn.Close()

End Sub

End Class

How can i correct it

Thanks very much.

Basara
Sudhakar_tom

You can't persist an ADO.NET created table with ADO.NET in an Access database.

You have to use ADOX to do that.

Here is example code. (not written by me - and I put the wrong reference in my database.)

Private Sub Command1_Click()
'Shows how to create an Access 2000 database and append tables, fields, indexes using ADOX. Don't forget
'a reference to ADOX (Microsoft ADO Ext. 2.x for DDL and Security)
Dim ADOXcatalog As New ADOX.Catalog
Dim ADOXtable As New Table
Dim ADOXindex As New ADOX.Index

   Try
            ADOXcatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\newdata.mdb"
  Catch
End Try

ADOXcatalog.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& "c:\newdata.mdb"

'name table, append fields to table
ADOXtable.Name = "Employees"
ADOXtable.Columns.Append "LastName", adVarWChar, 40
ADOXtable.Columns.Append "ID", adInteger
ADOXtable.Columns.Append "Department", adVarWChar, 20

'append tables to database
ADOXcatalog.Tables.Append ADOXtable

'internal index on two fields
ADOXindex.Name = "TwoColumnsIndex" 'name of index
ADOXindex.Columns.Append "LastName"
ADOXindex.Columns.Append "ID"

ADOXtable.Indexes.Append ADOXindex
ADOXtable = Nothing
ADOXindex = Nothing
ADOXcatalog = Nothing
End Sub

(The original was an incorrect mixture of Vs2005 and VB6. This has been corrected by me.)
Nian2000

Hi ReneeC,

There is some problem in the code. The createTable class is fine just that I have to delete the test for whether a connection is already open. ConnectionIsOpen cannot be used. But the code that call this class has a lot problem. Maybe because the code is not complete

Here is my own code for creating an Access database and create table inside. I think this is also working fine.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim cat As ADOX.Catalog

Dim tbl As ADOX.Table

Dim con As ADODB.Connection

' Delete the database if it already exists.

On Error Resume Next

Kill("C:\Documents and Settings\yzhang\My Documents\intern\Analysis\txtDatabase.mdb")

MsgBox("database killed")

On Error GoTo 0

' Create the new database.

cat = New ADOX.Catalog

cat.Create( _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Documents and Settings\yzhang\My Documents\intern\Analysis\txtDatabase.mdb;")

' Create a new table.

tbl = New ADOX.Table

tbl.Name = "ImportSales"

tbl.Columns.Append("DistributorName", ADOX.DataTypeEnum.adVarWChar, 40)

tbl.Columns.Append("EndCustomer", ADOX.DataTypeEnum.adVarWChar, 40)

tbl.Columns.Append("PartNumber", ADOX.DataTypeEnum.adVarWChar, 40)

tbl.Columns.Append("Date", ADOX.DataTypeEnum.adVarWChar, 40)

tbl.Columns.Append("Sales", ADOX.DataTypeEnum.adVarWChar, 40)

cat.Tables.Append(tbl)

' Connect to the database.

con = cat.ActiveConnection

' Insert a record.

'con.Execute("INSERT INTO TestTable VALUES ('Rod', " & _

' "'Stephens')")

con.BeginTrans()

con = Nothing

tbl = Nothing

cat = Nothing

MsgBox("Done")

End Sub

The problem now is how am i able to transfer the data from my dataset 'ds' which i give the code in last post into the table in my Access database.

Thank you.
Filipe Fortes - MSFT

Hi!

You're welcome. I'm going to answer point by point because I sense some misperceptions.

Am i able to do it in VB 2005 express Or must i use the ADO.Net IDE instead

Yes, and they are one in the same. There is no such thing as an ADO.Net IDE.

I tried to include namespace System.Data.Odbc in front of your code. But it's not working.

I don't know why it would work.

The code you have is ADOX code and it will require:

Project | Add References | COM | and the following two references

' Microsoft ActiveX Data Objects 2.8 Library

' Microft ADO V2.8 for DDL and Security

I will be giving you code. You will need to include the ADO.NET Namespace which is accomplished by:

Project | Add References | .Net | Adodb

Yes I have code that will persist a modified or appended table in an Access Database.

The dataset does not persist your data. The DataAdaper does.
Jeps

Basara,

My own programming style is one where I try to never use SQL at all....

What does your table look like
Joe Buys

OK....

With the references I identified above.... this will create a database:

Private Sub cbGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbGo.Click

Dim cat As New ADOX.Catalog
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\foo.mdb")
cat = Nothing

End Sub

This is tested and it created a 64K mdb file with all of the Access Objects.

Next how to create a table.... (Thats going to have more code.)
meiki

Hi,

I can see the new Access database created also, So I think the .Create part has no problem.

But the .ActivateConnection part has problem.

Dim ADOXcatalog As New ADOX.Catalog

Dim ADOXtable As New ADOX.Table

Dim ADOXindex As New ADOX.Index

Try

ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Documents and Settings\yzhang\My Documents\intern\ADO\newdata.mdb")

Catch

End Try

ADOXcatalog.ActiveConnection = _

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _

& "C:\Documents and Settings\yzhang\My Documents\intern\ADO\newdata.mdb"
okinseattle

Hi,

Thanks for your response. The code is to create a Access database and then add tables to the database right

Am i able to do it in VB 2005 express Or must i use the ADO.Net IDE instead I tried to include namespace System.Data.Odbc in front of your code. But it's not working.

I am totally new to database programming like ADO, OleDb... and VB

Since i can connect to Access database and make changes to the database(as far as i know done by store data into dataset to make changes then save it back to database) , I think there should be a way that i may save the change to the dataset back to database also right

I just need to know how to save the data in my dataset back into Access. Am I able to do this

Best regards.
cenko2

Hi,

I have tried the code above and it give me some error here.

ADOXcatalog.ActiveConnection = _

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _

& "C:\Documents and Settings\yzhang\My Documents\intern\ADO\newdata.mdb"

It says "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

you mentioned earlier that you will be giving me some code, do you mean code other than the one above

Regards..
Vatroslav Mihalj

Basara,

In your code, I see where you are opening Excell sheets. I don't see any Access code.

It seems to that you've described several issues. Which ADO issue do you want to deal with first
Retrieve data from Excel and store it in Access through VB

Answer this question