how to import MS access database table in sql server 2005 in vb.net code

Question

I am developing a vb.net application in SQL server 2005 ,now i would like to import MS access database tables from a network into sql server 2005 database, using stored procedure or vb.net code without using SQL server 2005 Wizard . please find a sollution thanks a lot.

Answer 1

1. put your MS Access database in "App_Data" file in your vb.net . now, suppose that your MS Access database table name is 'customer' and you have id ,name and address.

2. in your pageName.aspx for example:

TextBox: txtid

Button: Find

AccessDataSource: AccessDataSource1

GridView: GridView1

a) in AccessDataSource1 press the arrow > then press "Configure Data Source..." . press on Browse ,select your MS Access database from the "App_Data" file and click Next. Select "Specify a custom SQL statment or stored procedure" and click Next. Put this in SQL statment box SELECT ID, Name, address FROM customer WHERE (ID = ?) and click next. Select 'Control' in 'Parameter Source:', txtid in 'ControlID' and click Next. Press Finish.

b) in GridView1 press the arrow > then select 'AccessDataSource1' in 'Choose Data Source:'.

b)double click on find to put the vb code:

Imports System.Data
Partial Class pageName
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        AccessDataSource1.DataSourceMode = SqlDataSourceMode.DataReader
        Dim reader As IDataReader = CType(AccessDataSource1.Select(DataSourceSelectArguments.Empty), IDataReader)
        Dim flag As Integer
        flag = 0
        If reader.Read Then
            txtid.Text = reader("id")
            txtname.Text = reader("name")
            txtaddress.Text = reader("address")
                            flag = 1
        End If
        If flag = 0 Then
            MsgBox("invalid customer id ... !")
        End If
    End Sub

      End Class