Update a database from another database

Question

_{Friday, April 22, 2011 12:05 AM}

Hi Guys,

How would I go about updating a datbase from another database on a different drive using vba.

I am trying to update values from one databaseA when the user updates the databaseB on a different drive/network.  Both datbases have different records and some records will be the same.  I need to be able to update databseA from databseB and vica versa.  Can anyone help me with this.  I mainly need help with the connecting code, the actual search engine and saving I know how to do.

All information will be invaluable, please help me!!

many thnaks,

Ian  

All replies (4)

_{Friday, April 22, 2011 1:56 AM ✅Answered}

One alternative would be to use the SQL IN clause to designate the database you want to update.  Below is a reference for Access 2007, and while the examples are SELECT statements, you can also use the IN clause with an UPDATE statement.

http://msdn.microsoft.com/en-us/library/bb177907.aspx

David Lloyd
Lemington Consulting
http://LemingtonIT.com

David Lloyd Lemington Consulting http://LemingtonIT.com

_{Friday, April 22, 2011 5:49 AM ✅Answered | 2 votes}

In addition to David,

if you can do your updates via pure SQL (I mean they are not as complicated to involve complex coding), you can work with external tables using this syntax:

SELECT A.* FROM [C:\MyDBs\Db1.mdb].Customers AS A
INNER JOIN [D:\Work\Db2.mdb].Orders AS B
ON A.CustomerID = B.CustomerID

It is absolutely the same as using IN clause but when you're working with big complicated queries you'll find this way more comfortable not to do syntax mistakes.

The main idea: forget that your tables are in different dbs. Work with them in queries like you work within 1 db. Just add a path in square brackets before a table name.

If you want to involve some coding, just assign your 'external' db to the variable:

Dim db as DAO.Database
  Set db = OpenDatabase("C:\MyDBs\Db1.mdb")
  'work with this db like you do with a current db
  Set db = Nothing

Andrey V Artemyev | Saint-Petersburg, Russia

_{Friday, April 22, 2011 1:55 AM}

Hi,

In this post http://www.utteraccess.com/forum/index.php?showtopic=1948617&view=findpost&p=1998846 you'll find an example of how to reference an external database.  Do as you would with any other db object from there.

hth

_{Friday, April 22, 2011 4:23 AM}

This thread might be useful too, solution in VBA using ADO;

http://vbadud.blogspot.com/2009/03/how-to-update-access-table-using-vba.html

HTH,

Daniel