INSERT TO OTHER DATABASE

D

DS

Every transaction I have I want it to also go to another table in another
database. How would I do this? I know there are linked tables and the
such. I'm using SQL with an unbound form. Or doesn't it matter?
Thanks
DS
 
B

Bob Hairgrove

Every transaction I have I want it to also go to another table in another
database. How would I do this? I know there are linked tables and the
such. I'm using SQL with an unbound form. Or doesn't it matter?
Thanks
DS

Using DAO, you can open the other database in the current workspace
and call the Execute method on it with your SQL statement as an
argument. I'm not sure how to do this with ADO, but you should be able
to open a new connection to that database and run queries in a very
similar manner. You should wrap everything in a transaction to keep
things consistent, though.

Check out the help topic for the OpenDatabase method of the Workspace
object in the DAO help for Access. Also, check out BeginTrans, Commit
and Rollback for transaction management.
 
M

Marshall Barton

DS said:
Every transaction I have I want it to also go to another table in another
database. How would I do this? I know there are linked tables and the
such. I'm using SQL with an unbound form. Or doesn't it matter?


Have you looked into using the IN phrase in the FROM or INTO
clause?
 
D

DS

Thanks Bob I'll give that a try!
DS
Every transaction I have I want it to also go to another table in another
database. How would I do this? I know there are linked tables and the
such. I'm using SQL with an unbound form. Or doesn't it matter?
Thanks
DS

Using DAO, you can open the other database in the current workspace
and call the Execute method on it with your SQL statement as an
argument. I'm not sure how to do this with ADO, but you should be able
to open a new connection to that database and run queries in a very
similar manner. You should wrap everything in a transaction to keep
things consistent, though.

Check out the help topic for the OpenDatabase method of the Workspace
object in the DAO help for Access. Also, check out BeginTrans, Commit
and Rollback for transaction management.
 
D

Douglas J. Steele

INSERT INTO Table1 IN C:\Folder\File.MDB (Field1, Field2, Field3)...

Alternatively, you can use

INSERT INTO [;Database="C:\Folder\File.MDB"].Table1 (Field1, Field2,
Field3)...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


DS said:
No I haven't How would that work?
Thanks
DS
 
D

DS

That is what I wanted !!!!!!!!!!
Thank you, once again!
DS
Douglas J. Steele said:
INSERT INTO Table1 IN C:\Folder\File.MDB (Field1, Field2, Field3)...

Alternatively, you can use

INSERT INTO [;Database="C:\Folder\File.MDB"].Table1 (Field1, Field2,
Field3)...
 
Top