Copying records from one database to another

[AlanJ10]

Sorry, not sure if this should be in the vba section, or the queries section, but I think it's more code-related so I've put it here.

I have a database that contains linked tables from multiple access databases. At certain points, code will trigger that is meant to copy a record from one table to another (using docmd.runsql "INSERT INTO..."), both of these tables are in two separate databases. Every so often, the query will fail and, although no errors are shown, the record isn't inserted into the destionation table. Unfortunately, the code then goes on to delete the record from the source table, so I haven't been able to test if it's something about the actual record that's the query to fail. I've now temporarily removed the delete query, so if it goes wrong again, I'll be able to try and insert the data myself and see what (if anything) goes wrong.

So, questions:
1. This is a multi-user database (everyone has their own local copy of the front end linked to multiple back end databases), is it likely that this is failing because of a record locking issue, or is it something else? (I've checked the destination table and there are no required fields, so even if there was some missing data that shouldn't be the cause).

2. Am I going about this the best way? In order to copy records from a table in one database to a table in another, should I link them in a central db and use runSQL from there, or would I be better off linking the tables within the databases and then executing the query in one of those (and would it be better to execute in the source or the destination)?

Sorry if this doesn't make much sense, I can usually fix most things, but as this doesn't always go wrong I need to try and work out what the specific set of circumstances are that make it go wrong and I was just hoping some of you might have some ideas.

Thanks.

 

[DCrake]

When linking to several databases it is more prudent to mke the links in the front end not creating a central repository of linked tables and then linking to that in the front end. Why? This means that everyone will be fighting for possession of the tables. By linking them directly fron the front end each user will have their own connection. Then the likely hood of conflicts is reduced.

One tip you could try is to perform the append query, then do a quick lookup to see if it has made it to the target table if so you are free to perform the delete query. I would also insert a DoEvents between each action to render Access to complete on action before progressing onto the next.

Eg:

Run Append Query
DoEvents
Test with DLookup for new record
If True
Run Delete query
Endif

 

[AlanJ10]

Sorry, in case I wasn't clear, everyone has a frontend with linked tables to the backends, there is no central repository of tables (infact I don't think access can link to linked tables can it?)

I realise that I could check to see if the record exists in the new table before deleting, but that's not something I want to have in place in the long term for two reasons i) I want to try to minimise access to each database as much as possible, so inserting a record and then checking to see if it's been inserted properly is an unnecessary step ii) It doesn't solve the problem, the record will be in the wrong table (although at least it will still exist so I can manually recover things), so things will still essentially be broken.

This actually reminds me of another question I had, when using docmd.runsql, if setwarnings is false, you get a popup box telling you how many records will be affected. Also, if you're trying to insert records and some of them can't be inserted, you get another popup telling you that x records have failed due to key violations, validation rules, etc, is there anyway to use those values in my module?

 

[DCrake]

If you are running a query from vba using the DoCmd.RunSQL YourSQLHere

you would normally wrap DoCmd.SetWarnings False / True around it to supress the message box. In your case you would just ensure that DoCmd.SetWarnings True was set prior to the DoCmd.RunSQL

 

[AlanJ10]

If you are running a query from vba using the DoCmd.RunSQL YourSQLHere

you would normally wrap DoCmd.SetWarnings False / True around it to supress the message box. In your case you would just ensure that DoCmd.SetWarnings True was set prior to the DoCmd.RunSQL

Sorry, I'm explaining myself really badly. I don't want to set DoCmd.SetWarnings True because I don't want the enduser to see it/have to click ok, I was just wondering if there was anyway to capture the number of records affected by the SQL and then use that in my code, ie.

docmd.setwarnings false
docmd.runsql MySQLHere
if x = 0 then panic here
docmd.setwarnings true

where x is the number of records affected by the SQL. Similarly, if the query attempted to append 100 records, but 50 failed due to validation rules, is there a way to capture that value as well?

 

[DCrake]

There is a .RecordsAffected intellisence option but you have to use a different method of appending the records to the table. If you do a search on .RecordsAffected you should get the correct syntax. I have heard of it but never used it.

 

[AlanJ10]

That looks pretty handy, thanks a lot.