Read Ms Access Mdb File

emi_sastra
emi_sastra used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Right now I read mdb to dataset
Are 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.
Distinguished Expert 2017

Commented:
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.
Ensure you’re charging the right price for your IT

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!

Author

Commented:
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.
Distinguished Expert 2017

Commented:
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?

Author

Commented:
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.
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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.

Author

Commented:
Hi Scott,

I use .NET
I also want to use SP also if could make it simple.

Thank you.

Author

Commented:
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.
Distinguished Expert 2017

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
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(YourConnection)
  conAccess.Open
  Using conSQL As New SQLClient.SQLConnection(YourConnection)
    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.ExecuteReader)
          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

Author

Commented:
Hi PatHartman,

Thank you for added .NET topic list.

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
I read mdb to dataset and insert to sql table using insert bulk.

Open in new window

Can you show how you're doing that? I'm curious how you read from one connection and insert into another in bulk.
Hi Scott,

Isi_Data_Mentah_From_Access_HIT (....)
Insert_Bulk_Data("TEMPHIT", strSQLInvCompanyConnection, dtHIT)


  Private Function Isi_Data_Mentah_From_Access_HIT(dteFromDate As DateTime, dteToDate As DateTime) As DataTable

        Dim sbdSQLCommand As New System.Text.StringBuilder

        sbdSQLCommand.Append(" SELECT DISTINCT")
        sbdSQLCommand.Append(" FingerPrintID, DateLog, TimeLog")
        sbdSQLCommand.Append(" FROM PersonalLog")
        sbdSQLCommand.AppendFormat(" WHERE DateLog BETWEEN #{0}# AND #{1}#", Format(dteFromDate, "yyyy/MM/dd"), Format(dteToDate, "yyyy/MM/dd"))
        sbdSQLCommand.Append(" ORDER BY FingerPrintID")

        Dim strSQLCommand As String = sbdSQLCommand.ToString

        Try

            Dim accessDataSet As New DataSet()
            Dim accessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtFile.Text.Trim & ";Persist Security Info=True;Jet OLEDB:Database Password=" & strPassword)
            Dim accessAdapter As New OleDbDataAdapter(strSQLCommand, accessConnection)

            accessAdapter.Fill(accessDataSet, "Logs")
            Return accessDataSet.Tables("Logs")

        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Information, Me.Text)
   
        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(strTargetConnection)
                conApp.Open()

                Dim sqlbulkSubt As New SqlBulkCopy(conApp)
                sqlbulkSubt.DestinationTableName = strTargetFileName

                For Each dtColumn As DataColumn In dtTable.Columns
                    sqlbulkSubt.ColumnMappings.Add(dtColumn.ColumnName.ToString, dtColumn.ColumnName.ToString)
                Next

                sqlbulkSubt.WriteToServer(dtTable)
            End Using

        Catch ex As Exception
            Throw
        End Try

    End Sub

Hope this is clear enough.

Thank you.

Author

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
You should ask that as a New question, and accept your comment as the solution to this one. It's frustrating for Experts to continue to work with a revolving-door question.

Author

Commented:
My solution accepted by Scott.

Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial