Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

Sql server, import complete table, using vb.net

currently the most uptodate client data resides in a table in an access db. I can manually import using the wizard in sql server express. How do I do that in vb.net.

I would like to drop the current table and import the access table from the access db.
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

I found someone doing this from sql server to access under the title "Copy Table from SQL Server Express to Access MDB in VB.NET", with this code, How do I do the other way around i.e table from access to sql server?

  DoCmd.TransferDatabase acLink, "ODBC Database",
                                           "ODBC;Driver=SQL Server;Server=localhost\SQLEXPRESS;Database=DBName;Trusted_Connection=Yes;",_
                               acTable, "dbo.[tablename", "tablename"

Open in new window

I need to ensure that the data in the sql server table is the most upto date table. The tables themselves come in an access mdb. Ive been importing the whole table as is using the sql server wizard, thats good as if they add a field (the client) or something else I dont need to concern myself with anything.

In access to ensure the person using the access db (each access db uses the client data in a different way) I would create a link in access using vba then convert the link to a local table using vba. this way I could ensure that one the network traffic was minimised and the process would run faster and be sure the user was using the most upto date data.

I have a vb.net program that a user will run but the question arose about data itself sql server with vb.net is just so much faster but the isers dont know how to import and I dont want them in the sql server.
Ive decided to go in a different direction

I can get to my table in access in vb.net.

is there a way to do an insert into the sql table without specifying every field in detail

i have this so far which works upto the for loop

    Public Sub InsertClientData(ByVal TableName As String, ByVal TableNamePrev As String, ByVal Path As String, ByVal DBName As String)
        Dim dtRecordsFromAccess As DataTable
        Dim cmd As New SqlCommand
        Dim y As Long

        'prepare to send data from access to sql
        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=ClientData;Integrated Security=True;MultipleActiveResultSets=True")
            cnSql.Open()
            cmd.Connection = cnSql

            'get data from access db table
            Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" & Path & DBName)
                Using cmdSelect As New OleDb.OleDbCommand
                    cmdSelect.Connection = cnAccess
                    cnAccess.Open()
                    cmdSelect.CommandText = "SELECT * FROM " & TableName & ";"
                    Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                    dtRecordsFromAccess = New DataTable
                    dtAdpt.Fill(dtRecordsFromAccess)
                    y = dtRecordsFromAccess.Rows.Count
                End Using
            End Using

            y = dtRecordsFromAccess.Rows.Count


            'For Each drAccessRecord As DataRow In dtRecordsFromAccess.Rows ' each client record
            '    Using cmdInsert As New SqlClient.SqlCommand
            '        cmdInsert.Connection = cnSql

            '        cmdInsert.CommandText = "INSERT INTO sql server without specifying every field in detail"


            '    End Using

            'Next
        End Using

        'cnSql.Close()
    End Sub

Open in new window

I did some searching around bulk import and am trying this but cant get the syntax right

use ClientData

go
sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO 
SELECT * INTO [dbo].[tClient]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','N:\Data\Abi\ABI MASTER.mdb';;, 'SELECT * FROM [tClient]')

Open in new window

the error message is Msg 7302, Level 16, State 1, Line 10
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thx Vitor
The Microsoft.ACE.OLEDB.12.0 did the trick?