Visual Basic is a Microsoft programming language that is relatively easy to use and Microsoft Access and Excel are applications offered in the Microsoft Office suite. Visual Basic.NET code is essential when you're working with Excel spreadsheets and need to import the data to an Access database. The data may be needed in Access for further data manipulation using database tools.
- Skill level:
Other People Are Reading
Start a new Visual Basic.NET project and add a "Button" to your form. Select the "Project" menu and click "<projectname> Properties." Select "References" and click "Add." Select "COM" and click "Microsoft Office 12.0 Object Library." Select "OK."
Create a new Excel spreadsheet and add the following data:
Field 1 Field 2
Data Data 2
Data Data 2
Save it to "C:\" as "ExcelToImport.xls."
Double-click "Button1" on your form to open "Form1.vb." Type the following under "Button1_Click" to create a sub call:
MessageBox.Show("The import is complete!")
Type the following to create a new sub called "importAllData" that will create a new database file for importing:
Private Shared Sub importAllData(ByVal file_path As String, ByVal db_path As String)
Dim accessDB As Microsoft.Office.Interop.Access.Application accessDB = New Microsoft.Office.Interop.Access.ApplicationClass() accessDB.Visible = False accessDB.NewCurrentDatabase(db_path, Microsoft.Office.Interop.Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2007) accessDB.CloseCurrentDatabase() accessDB.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll) accessDB = Nothing Dim con As OleDb.OleDbConnection = MakeExcelConnection(file_path) FillAccessDatabase(con, db_path, file_path)
Type the following to create a new sub called "FillAccessDatabase":
Private Shared Sub FillAccessDatabase(ByVal con, ByVal db_path, ByVal file_path)
conn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & file_path & ";" & "Extended Properties=Excel 12.0;" Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(con) Dim _command As OleDb.OleDbCommand = New OleDb.OleDbCommand() _command.Connection = connection _command.CommandText = "SELECT * INTO [MS Access;Database=" & db_path & "].[Sheet1] FROM [Sheet1$]" connection.Open() _command.ExecuteNonQuery() connection.Close()
Type the following to create a new function called "MakeExcelConnection":
Private Shared Function MakeExcelConnection(ByVal filename As String) As OleDb.OleDbConnection
Dim con As String conn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 12.0;" Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(con) Return connection
Run your program and click on "Button1" to import "Sheet1" of your Excel spreadsheet to "Sheet1" table in Access.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for