Copying tables

[Rick Palmer]

OK, I got something REALLY simple-sounding that is turning in to something
REALLY frickin hard. All I need to do is suck the data out of one Access
database and stuff it in another one. Here's my current code:

Sub main()
'First download the database from the web server
Dim ftp As New FTPFunctions.FTPClassClass
ftp.FTPDownload("remotename.mdb", "localname.mdb", "www.someserver.com",
"username", "passwordtext")

'Then query it for the Broker records and stuff them in a dataset (table
name: AgentDB)
Dim da As New OleDb.OleDbDataAdapter("Select * from UserDB where
HomeURL='broker_info.asp'", "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\temp\weluser.mdb;")
Dim ds As New DataSet
da.Fill(ds.Tables.Add("AgentDB"))

'Now, clear the existing in-house database
Dim conn As New OleDb.OleDbConnection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=h:\app_Data\access\BrokerEmail.mdb; Mode=ReadWrite;"
Dim cmd As New OleDb.OleDbCommand("Delete from Broker", conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()

'Now that the existing database is clear, modify the query results to match
the in-house DB's schema
ds.Tables("AgentDB").Columns.Remove("Pass")
ds.Tables("AgentDB").Columns.Remove("PWExpire")
ds.Tables("AgentDB").Columns.Remove("HomeURL")
ds.Tables("AgentDB").Columns.Remove("LastLogin")

'Take the results of the query and stuff them in the in-house database.
Dim da2 As New OleDb.OleDbDataAdapter("Select * from Broker",
conn.ConnectionString)
Dim cb As New OleDb.OleDbCommandBuilder(da2)
da2.UpdateCommand = cb.GetUpdateCommand
da2.Update(ds, "AgentDB")
End Sub

It executes with no errors but does not update the table. I have tried
making a copy of the DataSet (ds) to use in the update but with no success.

[Herfried K. Wagner [MVP]]

* "Rick Palmer" <ri*********@welove.com.nospam> scripsit:

OK, I got something REALLY simple-sounding that is turning in to something
REALLY frickin hard. All I need to do is suck the data out of one Access
database and stuff it in another one. Here's my current code:

ADO.NET group:

<news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<http://msdn.microsoft.com/newsgroups/default.asp?url=/newsgroups/loadframes.asp?icp=msdn&slcid=us&newsgroup=microso ft.public.dotnet.framework.adonet>

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>

[Bernie Yaeger]

Hi Rick,

It appears as if you are deleting broker and then selecting all from it - it
you deleted all of the rows, it returns zero rows. Am I missing something
here?

HTH,

Bernie Yaeger

"Rick Palmer" <ri*********@welove.com.nospam> wrote in message
news:O3*************@tk2msftngp13.phx.gbl...

OK, I got something REALLY simple-sounding that is turning in to something
REALLY frickin hard. All I need to do is suck the data out of one Access
database and stuff it in another one. Here's my current code:

Sub main()
'First download the database from the web server
Dim ftp As New FTPFunctions.FTPClassClass
ftp.FTPDownload("remotename.mdb", "localname.mdb", "www.someserver.com",
"username", "passwordtext")

'Then query it for the Broker records and stuff them in a dataset (table
name: AgentDB)
Dim da As New OleDb.OleDbDataAdapter("Select * from UserDB where
HomeURL='broker_info.asp'", "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\temp\weluser.mdb;")
Dim ds As New DataSet
da.Fill(ds.Tables.Add("AgentDB"))

'Now, clear the existing in-house database
Dim conn As New OleDb.OleDbConnection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=h:\app_Data\access\BrokerEmail.mdb; Mode=ReadWrite;"
Dim cmd As New OleDb.OleDbCommand("Delete from Broker", conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()

'Now that the existing database is clear, modify the query results to match the in-house DB's schema
ds.Tables("AgentDB").Columns.Remove("Pass")
ds.Tables("AgentDB").Columns.Remove("PWExpire")
ds.Tables("AgentDB").Columns.Remove("HomeURL")
ds.Tables("AgentDB").Columns.Remove("LastLogin")

'Take the results of the query and stuff them in the in-house database.
Dim da2 As New OleDb.OleDbDataAdapter("Select * from Broker",
conn.ConnectionString)
Dim cb As New OleDb.OleDbCommandBuilder(da2)
da2.UpdateCommand = cb.GetUpdateCommand
da2.Update(ds, "AgentDB")
End Sub

It executes with no errors but does not update the table. I have tried
making a copy of the DataSet (ds) to use in the update but with no success.