Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Copy a DataSet/DataTable to an Access Database.

Posted on 2004-10-11
15
15,845 Views
Last Modified: 2008-01-09
Hi there... I'm having a very hard time trying to find a direct, fully-programatic way to select data from a table in SQL Server and copy it into a table in Access. There are a million "work-arounds" out there, but none of them are very efficient options. I have exhausted the options available using ADO in VB, and since this process will eventually be written in VB.NET, I figured I'd try this angle.


So here's my scenario... I open a connection to SQL Server (using an OLEDB con in .NET) I select the contents of a table (or perform any kind of SELECT query) and populate a DataTable/DataSet with the results... I then open an OLEDB con to an Access DB. I now have a DataTable (or DataSet) full of data that I want to drop directly into a new table in the Access DB.


So how do I do this?



P.S. The most common "solution" I've found up to this point involves looping through every single record and writing them to a table/text file/whatever... that's not an efficient option in my case, since I will be working with so many records.


WATYF
0
Comment
Question by:WATYF
  • 6
  • 4
  • 3
15 Comments
 
LVL 28

Expert Comment

by:iboutchkine
ID: 12278684
You can use SQL to insert from one table into another in external database

For SQL Server
=============
insert into db2.dbo.table2 select * from db1.dbo.table1

For MS Access
=============
INSERT INTO Table1 IN 'ExternalDatabasePath1' SELECT *
FROM Table1 IN 'ExternalDatabasePath2';
where externaldatabasepath1 & 2 are something like c:\databases\db1.mdb & c:\databases\db2.mdb
(INSERT INTO Table1 SELECT * FROM Table2  IN 'c:/aaa/bbb/Database.mdb')

Database, which engine is understood by VB
=====================================
You can also add a type to the end of the IN clause such as "dBASE IV" e.g.,

INSERT INTO Table1 IN 'ExtDB' 'dBASE IV' SELECT * ....
0
 
LVL 11

Author Comment

by:WATYF
ID: 12279516
OK. I use the Access version of that SQL statement quite frequently... if the original data was in Access, I wouldn't even be having this problem...


...but how do you specify a remote path in the SQL version?


insert into db2.dbo.table2 select * from db1.dbo.table1


How do I specify a path and db filename of an Access database in the above example?



WATYF
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 12279654
insert into db2.dbo.table2 SELECT * FROM Table2  IN 'c:/aaa/bbb/Database.mdb'
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 11

Author Comment

by:WATYF
ID: 12280053
That would be selecting data from Access into SQL. I need it the other way around. From SQL into Access DB.

...something like:


INSERT INTO MyTable IN 'C:\MyFiles\Test.mdb' SELECT * FROM dbo.SQLTable


The problem is... you can't use the "IN" operator in SQL Server... or at least... it does something entirely different in SQL than it does in Access.


WATYF
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 12280162
INSERT INTO MyTable IN 'C:\MyFiles\Test.mdb' SELECT * FROM dbo.SQLTable

does this work?


Microsoft claims that it will work

Database, which engine is understood by VB
=====================================
You can also add a type to the end of the IN clause such as "dBASE IV" e.g.,

INSERT INTO Table1 IN 'ExtDB' 'dBASE IV' SELECT * ....



Another option will be to read table from SQL Server to dataset and then insert from dataset to Access. If you want to create a new table in Access use ADOX.
0
 
LVL 11

Author Comment

by:WATYF
ID: 12280591
No... that statement will not work... I get an error: Invalid Syntax near 'IN'.

In SQL Server, the "IN" operator is used to find values in a column... not to specify what Database a Table resides in. That statement will work in Access, but not in SQL Server.


And yes... the option I originally asked about was inserting from a dataset into Access... so how do I do that?


WATYF
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 12280766

   Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim sql As String = "SELECT * FROM Publishers"
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim ds as New DataSet()

        cn.Open()
        da.Fill(ds, "Publishers")


        Dim cmdBuilder As New OleDbCommandBuilder(da)


        da.InsertCommand = cmdBuilder.GetInsertCommand


        With ds.Tables("Publishers")
            ' Add a new record
            Dim dr As DataRow = .NewRow
            dr("pub_id") = "9988"
            dr("Name") = "Iouri"
            dr("city") = "New York"
            dr("country") = "USA"
            .Rows.Add(dr)
        End With

        ' Send changes them to the database.
        da.Update(ds, "Publishers")
        cn.Close()
    End Sub

'else there was no success give a message
0
 
LVL 11

Author Comment

by:WATYF
ID: 12280869
I'm sorry, but that's not what I'm looking for either. I don't need to add a row to a datatable. I need to copy an entire table from SQL Server to Access.


WATYF
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12281338
You are talking about bulk insert... Take a look here.. http://support.microsoft.com/default.aspx?scid=kb;EN-US;316244
-Baan
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12281500
0
 
LVL 11

Author Comment

by:WATYF
ID: 12292046
Actually, bulk insert would be if I wanted to import into SQL. I want to export out of SQL.

And I can't use the DTS wrapper because SQL Server isn't installed on the client machines that will be running this code.


WATYF
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12292089
Then the only way out is to loop through the records. Lets see what other experts say.
-Baan
0
 
LVL 11

Accepted Solution

by:
WATYF earned 0 total points
ID: 12300283
I finally found what I'm looking for... it was a single SQL Statement.


SELECT * INTO [AccTable] FROM [odbc;driver={SQL Server};server=MYSERVER;database=MYDB;uid=myuser;pwd=mypass].[SQLTable]


Pulls right from SQL Server into Access DB when run from the Access DB.



WATYF
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question