Solved

COPY TABLE1 FROM C:\DB1.MDB TO C:\DB2.MDB

Posted on 2006-11-10
14
256 Views
Last Modified: 2008-07-27
i have 2 db same structre and i want to copy the data of table1 from db1 to db2
but i dont want to include the first field(key field and dont care about it)  

i have done this in vb.net

    Private Sub UpdateData(ByVal SelSql As String)

        Dim sourceSQL As String = SelSql
        Dim targetSQL As String = SelSql
        Dim targetDA As New OleDbDataAdapter(targetSQL, CON1)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim targetCommand As OleDbCommand = cb.GetInsertCommand
        Dim cmdText As String = targetCommand.CommandText

        If CON2.State = Data.ConnectionState.Closed Then CON2.Open()
        If targetCommand.Connection.State = Data.ConnectionState.Closed Then targetCommand.Connection.Open()

        cmdText = cmdText.Replace("( ", "( [")
        cmdText = cmdText.Replace(" , ", "] , [")
        cmdText = cmdText.Replace(" )", "] )")
        cmdText = cmdText.Replace("[?]", "?")

        targetCommand.CommandText = cmdText
        Dim sourceCommand As New OleDbCommand(sourceSQL, CON2)
        Dim sourceReader As OleDbDataReader
        sourceReader = sourceCommand.ExecuteReader()

        Me.Text = 0
        While sourceReader.Read()
            For i As Integer = 1 To sourceReader.FieldCount - 1
                targetCommand.Parameters(i - 1).Value = sourceReader(i)
            Next
            targetCommand.ExecuteNonQuery()
            Me.Text = Me.Text + 1
        End While

        sourceReader.Close()
        CON2.Close()
        targetCommand.Connection.Close()
    End Sub

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21975556.html

this works but is there any better way this takes to much time

Dimitris


0
Comment
Question by:rockas1982
  • 4
  • 3
  • 2
  • +4
14 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
The easy way to create a make-table query and execute it.

What database are you targeting?
0
 
LVL 24

Expert Comment

by:Justin_W
Comment Utility
If you only need to copy the data once, you could export it to a flat file (e.g. CSV), then bulk import the flat file into the other DB.
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Code looks something like this:

Using conn as New OleDbConnection(connectionString)
  Dim sql as String = "SELECT <fieldList>  INTO <destinationTable> FROM <sourceTables>"
  Using cmd as New OleDbCommand(sql,conn)
    conn.Open
     cmd.ExecuteNonQuery
  End Using
End Using

0
 
LVL 1

Author Comment

by:rockas1982
Comment Utility
thnx for fast replay

i'm using access 2003 with vb.net 2003

i'm new in sql commands and i saw something that might work the "IN" command .. can this help me ?

the sample i saw is this

strSQL = "INSERT INTO Pricebook  SELECT * FROM Pricebook IN 'K:\PriceBk\PriceBook.mdb'"

i want to give the filepathh from vb.net sourcefile and targetfile
Can i connect to my target database and make an insert query asking data from onother database "insert into table1 (all fileds except the first) select table1 (all fileds except the first) IN (filepathAndName) (where shop = "shop1")
do tou think this may work

P.S. is there any way i can add progressbar into this so user can see how long this will take(not important)

sorry for my english(Greece)

DIMITRIS
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
Chaosian

Hi Jeff ;-).  Long time no speak.

Can you do that from one DB to another?  Won't you need two different connections?

Roger
0
 
LVL 1

Author Comment

by:rockas1982
Comment Utility
I hoped that sql will call the second database with the "IN" command ... don't really know
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Roger...

Shoot... you almost caught me...

The sneaky here is that with an enterprise level database (read SQL Server), you can use fully-qualified table names to work across multiple registered databases. I missed the MDB part.

Dimitri... let me post a pointer for you....the Acces gurus can probably help out on this one...
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Dimitri.... I'll do one better....

Create the query in Access using the query builder. Click the button in the upper left until it says "SQL"... cut-and-paste this into your VB app as the SQL statement for your command (fixing any quotes, as required)

This way, you can test the SQL statement to make sure it does what you want... and the make-table wizard lets you specify the other database to use.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
Yup.
Looks like you're already along the right lines.
You just have to specify the fields you do want (to eliminate the fields you don't).

strSQL = "INSERT INTO Pricebook (Field2, Field3, Field26) SELECT Field2, Field3, Field26 FROM Pricebook IN 'K:\PriceBk\PriceBook.mdb'"
or
strSQL = "INSERT INTO Pricebook (Field2, Field3, Field26) SELECT Field2, Field3, Field26 FROM [K:\PriceBk\PriceBook.mdb].Pricebook"

Not dissimilar to using a linked server - considering that mdb's are file based - you just specify the file.
Executed with a connection to the db you want to receive the records of course.
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
basic ssyntax is
Select * into tablename in '\path\database.mdb' from tablename
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
isn't insert into an append?
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 100 total points
Comment Utility
Yeah - an append.
Select Into would create a new table - but I understood the question to imply the two db's already had a similar structure.

(Oh, and no rockas1982 there's no exact way to get a progress meter, as the query engine's execution can't be hooked into).
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 200 total points
Comment Utility
right you are.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 200 total points
Comment Utility
Something like this?


Select fld1, fld2, fl3 INTO myNewTable IN "c:\Data\AccessData\myOtherMDB.mdb" FROM myTable;
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now