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.

 

[scorpion53061]

I suspect the problem is in your update statement. Should it be a insert
since the original datbase is empty of rows?

Why just copy this to the hard drive?

Dim wc As New System.Net.WebClient
wc.DownloadFile("http://www.abc.com/file.mdb", "C:\new database\file1.mdb")

'with your preconfigured dataadatper (that will look to "C:\new
database\file1.mdb") you load the new database into a dataset
delete the contents of the original database
Make sure the orignal dataset is empty of rows
Copy the new dataset to the old and run your insert statement in a
for.....next situation. Dont forget to remove the foreign columns first and
include any autoincrement statements for your primary field.


If I am wrong or dont have all the facts here I am sorry.

 

[Rick Palmer]

OK, I've figured part of this out....It's not updating because all the
records in the dataset are marked as "Unmodified". An wouldn't you know
that the "RowState" is read-only, so you can't loop through the rows and
mark them as something other than "Unmodified". So how the hell are you
supposed to get the data in the database?????


C'mon MVP's, you're not going to get stumped by an entry-level programmer
are you????

 

[Rick Palmer]

I would just copy the file, but there is some data I don't want in the
"in-house" file that I am filtering out, as well as a couple of columns
getting dropped.

See my latest post regarding new discoveries.....



-rp

 

[scorpion53061]

Ok so you after filling the new dataset exclude the columns you want and
then insert them into the datbase you are desiring to go to.