Read Ms Access Mdb File

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.

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.

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.

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.

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.

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.

Hi Scott,

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

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 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(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

Hi PatHartman,

Thank you for added .NET topic list.

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.

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

Select allOpen 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.

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.

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.

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.

My solution accepted by Scott.

Thank you.