Avatar of emi_sastra
emi_sastra
 asked on

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.
Microsoft AccessMicrosoft SQL Server.NET Programming

Avatar of undefined
Last Comment
emi_sastra

8/22/2022 - Mon
Jim Dettman (EE MVE)

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.
Scott McDaniel (EE MVE )

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.
PatHartman

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
emi_sastra

ASKER
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.
PatHartman

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?
emi_sastra

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

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.
emi_sastra

ASKER
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.
emi_sastra

ASKER
Hi Scott,

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

Thank you.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
emi_sastra

ASKER
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.
PatHartman

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.
Scott McDaniel (EE MVE )

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
emi_sastra

ASKER
Hi PatHartman,

Thank you for added .NET topic list.
emi_sastra

ASKER
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.
Scott McDaniel (EE MVE )

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
emi_sastra

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
emi_sastra

ASKER
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.
emi_sastra

ASKER
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.
Scott McDaniel (EE MVE )

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
emi_sastra

ASKER
My solution accepted by Scott.

Thank you.