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.
LVL 1
emi_sastraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
PatHartmanCommented:
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.
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

emi_sastraAuthor 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.
0
PatHartmanCommented:
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?
0
emi_sastraAuthor 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.
0
PatHartmanCommented:
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.
0
emi_sastraAuthor 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.
0
emi_sastraAuthor Commented:
Hi Scott,

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

Thank you.
0
emi_sastraAuthor 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.
0
PatHartmanCommented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
emi_sastraAuthor Commented:
Hi PatHartman,

Thank you for added .NET topic list.
0
emi_sastraAuthor 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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
emi_sastraAuthor Commented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor 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.
0
emi_sastraAuthor 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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
emi_sastraAuthor Commented:
My solution accepted by Scott.

Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.