How can I synchronise my Access DB with another?

[benzo1]

I would like to synchronise a form in my Access DB with data from another form from another Access DB. Apart from copying and pasting the relevant field data in datasheet view, is there any way I can do this automatically using an Access function/macro/module?

This would save me a lot of time as there is a lot of data to synchronise.

Thanks in advance,

Benzo1

 

[annsolomon]

Hi Benzo1. Can you explain a bit more? By the way, forms don't contain data, tables do. Are you saying that you have a field (e.g., text box) on a form that you would like to display data that's contained in a table from a database other than the one you're currently using? If so, you can probably link to that table and, using a query, reference that table's field in your form.

Give us some more info so we can help better. Ann

 

[koresnordic]

I agree with Ann above. You are probably talking about tables and not forms. If this is the case, create a link to the other database table, and use an update query to update the data in this database. Check in the Access help file to see how to create an update query.

Hope this helps.

Graham

 

[benzo1]

Hi Ann and Graham;

Sorry about misdescribing tables as forms. I am very much a novice in the big bad world of DBs (as you can tell).

Essentially, my boss has given me an Access DB with a table (table a) that has related fields (I hope this is the correct terminology) to that of another DB (table b). I would like to copy the data from the table b to the corresponding fields in table A. As there are about 730 records in table b with several fields that I would like to take data from, I would very much like to make this task very efficient. Hence, my posting in this forum.

I hope I have explained myself in a comprehensive, thorough manner.

Thanks for all your help.

Regards,

Benzo1

 

[annsolomon]

If you truly are talking about tables in two separates databases the first thing you have to do (as Graham and I mentioned) is to link the databases. Decide which database is going to be your primary database then go to File, Get External Data, Link Tables. Let us know if you need more help there.

Secondly, go to the query window and select "create a query in design view". Choose Update Query from the Query menu. In the QBE (query by example) grid, enter the fields you want to copy. If the field names in the other database table are the same the Update To field will be filled in automatically; otherwise, you'll have to type it in.

Holler back if you need more coaching from us.

By the way, since you're new to this, let me offer you a tip that I wish someone had told me years ago. It's a good idea not to use spaces or special characters (like dashes) in your table, field, etc. names. If you ever decide to start automating your database with VBA (which you probably will) it's a pain in the ... to work with spaces/special characters. Save yourself some future heartache.
Ann

 

[koresnordic]

Hi Benzo1, Don't worry about the mis-understanding, I still make them.
What you are describing sounds just right for an update query as I mentioned above.

 

[benzo1]

Thanks Ann and Graham for all your help.

Would I also be able to link forms from two different databases? Or is this too high level for it to work?

Thanks again.

Benzo1

Ps. Ann, thanks for the Noobie advice!

 

[annsolomon]

Benzo1, you don't link forms. You only link tables. You can, however, import forms from another database (instead of having to recreate them). Keep in mind, if you import forms they're going to be referencing fields from the original table from which the forms were created. So, if you don't import or link the original table to your new database, you'll get errors when you try to open the imported form. Ann

 

[koresnordic]

You can link as many databses as you wish, using the link information given by Ann above. Once the table has been linked to the database you are working on. Then it can be treated as if it was in that databse.



Graham