Populating MS Access through Excel using VB2008

[lccengr]

Hello all,

I am a newbie in .net world and i am trying to populate MS Access through excel using Visual Basic.

I have created the code to take the input of excel but i don't have any idea how to go ahead. Supposing my excel has only 1 sheet with huge amount of data, and i want to upgrade my Access database everyday using excel sheet then how to do it?

Here is what i have done so far:

'opening Excel Sheet to import
Dim xlopenfile As OpenFileDialog = New OpenFileDialog()
Dim xlpath As String
xlopenfile.Title = "Select Excel Sheet"
xlopenfile.Filter = "Excel 2000 to 2003 (*.xls)|*.xls| Excel 2007 (*.xlsx)|*xlsx"
xlopenfile.InitialDirectory = "C:\"
xlopenfile.ShowDialog()
xlpath = xlopenfile.FileName()
If xlpath <> "" Then
'database connection with the path specified above
Dim xlsconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xlpath; Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
'Fetching table of Excel in to dataadapter
Dim xlda As New OleDbDataAdapter("select * from [sheet1$]", xlsconn)
End If


I know i have to write more statements in If statement, the next step is to create the access database here and then to populate the excel sheet in access table, anyone can give me an example here? it will be very helpful if you mark the comments and describe the code a bit, understanding something is far more important then to just copy paste it. Please also tell me how to get the UPDATING BAR during the access update process !!

Thanks

 

[Tom]

I dont know your office structure and how your network is setup but if it is on a network you can install Sql Server on your server and everyone will be able to access the data.

If your saying you need a seperate database kept on every individual PC, then yes you would need that database installed on each whether thats Access or Sql.

Another option may be creating a remote database on a webserver. Everyone should have an internet connection but you also need to figure in how this may be slower than the alternatives mentioned.

 

[Tom]

Dear Tom,

When i try to write this code, i get the following error

dlgOpen is not defined
dgv1 is not defined
StringBuilder is not defined

dlgOpen is a OpenFileDialog control that you can add on your form. This was just a dynamic way of selecting the file you want to import such as where you passed in "c\.....xls"

dgv1 was simple a DataGridViewControl. This displays the records you query on your form. I use if for testing purposes just to visually see what data was retrieved. If you dont need it then dont use that line.

Stringbuilder is part of the System.Text namespace (intellisense should have shown how to fix this. Place "Imports System.Text" at the top of your form.

 

[lccengr]

Dear Tom,

When i try to write this code, i get the following error

dlgOpen is not declared
dgv1 is not declared
StringBuilder is not declared

I see that you haven't declared them as a variable and just used it in a code. Can you please clear it?

 

[Tom]

Dear Tom,

When i try to write this code, i get the following error

dlgOpen is not declared
dgv1 is not declared
StringBuilder is not declared

I see that you haven't declared them as a variable and just used it in a code. Can you please clear it?

There first two are controls on my form, not variables. The prefixes show be a hint. dlg stands for dialog control, dgv for DataGridView

Do you have an OpenFileDialog contral named dlgOpen on your form?
Do you have an DataGridView control named dgv1 on your form?
These are controls

Did you add the Imports System.Text to the top of your form like I suggested in my previous post?

 

[lccengr]

There first two are controls on my form, not variables. The prefixes show be a hint. dlg stands for dialog control, dgv for DataGridView

Do you have an OpenFileDialog contral named dlgOpen on your form?
Do you have an DataGridView control named dgv1 on your form?
These are controls

Did you add the Imports System.Text to the top of your form like I suggested in my previous post?

Thanks Tom

Actually the code which i have written doesn't need to place that object on the forum, I have initialized it already in my code. I didn't see the initialization in your provided code that is why i wondered.

Anywayz i have done the first part, my excel sheet is displayed in the grid format. Can you please help me in understanding SQL Server 2005. Thanks

 

[Tom]

What part do you need help with? Is it installed? Is it the database creation you need help with?

 

[lccengr]

What part do you need help with? Is it installed? Is it the database creation you need help with?

Yes Tom, i have installed the SQL Server 2005. Please tell me how to proceed with the database creation. How to create the tables in SQL Server 2005. Is there any guide of writing SQL Queries in Visual Basic that will fetch the information from the binded database of SQL Server 2005. Thanks

 

[Tom]

As to creating the database, I open the Sql Server Management Studio, connect to the server I want, right click on the database folder and choose "New Database...". Within the database you can again right click and choose "New table..."

As to a guide I'm not sure how you want me to answer this one. There are a ton of books on working with SQL Server both by itself and from VB. There is one in particular that I would recommend "Pro Ado .Net 2.0" that is all about working with VB & databases.