DataSet transfer data from one database to another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to select data from Database A into Database B (both Access
Databases) by placing the selected data into a Dataset and then inserting it
into the 2nd DB. I can select the data, fill the dataset, create the Adapter
commands for insert update delete, but the update is not successful. Here's
the code:
'g_strConnectionString = Connectionstring to Database A (original DB)
strSQL = "Select * from tblCase where CaseID = " & (Me.CaseID)
Dim objDbCommandAdapter2 As New OleDbDataAdapter(strSQL,
g_strConnectionString)
objDbCommandAdapter2.AcceptChangesDuringFill = False
objDbCommandAdapter2.Fill(objDataSet)

'm_strConnectionString = ConnectionString to Database B (destination DB)
Dim objDbCommandAdapter As New OleDbDataAdapter("Select * from tblCase",
m_strConnectionString)
Dim objDbCommandBuilder As New OleDbCommandBuilder(objDbCommandAdapter)
Try
objDbCommandAdapter.InsertCommand =
objDbCommandBuilder.GetInsertCommand()
objDbCommandAdapter.InsertCommand.Connection = objConn
objDbCommandAdapter.UpdateCommand =
objDbCommandBuilder.GetUpdateCommand()
objDbCommandAdapter.UpdateCommand.Connection = objConn
objDbCommandAdapter.DeleteCommand =
objDbCommandBuilder.GetDeleteCommand()
objDbCommandAdapter.DeleteCommand.Connection = objConn
Dim DSChanges As New DataSet
DSChanges = objDataSet.GetChanges()
intRetval = objDbCommandAdapter.Update(DSChanges, "tblCase")

The error I receive is "Update unable to find TableMapping['tblCase'] or
DataTable 'tblCase'." The table structure does exist in the destination DB
and I thought the Select from DestinationDB provided the structure to the
Adapter.

Please help. Thanks.
 
Użytkownik "LisaConsult said:
I am attempting to select data from Database A into Database B (both Access
Databases) by placing the selected data into a Dataset and then inserting
it
into the 2nd DB. I can select the data, fill the dataset, create the
Adapter
commands for insert update delete, but the update is not successful.
Here's
the code:
Click to expand...
(...)

If you like to transfer all record from one access table to other table in
other database I propose to use SQL. For example:

strSQL = "Insert Into tblCase In 'E:\B.mdb' " + _
"Select * From tblCase In 'E:\A.mdb'"

Dim com As OleDbCommand = (strSQL, g_strConnectionString)
'or
'Dim com As OleDbCommand = (strSQL, m_strConnectionString)
com.ExecuteNonQuery()

The solution will be significant faster than a method based on dataset.

I hope it helps.
Grzegorz
 
Thanks for Grzegorz's quick response!

Hi,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you are having problem when trying to
update data from dataset to a database. If there is any misunderstanding,
please feel free to let me know.

Based on the code you have provided, I think there might be something wrong
with the table name in the DataSet. Since you didn't set the table name in
objDataSet, the DataAdapter cannot find corresponding table to update.

Try to replace

objDbCommandAdapter2.Fill(objDataSet)

with

objDbCommandAdapter2.Fill(objDataSet, "tblCase")

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Grzegorz,
Thanks so much for your suggestion. I am trying this method for better
efficiency, but do you know what the syntax would be when both databases have
a database password (I know they are useless, but we still must have them).

Thanks, Lisa
 
http://www.connectionstrings.com/ is your friend!

See the Access, With Password entry.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet
OLEDB:Database Password=MyDbPassword;"

Adjust the g_strConnectionString and m_strConnectionString values as
appropriate.
 
That was my logical thought as well, here's my statement:
Insert into tblCase in 'Provider=Microsoft.Jet.OlEDB.4.0;Data
Source=d:\project files\RptTmpl.mdb;Jet OLEDB:Database Password=XXXXX' Select
* from tblCase in 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=D:\project
files\data\XXX.mdb;Jet OLEDB:Database Password=YYYYYY'"

I receive an error "Invalid File Name". Thanks.
 
Użytkownik "LisaConsult said:
Grzegorz,
Thanks so much for your suggestion. I am trying this method for better
efficiency, but do you know what the syntax would be when both databases
have
a database password (I know they are useless, but we still must have
them).

Thanks, Lisa
Click to expand...

Well, syntax to use external access database (external to actual connection)
is:

Select
*
From
TableName
In
'FullPathName'

or

Select
*
From
TableName
In
"" [Database=FullPathName]

If the database has a database password syntax is some more complex:

Select
*
From
TableName
In
"" [;PWD=password;Database=FullPathName]

Another remark: "In" clause can be used in both "From" and "Insert Into"
sections of sql query. Therefore I have given previous example:

Insert Into
tblCase
In
'E:\B.mdb'
Select
*
From
tblCase
In
'E:\A.mdb'

Finally version of sql adequate to your situation (database password) should
be:

Insert Into
tblCase
In
'' [;Pwd=XXXXX;Database=E:\B.mdb]
Select
*
From
tblCase
In
'' [;Pwd=YYYYYY;Database=E:\A.mdb]

If you have open connection on source or target database you can omit one of
those full pathes, for example (assuming you have open connection to source
database):

Insert Into
tblCase
In
'' [;Pwd=XXXXX;Database=E:\B.mdb]
Select
*
From
tblCase

I hope it helps.
Grzegorz
 
Worked like a charm :-) Thanks !

Grzegorz Danowski said:
Użytkownik "LisaConsult said:
Grzegorz,
Thanks so much for your suggestion. I am trying this method for better
efficiency, but do you know what the syntax would be when both databases
have
a database password (I know they are useless, but we still must have
them).

Thanks, Lisa
Click to expand...

Well, syntax to use external access database (external to actual connection)
is:

Select
*
From
TableName
In
'FullPathName'

or

Select
*
From
TableName
In
"" [Database=FullPathName]

If the database has a database password syntax is some more complex:

Select
*
From
TableName
In
"" [;PWD=password;Database=FullPathName]

Another remark: "In" clause can be used in both "From" and "Insert Into"
sections of sql query. Therefore I have given previous example:

Insert Into
tblCase
In
'E:\B.mdb'
Select
*
From
tblCase
In
'E:\A.mdb'

Finally version of sql adequate to your situation (database password) should
be:

Insert Into
tblCase
In
'' [;Pwd=XXXXX;Database=E:\B.mdb]
Select
*
From
tblCase
In
'' [;Pwd=YYYYYY;Database=E:\A.mdb]

If you have open connection on source or target database you can omit one of
those full pathes, for example (assuming you have open connection to source
database):

Insert Into
tblCase
In
'' [;Pwd=XXXXX;Database=E:\B.mdb]
Select
*
From
tblCase

I hope it helps.
Grzegorz
Click to expand...
 
Hi Lisa,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
I have never seen the IN syntax.

In fact, I'm trying it here, and I get a runtime error every time,
"Incorrect syntax near the keyword "in".
Here's the code:

Dim con As New OleDbCommand

OleDbConnection1.Open()

con.CommandText = "Insert into Accounts in 'c:\erstemplate.mdb' Select *
from Accounts"

con.Connection = OleDbConnection1

con.ExecuteNonQuery()

OleDBConnection1 is a connection to my local SQL Server database.

Any trick to get this to work with SQL to Access? Or does this work ONLY
when using Access to Access.

Jeff

Grzegorz Danowski said:
Uzytkownik "LisaConsult said:
Grzegorz,
Thanks so much for your suggestion. I am trying this method for better
efficiency, but do you know what the syntax would be when both databases
have
a database password (I know they are useless, but we still must have
them).

Thanks, Lisa
Click to expand...

Well, syntax to use external access database (external to actual connection)
is:

Select
*
From
TableName
In
'FullPathName'

or

Select
*
From
TableName
In
"" [Database=FullPathName]

If the database has a database password syntax is some more complex:

Select
*
From
TableName
In
"" [;PWD=password;Database=FullPathName]

Another remark: "In" clause can be used in both "From" and "Insert Into"
sections of sql query. Therefore I have given previous example:

Insert Into
tblCase
In
'E:\B.mdb'
Select
*
From
tblCase
In
'E:\A.mdb'

Finally version of sql adequate to your situation (database password) should
be:

Insert Into
tblCase
In
'' [;Pwd=XXXXX;Database=E:\B.mdb]
Select
*
From
tblCase
In
'' [;Pwd=YYYYYY;Database=E:\A.mdb]

If you have open connection on source or target database you can omit one of
those full pathes, for example (assuming you have open connection to source
database):

Insert Into
tblCase
In
'' [;Pwd=XXXXX;Database=E:\B.mdb]
Select
*
From
tblCase

I hope it helps.
Grzegorz
Click to expand...
 
U¿ytkownik "Jeff Dillon said:
I have never seen the IN syntax.

In fact, I'm trying it here, and I get a runtime error every time,
"Incorrect syntax near the keyword "in".
Here's the code:

Dim con As New OleDbCommand

OleDbConnection1.Open()

con.CommandText = "Insert into Accounts in 'c:\erstemplate.mdb' Select *
from Accounts"

con.Connection = OleDbConnection1

con.ExecuteNonQuery()

OleDBConnection1 is a connection to my local SQL Server database.

Any trick to get this to work with SQL to Access? Or does this work ONLY
when using Access to Access.
Click to expand...

The syntax is specific to Jet databases (and then Jet OleDb provider), in
case of SQL Server you can use linked servers.

Regards,
Grzegorz
 
¤ I have never seen the IN syntax.
¤
¤ In fact, I'm trying it here, and I get a runtime error every time,
¤ "Incorrect syntax near the keyword "in".
¤ Here's the code:
¤
¤ Dim con As New OleDbCommand
¤
¤ OleDbConnection1.Open()
¤
¤ con.CommandText = "Insert into Accounts in 'c:\erstemplate.mdb' Select *
¤ from Accounts"
¤
¤ con.Connection = OleDbConnection1
¤
¤ con.ExecuteNonQuery()
¤
¤ OleDBConnection1 is a connection to my local SQL Server database.
¤
¤ Any trick to get this to work with SQL to Access? Or does this work ONLY
¤ when using Access to Access.

No this only works with the Jet Provider or with ISAM drivers used in conjunction with the Jet
Provider.

The easiest method would be to link your SQL Server table to your Access database and then create a
query using the native Access table and linked SQL Server table.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
getting not proper bracketing for the following code

Insert Into
tblCase
In
'' [;Pwd=XXXXX;Database=E:\B.mdb]
Select
*
From
tblCase
 
getting error of "" not proper bracketing
plss reply mee...i badly need it
confrom.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("insert into IECMaterial in [;PWD=my1stproject;DATABASE='" & DB_TO & "'] select * from IECMaterial", confrom)

AccessCommand.ExecuteNonQuery()
confrom.Close()
 
Back
Top