ADO / SQL help..

[softwareguy74]

I know how to open a database connection via ADO to an Access DB but how would I do the following:

I want to open a db connection and issue an SQL statement which INSERTS data from a table in that db to another table in another db... Does that make sense? Would I need to create 2 db connections? If so, how would the code look? In know how the INSERT INTO SQL statement would look if the tables were in the same db but I'm trying to go from one db to another and not sure how it would be accomplished..

This needs to be done strictly in VB without using Access objects..

Thanks for any help you can provide...

Dan

[VB6]

[Ianpbaker]

Hi Dan

The easiest way to do this is to use two recordset's with two different connections. open them up then loop through the recordset, adding the field's from the first to the second.

While Not recordset1.EOF
recordset2.addnew
recordset2("field1") = recordset1("field1")
recordset2("field2") = recordset1("field2")
etc...
recordset2.Update
recordset1.MoveNext
Wend

Hope this helps

Ian

[softwareguy74]

Thanks for the help, but..

I wanted to try and stay away from looping and inserting each record individually because it is very slow with a large amout of data.. I want to use SQL because it is much quicker..

What I was hoping to do was to create an SQL query such as:

"INSERT INTO Table1 IN 'db1.mdb'
SELECT Table1.*
FROM Table1"

But, when using an ADO db connection in VB, how do I tell it to insert into the db connection rather than the database name (db1.mdb)?

Any help would be appreciated..

Dan

[Ianpbaker]

Just found this from the msdn site, I thought you could do it

SELECT * FROM OpenRowset('Microsoft.Jet.OLEDB.4.0','c:\northwind.mdb';'admin'; '', 'SELECT CustomerID, CompanyName FROM Customers WHERE Region = ''WA'' ')

Hope this helps

ian

[softwareguy74]

Thanks, I think we're on to something here... Can you provide me with the link on MSDN were you found your information? I would like to do some further research..

Thanks,

dan

[softwareguy74]

How would this be executed in VB code? Either I did it wrong or VB doesn't support this but I keep getting SQL syntax error.. Are you sure this code doesn't just apply to SQL Server? I did a search on MSDN for OPENROWSET and it mentions that it's T-SQL language.. Can that be executed in VB? If so, how?

Thanks,

Dan

[chicho_chicho]

IN SQL SERVER You can access tables from other databases using (Select * FROM Database.Table) Have you tried this on Access?