Insert into linked SQLite- Table doesn't work

[am2]

Hi, i got the following problem:

I want to Insert Data in a ODBC- linked table. If I do this via INSERT- Statement I get the Message "Ressources exceeded" (in German: "Systemressourcen nicht ausreichend")
Whatever I tried, the problem remained.

So I tried insert the Data record by record with the following code (not exactly, I tried to reduce the code to the main statements)
But in result I got Error 3155. If I do this for a record, there is no problem. I can't find out the special reason, maybe constraints or whatever.

Private Function copy_cell(rownum As Integer, rsZiel As Recordset, rsQuelle As Recordset, Index As Integer) As String
  Dim err_txt As String
  On Error GoTo ErrorMessage:
    copy_cell = ""
    rsZiel.Fields(Index+1) = pcrsQuelle.Fields(Index)
    Exit Function
  ErrorMessage:

    err_txt = "Row " & Str(rownum )

    err_txt = err_txt & " [" & rsZiel.Fields(Index +1).Name & "] "
    err_txt = err_txt & "<" & Err.Description & ">"
    copy_cell = err_txt
    Resume Next
End Function
 
private sub copy_table(TabelleZiel As String, TabelleQuelle As String)
  Set rsZiel = dbx.OpenRecordset(TabelleZiel)
  Set rsQuelle = dbx.OpenRecordset(TabelleQuelle)
 
  zahl = 0
  Do While Not rsQuelle.EOF
    zahl = zahl + 1
    rsZiel.AddNew
    rsZiel.Fields(0) = zahl 'Primärschlüssel
   
    'copy record field by field

    'it is a little simplified, because fields maybe not in the right order. But this I managed.

    For Index = 0 To rsZiel.Fields.Count - 1

      'result of copy_cell ist empty, if there is no error. err.descriotion is empty, whyever
      s = copy_cell(zahl, rsZiel, rsQuelle, Index)

    Next Index

    rsZiel.Update 'that is the place, where error appears

    rsQuelle.MoveNext

  loop


end sub

I am not sure, the code will work, because I eliminated less important lines for a better understanding.
I hope anybody can help me.

[Minty]

Can you manually add a record directly in the table?

Can you post up the SQL of your insert query?

[am2]

Destination table consists an autoincrement primary key field, that is not filled, because I could not find out, how to generate sequential numbers.
Thats why all columns are explicitly mentioned.
If I add a record manually (by copy column by column) it works, at least for the first rows.

INSERT INTO AB001041
  (VNK, NNK, VST, BST, FSLAGE, FSNUMMER, KLASSE, DECKSCHICH, BEWERTDAT, OBJEKTNR, ERFART, QUELLE, ADATUM, BEMERKUNG, BEARBEITER, STAND, OBJEKTID, FLAG)
SELECT 
  VNK, NNK, VST, BST, FSLAGE, FSNUMMER, KLASSE, DECKSCHICH, BEWERTDAT, OBJEKTNR, ERFART, QUELLE, ADATUM, BEMERKUNG, BEARBEITER, STAND, OBJEKTID, FLAG
FROM AB001041_INT;

This Query runs out of ressources ... (we talk about 100 000 records).

[Minty]

Well looping through a 100K records field by field is going to be painful and take an age.

Normally you simply exclude the primary key field from your insert statement and it will automatically insert the next unique key value.
That's what SQL server and Oracle do.

So your SQL insert should work, assuming you aren't adding 100k records in one go.
If you reduce the number of records down to 100 does it work?
If it does slowly increase the number of records and start timing the process. Once it gets to about 5 minutes tops, that is where I would say you need to stop and keep doing that many records in chunks.

I doubt you'll be able to do a 100k insert in one hit. You may need something like as SSIS equivalent for that sort of import.

[isladogs]

That code looks familiar. Is it from the German developer Andre Minhorst? If so, have you tried contacting him direct?

[am2]

Well looping through a 100K records field by field is going to be painful and take an age.

Maybe, but it would be no problem, if it would work. Once in a year I get a big datafile and have to prepare it for import.
The process of preparation is a little complicated, because data may be wrong. If I could determine records and columns, that don't match, it would be a benefit, that even may cost ages.
A more complex insert into the intern table AB001041_INT takes seconds, so the statement (w/o VBA) should not take too long.
I'm afraid that constraints and date- fields cause the error. But I need them and there are many, so I would prefer not to eliminate them one by one.

Normally you simply exclude the primary key field from your insert statement and it will automatically insert the next unique key value.
That's what SQL server and Oracle do.

So your SQL insert should work, assuming you aren't adding 100k records in one go.

That I hoped ...

If you reduce the number of records down to 100 does it work?
If it does slowly increase the number of records and start timing the process. Once it gets to about 5 minutes tops, that is where I would say you need to stop and keep doing that many records in chunks.

I'll try it out

I doubt you'll be able to do a 100k insert in one hit. You may need something like as SSIS equivalent for that sort of import.

One year ago it worked with dBASE- files w/o problems. For me it seems to be a ODBC- Problem.

That code looks familiar. Is it from the German developer Andre Minhorst? If so, have you tried contacting him direct?

I do not know him. Maybe he has a similar task. Every German federal state gets their data and has to include this in its own datastore. These stores differ from state to state, cause structures of organization differ.

[am2]

If you reduce the number of records down to 100 does it work?
If it does slowly increase the number of records and start timing the process. Once it gets to about 5 minutes tops, that is where I would say you need to stop and keep doing that many records in chunks.

I'll try it out

even 1 record exceeds the ressources

[Minty]

even 1 record exceeds the ressources

That sounds like a driver problem.

I would delete the ODBC connection and remove the driver.
Reboot - then go and find the latest version of the driver and reinstall from a fresh download.

See if that makes any difference. I have seen drivers lead to horrible performance, the Sage ODBC driver is a prime example.
I'm sure it is deliberately rubbish to sway you away from trying anything actually useful with automation.

[am2]

That sounds like a driver problem.

I would delete the ODBC connection and remove the driver.
Reboot - then go and find the latest version of the driver and reinstall from a fresh download.

See if that makes any difference. I have seen drivers lead to horrible performance, the Sage ODBC driver is a prime example.
I'm sure it is deliberately rubbish to sway you away from trying anything actually useful with automation.

The driver worked fine for other purposes and is freshly installed in last version. If not I am afraid of a lot of other problems coming soon
I still think, date- fields or constraints are the problem.

I think, I will try the other direction: If I can't push data from Access into DB I try to pull it out of access via python. Must wait, until admins installed a matching ODBC- driver for *.accdb- files.

Thank you for now. Is there any Idea why my program code only tells me "Doesn't work", although I manually can copy the row? And why there is no way to find out the precise reason?

[Minty]

I'm at a loss for any other ideas I'm afraid.
What happens if you create a simple select query on the table (Just select all the fields) and then try and insert a new record manually in that?
If it fails it might give you a more meaningful error.

[am2]

I'm at a loss for any other ideas I'm afraid.
What happens if you create a simple select query on the table (Just select all the fields) and then try and insert a new record manually in that?
If it fails it might give you a more meaningful error.

I can not try, because I do not know, how to fill the autoinc- Field.

I created a new SQlite- Table with same structure, but w/o constraints. Didn't work neither.

Actually I try to connect to Access via ODBC and to SQLite native (pyodbc). I hoped, I would find another way, but this way seems encouraging.