how to import access data into sql server using vb.net

[sophie_kiu]

NEED HELP~!
Does anyone know how to import access data into sql server using vb.net? I want to create a openfiledialog and allow user to choose which access file to be imported into the server.
Thank you so much~~ >.<
PS. I am just a beginner of vb.net. So would u pls tell me the solution in detail?
THANKS~~~!!

[Oxiegen]

You can try this.
Put a button of your form and call it btnImport.
Then add this code.

Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
        Dim fileName As String = ""

        Dim ofd As New OpenFileDialog
        If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
            fileName = ofd.FileName
            PerformImportToSql(fileName)
        End If
    End Sub

    Private Sub PerformImportToSql(ByVal Filename As String)
        Dim table As DataTable = New DataTable
        Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; DataSource=" & Filename & ";User Id=admin; Password=;")
        Dim sqlConnection As New SqlClient.SqlConnection("Data Source=yourServer; Initial Catalog=yourDatabase; User Id=yourUsername; Password=yourPassword;")

        Try

            'Import the Access data
            accConnection.Open()

            Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM <tablename>", accConnection)
            accDataAdapter.Fill(table)
            accConnection.Close()

            'Export to MS SQL
            sqlConnection.Open()
            Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT * FROM <tablename>", sqlConnection)
            Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)
            sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand()
            sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand()
            sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand()
            sqlDataAdapter.Update(table)
            sqlConnection.Close()
        Catch ex As Exception
            If accConnection.State = ConnectionState.Open Then
                accConnection.Close()
            End If
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
            MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _
            & ex.ToString)
        End Try
    End Sub

[sophie_kiu]

Thank you very much!!!
I will try it !!!
Thank you !!

[Oxiegen]

Good luck! :)

[sophie_kiu]

I found error :
system.data.oledb.oledbexception Could not find installable ISAM
system.data.oledb.oledbconnection.processresult()
system.data.oledb.oledbconnection.initializeprovider()
system.data.oledb.oledbconnection.open()

Like this~~

Pls help me >.<

[sophie_kiu]

If the program run successully, what will be the name of the datafile in SQL?
Do I need to change this statement :
"SELECT * FROM <tablename>"
like typing the tablename by my own?

And also, could I ignore the userid and password at the accconnection part if there is no need to enter such info to access the datafile?

Thank you so much!!!!!!

[Oxiegen]

One of the most likely causes for that ISAM error is that you may be missing the JET 4.0 data access component.
It's available for download from this link.

And as for your other question.
And yes, you need to change <tablename> into the name of the table in the database.

[sophie_kiu]

I have tried your method already. But the error still appears T.T

[Oxiegen]

Ok.
On what line in the method PerformImportToSql does this error occur?
And what version of Access database are you trying to read? x<=2003 or 2007?

[sophie_kiu]

error appears on line 19
I am using access 2003 version.

Thank you so much~~!!

[sophie_kiu]

is it because my access version?
as I am using window server 2000

[sophie_kiu]

I mean do I need to use access 2000 instead?

THANKS~

[Oxiegen]

No. It has nothing to do with the server version.
And I was just making sure of the Access version.

There's obviously something hinky with the connection string.
If everything is as it should be, your connection string should look something like this, (where C:\mydatabase.mdb is YOUR path and database file):

Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;")

Oooh, I just realized.
Try changing from JET to Jet in the connection string.
It's a small chance, but let's try it anyway.

[sophie_kiu]

I have chnaged JET to Jet already~~ Its not becuase of this~~
OH~
I just switch my access version to 2000
does it matter?
Do I need to change "User Id=admin;Password=;" ?

I have tried using your method~
it still doesn't work~
But I really appreciate for your help!!!
Thank you so much~~

[sophie_kiu]

Oh~ I am sorry~
I just found out that I didn't put a space between data and source

But then another error appear~ >.<
it said it cannot find my database.
But it really exist~
I don't know why
pls help ~~

[Oxiegen]

No, switching access version would have no impact on this.

I just noticed something.
What happens if you add a space between Data and Source in the connection string.
Ie, change from "DataSource" to "Data Source".

[sophie_kiu]

the original error doesn't appear~ ^o^

I would like to know what tablename should I be put in in this statement?
Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM <tablename>",
accConnection)

THanks~~

[Oxiegen]

You should put the name of the table as it appears in the access database.
And use the same name for the other SQL query as well.

[sophie_kiu]

I could not find the name~
I use outage_summary as the name
and then
another error comes out :
system invalid operation exception
dynamic SQL generation for the update command
something like this~~

[sophie_kiu]

I mean the system didn't tell me Outage_summary is not found
So I think this is the table name

But this error message appears at line 30

what is this?

[Oxiegen]

Weird, is what it is.
Did you put "outage_summary" as table name in both "SELECT ...." querys?

[sophie_kiu]

no i didn't~
I know the name of the file in SQL.
what should I do??

[Oxiegen]

Ok.
Here it is.
First, in the table on your SQL server, set the Primary Key.
Here's how to do it.
Make sure that it is the corresponding field as in your access database.

Then, immediatly after the line accConnection.Close() .
Add these lines:

For Each row As DataRow In table.Rows
   row.SetAdded()
Next

This will ensure that the state of each row is not unmodified. Which in turn will make the sqlDataAdapter.Update(table) run an INSERT query. Thus adding the information to your sql database.

[sophie_kiu]

I couldn't find the setadded~
Is it because I do not have a certain dll file?
Also, I have no idea of what the passage "set primary key" is talking about. >.<
I couldn't find the things stated in the passage.

[Oxiegen]

The method SetAdded() is a part of the DataRow object, which comes standard with .NET.
Just copy the code in the snippet as is.

Here's how to set the primary key for a table in SQL server.

Start SQL Server Management Studio Express, and logon to the server.
Browse to the database and expand the tables folder.
Right-click on the table in question and select Modify.
In this view, right-click on the small left-hand side row-header, for the field that needs to be a Primary Key, and select "Set Primary Key". That's it.
Save, close and exit SQL Server Management Studio Express.