DataSet transfer data from one database to another

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

Grzegorz Danowski

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:
(...)

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
 
K

Kevin Yu [MSFT]

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."
 
G

Guest

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
 
J

Jim Hughes

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

Guest

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

Grzegorz Danowski

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

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
 
G

Guest

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

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
 
K

Kevin Yu [MSFT]

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."
 
J

Jeff Dillon

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

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
 
G

Grzegorz Danowski

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.

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

Regards,
Grzegorz
 
P

Paul Clement

¤ 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)
 
Joined
May 4, 2012
Messages
2
Reaction score
0
getting not proper bracketing for the following code

Insert Into
tblCase
In
'' [;Pwd=XXXXX;Database=E:\B.mdb]
Select
*
From
tblCase
 
Joined
May 4, 2012
Messages
2
Reaction score
0
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()
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top