Missing records when inserting

[mihabaki]

Hi!

I hope this is the right section for posting this Q.

I use Access 2003 on WinXP pro as front-end & for back-end a MySql on a Linux server. I use MyODBC to connect to the back-end and all the tables are on the back-end. The workstation is connected to the server via VPN (so the server and the station are on different locations).

Quite often I get a problem that not all records are inserted into a table. E.g. I have like 5 - 15 records (up to 10 fields) in one table and I want to transfer/copy them to another:

strSql = "INSERT INTO tblDetailNakup " & _
          "SELECT tblDetailNakupTemp.* " & _
         "FROM tblDetailNakupTemp;"
docmd.runsql strSql

most of the time it works OK, but from time to time a couple of records are missing.

What could be the problem? Is there any way for somekind of a check, if all has been inserted otherwise the query is repeated?

TNX in advance,
Miha

 

[jdraw]

Hi!

I hope this is the right section for posting this Q.

I use Access 2003 on WinXP pro as front-end & for back-end a MySql on a Linux server. I use MyODBC to connect to the back-end and all the tables are on the back-end. The workstation is connected to the server via VPN (so the server and the station are on different locations).

Quite often I get a problem that not all records are inserted into a table. E.g. I have like 5 - 15 records (up to 10 fields) in one table and I want to transfer/copy them to another:

strSql = "INSERT INTO tblDetailNakup " & _
          "SELECT tblDetailNakupTemp.* " & _
         "FROM tblDetailNakupTemp;"
docmd.runsql strSql

most of the time it works OK, but from time to time a couple of records are missing.

What could be the problem? Is there any way for somekind of a check, if all has been inserted otherwise the query is repeated?

TNX in advance,
Miha

You have an interesting set up. I haven't tried such a set up but here are a few questions that you may have considered already.

Is there anything else running against the back end at the same time as your queries?
Have you tried running your query as part of a transaction -BeginTrans EndTrans?

Have you tried writing to a log.
Write a comment about your intent.
Run the query.
Check the record count on the table see if has increased.
If it hasn't increased -write some key info (whatever that may be) to your log.
Does MySQL lock the Table when your query is trying to INSERT?
Is MySQL ignoring some of your requests?

 

[mihabaki]

Fixed it. The problem was that due to some othe part of the code (in certain cases) I had some primary key violentions.

Tnx for the help.
M