Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Read Ms Access Mdb File
Hi All,
i wan to read mdb file and also want to join with one/more Ms SQL Table to insert to other Ms SQL Table.
Right now I read mdb to dataset and looping it, this is sure take time for huge data.
How could I solve this problem?
Thank you.
i wan to read mdb file and also want to join with one/more Ms SQL Table to insert to other Ms SQL Table.
Right now I read mdb to dataset and looping it, this is sure take time for huge data.
How could I solve this problem?
Thank you.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Create linked tables to your remote data sources, then use SQL statements to do the work you need rather than a recordset and a loop in VBA.
Using code to process records will always be slower than doing it with SQL statements (a query in Access).
Also I would review the indexing that is being used on the Access tables.
Jim.
Using code to process records will always be slower than doing it with SQL statements (a query in Access).
Also I would review the indexing that is being used on the Access tables.
Jim.
Right now I read mdb to datasetAre you doing this in Access, or in .NET? A "Dataset" is specific to .NET, but it could also be used to describe other items.
If you're doing this in .NET, then the only way to move records from Access to SQL Server is by doing what you're doing now - read the Access data, and insert it into SQL Server row-by-row.
If you can link those SQL tables in your Access database, you could very likely do everything you need in Access, as Jim suggests.
You also mentioned that you want to join a Jet/ACE table to a SQL Server table. Although that can be done, Access accomplishes it by downloading the entire SQL Server table to your local PC and it runs the join on your local PC. If the server table is large, the process would be more efficient if you upload the Jet/ACE table and then you can do everything on the server using Append queries. Delete the Jet/ACE table when you're done.
Hi All,
I am thinking of inserting to sql database from selecting access database.
If could do this, then I could easily join other sql database tables to insert to another sql database tables.
Could it be done ?
Thank you.
I am thinking of inserting to sql database from selecting access database.
If could do this, then I could easily join other sql database tables to insert to another sql database tables.
Could it be done ?
Thank you.
You can link to Jet/ACE table and link to jSQL Server tables. Then you can use append queries that copy from the Jet/ACE table to the SQL Server table. Is that what you want to do?
Hi PatHartman,
- You can link to Jet/ACE table and link to jSQL Server tables.
jSQL ?
How to code it in Stored Procedure ?
Thank you.
- You can link to Jet/ACE table and link to jSQL Server tables.
jSQL ?
How to code it in Stored Procedure ?
Thank you.
The answers that Jim and I provided are for an "Access" solution. Just because you are using a Jet database doesn't mean that you are using Access.
If you are doing this from any other tool, import the Jet table into SQL Server and execute append queries. Do not use a cursor unless there is no other alternative. It will always be slower.
If you are doing this from any other tool, import the Jet table into SQL Server and execute append queries. Do not use a cursor unless there is no other alternative. It will always be slower.
Hi Jim,
- Create linked tables to your remote data sources, then use SQL statements to do the work you need rather than a recordset and a loop in VBA.
What is the remote data source ?
- The app menu let user to load mdb using browse from their local computer.
Thank you.
- Create linked tables to your remote data sources, then use SQL statements to do the work you need rather than a recordset and a loop in VBA.
What is the remote data source ?
- The app menu let user to load mdb using browse from their local computer.
Thank you.
Hi PatHartman,
-If you are doing this from any other tool, import the Jet table into SQL Server and execute append queries. Do not use a cursor unless there is no other alternative. It will always be slower.
I use .NET to let user to load mdb.
Thank you.
-If you are doing this from any other tool, import the Jet table into SQL Server and execute append queries. Do not use a cursor unless there is no other alternative. It will always be slower.
I use .NET to let user to load mdb.
Thank you.
If you are using .Net, you will get better help if you include .Net in your topic list. Your request has nothing to do with Access per se and so Access people who use VBA will not necessarily be able to help with something in the .Net world. I added .Net for you. If there is something more specific to your tool, then add additional topics.
If you're doing this in .NET, then you're going to have to (a) read the data from one source and then (b) write it back to the different source. With Access you'll use the OLEDB namespace, and with SQL Server you'll use the SQL namespaces (like SQLClient). You could, however, link the SQL table in the Access database, and then use the same connection (the OLEDB connection) to write to the linked and local Access tables. That would most likely be the fastest method, but - again - you'd have to make sure the SQL table is linked correctly.
If not, then something like this:
Using conAccess As New OLEDB.Connection(YourConne
conAccess.Open
Using conSQL As New SQLClient.SQLConnection(Yo
conSQL.Open
Using cmdAccess as New OLEDB.Command
cmdAccess.Connection = conAccess
Using cmdSQL As New SQLClient.SQLCommand
cmdSQL.Connection = conSQL
cmdAccess.CommandText = "SELECT * FROM SomeAccessTable
Using dtAccess As New Datatable
dtAccess.Load(cmdAccess.Ex
For Each dtr As Datarow in dtAccess.Rows
cmdSQL.CommandText = "UPDATE SomeSQLTable SET Field1=" & dtr("Field1") & " WHERE SomeFields=SomeCriteria"
Next
End Using
End Using
End Using
End Using
End Using
If not, then something like this:
Using conAccess As New OLEDB.Connection(YourConne
conAccess.Open
Using conSQL As New SQLClient.SQLConnection(Yo
conSQL.Open
Using cmdAccess as New OLEDB.Command
cmdAccess.Connection = conAccess
Using cmdSQL As New SQLClient.SQLCommand
cmdSQL.Connection = conSQL
cmdAccess.CommandText = "SELECT * FROM SomeAccessTable
Using dtAccess As New Datatable
dtAccess.Load(cmdAccess.Ex
For Each dtr As Datarow in dtAccess.Rows
cmdSQL.CommandText = "UPDATE SomeSQLTable SET Field1=" & dtr("Field1") & " WHERE SomeFields=SomeCriteria"
Next
End Using
End Using
End Using
End Using
End Using
Hi Scott,
For Each dtr As Datarow in dtAccess.Rows
cmdSQL.CommandText = "UPDATE SomeSQLTable SET Field1=" & dtr("Field1") & " WHERE SomeFields=SomeCriteria"
Next
This still use looping.
I try insert bulk.
I read mdb to dataset and insert to sql table using insert bulk.
After that I join other sql table and insert to another sql table.
It is a lot faster.
Thank you.
For Each dtr As Datarow in dtAccess.Rows
cmdSQL.CommandText = "UPDATE SomeSQLTable SET Field1=" & dtr("Field1") & " WHERE SomeFields=SomeCriteria"
Next
This still use looping.
I try insert bulk.
I read mdb to dataset and insert to sql table using insert bulk.
After that I join other sql table and insert to another sql table.
It is a lot faster.
Thank you.
I read mdb to dataset and insert to sql table using insert bulk.
Hi Scott,
Isi_Data_Mentah_From_Acces
Insert_Bulk_Data("TEMPHIT"
Private Function Isi_Data_Mentah_From_Acces
Dim sbdSQLCommand As New System.Text.StringBuilder
sbdSQLCommand.Append(" SELECT DISTINCT")
sbdSQLCommand.Append(" FingerPrintID, DateLog, TimeLog")
sbdSQLCommand.Append(" FROM PersonalLog")
sbdSQLCommand.AppendFormat
sbdSQLCommand.Append(" ORDER BY FingerPrintID")
Dim strSQLCommand As String = sbdSQLCommand.ToString
Try
Dim accessDataSet As New DataSet()
Dim accessConnection As New OleDbConnection("Provider=
Dim accessAdapter As New OleDbDataAdapter(strSQLCom
accessAdapter.Fill(accessD
Return accessDataSet.Tables("Logs
Catch ex As Exception
MsgBox(ex.Message.ToString
End Try
End Function
Public Shared Sub Insert_Bulk_Data(ByVal strTargetFileName As String, _
ByVal strTargetConnection As String, _
ByVal dtTable As DataTable)
If dtTable.Rows.Count = 0 Then Exit Sub
Try
Using conApp As SqlConnection = New SqlConnection(strTargetCon
conApp.Open()
Dim sqlbulkSubt As New SqlBulkCopy(conApp)
sqlbulkSubt.DestinationTab
For Each dtColumn As DataColumn In dtTable.Columns
sqlbulkSubt.ColumnMappings
Next
sqlbulkSubt.WriteToServer(
End Using
Catch ex As Exception
Throw
End Try
End Sub
Hope this is clear enough.
Thank you.
Isi_Data_Mentah_From_Acces
Insert_Bulk_Data("TEMPHIT"
Private Function Isi_Data_Mentah_From_Acces
Dim sbdSQLCommand As New System.Text.StringBuilder
sbdSQLCommand.Append(" SELECT DISTINCT")
sbdSQLCommand.Append(" FingerPrintID, DateLog, TimeLog")
sbdSQLCommand.Append(" FROM PersonalLog")
sbdSQLCommand.AppendFormat
sbdSQLCommand.Append(" ORDER BY FingerPrintID")
Dim strSQLCommand As String = sbdSQLCommand.ToString
Try
Dim accessDataSet As New DataSet()
Dim accessConnection As New OleDbConnection("Provider=
Dim accessAdapter As New OleDbDataAdapter(strSQLCom
accessAdapter.Fill(accessD
Return accessDataSet.Tables("Logs
Catch ex As Exception
MsgBox(ex.Message.ToString
End Try
End Function
Public Shared Sub Insert_Bulk_Data(ByVal strTargetFileName As String, _
ByVal strTargetConnection As String, _
ByVal dtTable As DataTable)
If dtTable.Rows.Count = 0 Then Exit Sub
Try
Using conApp As SqlConnection = New SqlConnection(strTargetCon
conApp.Open()
Dim sqlbulkSubt As New SqlBulkCopy(conApp)
sqlbulkSubt.DestinationTab
For Each dtColumn As DataColumn In dtTable.Columns
sqlbulkSubt.ColumnMappings
Next
sqlbulkSubt.WriteToServer(
End Using
Catch ex As Exception
Throw
End Try
End Sub
Hope this is clear enough.
Thank you.
The problem using link server is we have to upload file to server first ?
While using insert bulk inside VB .NET we don't have to.
Any other advantage and disadvantage using link server or insert bulk ?
Thank you.
While using insert bulk inside VB .NET we don't have to.
Any other advantage and disadvantage using link server or insert bulk ?
Thank you.
The problem using link server is we have to upload file to server first ?
While using insert bulk inside VB .NET we don't have to.
Any other advantage and disadvantage using link server or insert bulk ?
Thank you.
While using insert bulk inside VB .NET we don't have to.
Any other advantage and disadvantage using link server or insert bulk ?
Thank you.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial