getting data from one database to another database

[BAE]

I'm attempting to learn VB.Net on my own and I have a question. I want to get data from one access database to another. I actually did it by populating invisible text boxes on my form first and then sending it to the other database, but I'm wondering if theres a way to do it directly. Any help would be great. Thanks.

[ahara]

well...one way that's a bit more direct would be to first fetch some records into a recordset from the first db table, then loop through the record set and use command object to perform inserts. (more efficient than your text box idea), although if there are a lot of records involved, (over 100,000) and numerous tables, you could be at this for a while. What about using Access's import features? I am not an expert with Access, but in the past I have imported data successfully from Excel - I see no reason why you could not do it with another mdb file (assuming this is a one time process for purposes unknown to me)

cheers

[Pasvorto]

You could use a SQL such as "Insert into destinationtablename Select * from sourcetablename"

[ahara]

Originally posted by Pasvorto
You could use a SQL such as "Insert into destinationtablename Select * from sourcetablename"

but how do you do that when the tables are in separate DB's?

[Pasvorto]

You got me on that one. The simplest way, I think would be:

Open input recordset
open output recordset

input.movefirst
do
output.addnew
output![field1] = input![field1]
.....
.....
output![fieldx] = input![fieldx]
ouput.update
input.movenext
loop while not input.eof

input.close
output.close

[ahara]

yeah...that's what I was thinking....do you think it might be quicker to just use Access gui interface and import? I really don't know.

[Pasvorto]

I've never imported between ACCESS dbs, only ACCESS to SQL Server.

[brucevde]

You could use a SQL such as "Insert into destinationtablename Select * from sourcetablename"

but how do you do that when the tables are in separate DB's?

The syntax of the Insert Into and Select statements allow you to specify a filename.

For example - I have the Northwind.mdb (Access 97) and NWind.mdb (Access 2000) sample databases on my PC. The following code will create a new table called Suppliers2 in the Northwind database and populate the new table with data from NWind.Suppliers.

Dim objConn As ADODB.Connection
Dim strSQL As String
 
Set objConn = New ADODB.Connection
 
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=M:\Testing\NWind.mdb"
 
strSQL = "Select * Into Suppliers2 In 'M:\Testing\northwind.mdb' From Suppliers"
 
objConn.Execute strSQL
 
objConn.Close