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.
I would like to drop the current table and import the access table from the access db.
You can also link an MSAccess db directly in SQL server.
https://www.google.com/url?sa=t&source=web&rct=j&url=https://support.office.com/en-us/article/Import-or-link-to-SQL-Server-data-a5a3b4eb-57b9-45a0-b732-77bc6089b84e&ved=0ahUKEwjl-Ona24nSAhUK4SYKHWOSBecQFggdMAE&usg=AFQjCNEUNiiJjLXk0rNQznSdxBXPF_C06A&sig2=kixdPzmPzSDUpki3Nw_6hA
https://www.google.com/url?sa=t&source=web&rct=j&url=https://support.office.com/en-us/article/Import-or-link-to-SQL-Server-data-a5a3b4eb-57b9-45a0-b732-77bc6089b84e&ved=0ahUKEwjl-Ona24nSAhUK4SYKHWOSBecQFggdMAE&usg=AFQjCNEUNiiJjLXk0rNQznSdxBXPF_C06A&sig2=kixdPzmPzSDUpki3Nw_6hA
ASKER
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.
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.
ASKER
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
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
ASKER
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]')
ASKER
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)".
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thx Vitor
The Microsoft.ACE.OLEDB.12.0 did the trick?
ASKER
Open in new window