Linking records from two tables into an intersection, it only links a small part

**SaraM** · 03-21-2018, 02:26 PM

For my internship, I've to link specific objects (tblObject, fldObject) to files (tblFiles, fldFiles) with the corresponding numbers. So let's for example say the object is TM002, if there is a file with the name 'TM002.jpg', it will link automatically when you press a button and put them into tblObjectFile.

The problem I have is that I have to link around 143,000 files and when I press the button, it only connects 18,000 of them.

Code:

Private Sub cmdLink_Click()
Dim vDB As DAO.Database
Dim vRST As DAO.Recordset
Dim strSQLObject As String
Dim strSQLFiles As String
Set vDB = CurrentDb
strSQLObject = "SELECT * from tblobject order by objectnumber"
Set vRST = vDB.OpenRecordset(strSQLObject, dbOpenDynaset, dbSeeChanges)
vRST.MoveFirst
Do While Not vRST.EOF
    strSQLFiles= "INSERT INTO tblObjectFile (fldobjectid,fldFileID)"
    strSQLFiles = strSQLFiles & " SELECT " & vRST!objectid & ", fldFileID FROM tblfile where instr(fldfile," & Chr(34) & vRST!Objectnumber & Chr(34) & ") >0 "
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQLFiles
    vRST.MoveNext
Loop
End Sub

**orange** · 03-21-2018, 02:33 PM

Welcome to the forum.
Can you show us the table designs? Also, could you give us a little more context for your set up?
Perhaps a jpg of the form iinvolved?

Do you get an error?
Do you see a pattern of what files are not connected?

For my internship

at/to/for what?

Objects?

**isladogs** · 03-21-2018, 02:41 PM

When it stopped was there an error? If so what?
Once you have linked those 18000 does it link any more if you click the button again?

Suggest you add error handling to the sub to help determine the issue

Also you have turned off warnings but not switched them on afterwards.

Suggest replace with

Code:

CurrentDB.Execute strsqlfiles, dbfailonerror

**kd2017** · 03-21-2018, 02:55 PM

SaraM,
I've modified your code to add a little error handling as Ridders suggested. You could run this and see if it presents you with an error message.

Code:

Private Sub cmdLink_Click()
On Error Goto ErrHandler
  Dim vDB As DAO.Database
  Dim vRST As DAO.Recordset
  Dim strSQLObject As String
  Dim strSQLFiles As String

  Set vDB = CurrentDb

  strSQLObject = "SELECT * from tblobject order by objectnumber"
  Set vRST = vDB.OpenRecordset(strSQLObject, dbOpenDynaset, dbSeeChanges)


  'DoCmd.SetWarnings False
  vRST.MoveFirst
  Do While Not vRST.EOF
      strSQLFiles= "INSERT INTO tblObjectFile (fldobjectid,fldFileID)"
      strSQLFiles = strSQLFiles & " SELECT " & vRST!objectid & ", fldFileID FROM tblfile where instr(fldfile," & Chr(34) & vRST!Objectnumber & Chr(34) & ") >0 "
      'DoCmd.RunSQL strSQLFiles
      vDB.Execute strSQLFiles, dbFailOnError 'Per ridders52
      vRST.MoveNext
  Loop

  vRST.Close
  vDB.Close

ExitHandler:
  'DoCmd.SetWarnings True
  Set vRST = Nothing
  Set vDB = Nothing
  Exit Sub

ErrHandler:
  Msgbox "Error in cmdLink_Click #" & Err.Number & ": " & Err.Description
  Resume ExitHandler

End Sub

**SaraM** · 03-21-2018, 03:09 PM

Originally Posted by orange

Welcome to the forum.
Can you show us the table designs? Also, could you give us a little more context for your set up?
Perhaps a jpg of the form iinvolved?

Do you get an error?
Do you see a pattern of what files are not connected?

at/to/for what?

Objects?

Click image for larger version.

Name: test.PNG
Views: 21
Size: 32.9 KB
ID: 33224

I get no error, it just does the first 18,000 lines, after that it just stops running. And if I run press again, it just does the first 143,000 lines again.

So I've to design a new program as part of my internship for a French company. You have three tables: tblObject (each object stands for a building, so for example building 1 is TS455) and tblFile (contains the images or reports linked to the building in Object, so for example TS455.jpg) . One object can have several files but a file can only be assigned to one object

I've a third table, tblObjectFile and each time a match is found between an object and and a file it will get linked and inserted into tblOBjectFile

**isladogs** · 03-21-2018, 03:22 PM

I get no error, it just does the first 18,000 lines, after that it just stops running. And if I run press again, it just does the first 143,000 lines again.

Did you mean that?

**SaraM** · 03-21-2018, 03:41 PM

Originally Posted by ridders52

Did you mean that?

Click image for larger version.

Name: 12.PNG
Views: 19
Size: 3.7 KB
ID: 33225

I get this error: Error in cmdKoppel1_Click, can't find the element #3265 in the collection.

**isladogs** · 03-21-2018, 05:00 PM

I get no error, it just does the first 18,000 lines, after that it just stops running. And if I run press again, it just does the first 143,000 lines again.

I meant if you re-run does it really do 143000 lines rather than 18000?

Error 3265 is a recordset error - item not found in this collection
Have a look at the first record NOT entered - what is different about it?
Often you can skip that error

Building on the updated version by kd2017....lets see what happens if you ignore error 3265:

Code:

Private Sub cmdLink_Click()

On Error Goto ErrHandler
  Dim vDB As DAO.Database
  Dim vRST As DAO.Recordset
  Dim strSQLObject As String
  Dim strSQLFiles As String

  Set vDB = CurrentDb

  strSQLObject = "SELECT * from tblobject order by objectnumber"
  Set vRST = vDB.OpenRecordset(strSQLObject, dbOpenDynaset, dbSeeChanges)

  vRST.MoveFirst
  Do While Not vRST.EOF
      strSQLFiles= "INSERT INTO tblObjectFile (fldobjectid,fldFileID)"
      strSQLFiles = strSQLFiles & " SELECT " & vRST!objectid & ", fldFileID FROM tblfile where instr(fldfile," & Chr(34) & vRST!Objectnumber & Chr(34) & ") >0 "
      vDB.Execute strSQLFiles, dbFailOnError 'Per ridders52
      vRST.MoveNext
  Loop

  vRST.Close
   'vDB.Close 'NO - don't close the current database!

  Set vRST = Nothing
  Set vDB = Nothing

ExitHandler:     
  Exit Sub

ErrHandler:
  If err=3265 Then Resume Next 'item not found in this collection
  Msgbox "Error in cmdLink_Click #" & Err.Number & ": " & Err.Description
  Resume ExitHandler
 End Sub

Does it get further or better still, now complete all records?
If it still doesn't complete, look at the first record NOT completed for clues

**orange** · 03-21-2018, 05:10 PM

Further to ridders52 request, do you have 143000 objects and 143000 files?

**ssanfu** · 03-21-2018, 09:11 PM

I am surprised that the code runs without errors....

Click image for larger version.

Name: test.PNG
Views: 18
Size: 15.2 KB
ID: 33227

Looking at ridders52 code mods example (better that the original)

Code:

    Do While Not vRST.EOF
        strSQLFiles = "INSERT INTO tblObjectFile (fldobjectid,fldFileID)"
        strSQLFiles = strSQLFiles & " SELECT " & vRST!objectid & ", fldFileID "
        strSQLFiles = strSQLFiles & " FROM tblfile where instr(fldfile," & Chr(34) & vRST!Objectnumber & Chr(34) & ") >0 "
        vDB.Execute strSQLFiles, dbFailOnError    'Per ridders52
        vRST.MoveNext
    Loop

The table "tblObjectFile", does not have a field named "fldobjectid". There IS a field named "objectid".

The table "tblfile", does not have a field named "fldfile". There IS a field named "fldfiles". (with an "s")

Am I missing something????

In my perfect world (in my head

), I would have a suffix of "_PK" for the primary key fields and a suffix of "_FK" for the foreign key fields.

I would also like to see the dB with some records....... or some sample data for the tables and the expected results....
I haven't fully figured out the logic nor the sub query Select statement....

**SaraM** · 03-22-2018, 03:14 AM

Originally Posted by ridders52

I meant if you re-run does it really do 143000 lines rather than 18000?

Error 3265 is a recordset error - item not found in this collection
Have a look at the first record NOT entered - what is different about it?
Often you can skip that error

Building on the updated version by kd2017....lets see what happens if you ignore error 3265:

Code:

Private Sub cmdLink_Click()

On Error Goto ErrHandler
  Dim vDB As DAO.Database
  Dim vRST As DAO.Recordset
  Dim strSQLObject As String
  Dim strSQLFiles As String

  Set vDB = CurrentDb

  strSQLObject = "SELECT * from tblobject order by objectnumber"
  Set vRST = vDB.OpenRecordset(strSQLObject, dbOpenDynaset, dbSeeChanges)

  vRST.MoveFirst
  Do While Not vRST.EOF
      strSQLFiles= "INSERT INTO tblObjectFile (fldobjectid,fldFileID)"
      strSQLFiles = strSQLFiles & " SELECT " & vRST!objectid & ", fldFileID FROM tblfile where instr(fldfile," & Chr(34) & vRST!Objectnumber & Chr(34) & ") >0 "
      vDB.Execute strSQLFiles, dbFailOnError 'Per ridders52
      vRST.MoveNext
  Loop

  vRST.Close
   'vDB.Close 'NO - don't close the current database!

  Set vRST = Nothing
  Set vDB = Nothing

ExitHandler:     
  Exit Sub

ErrHandler:
  If err=3265 Then Resume Next 'item not found in this collection
  Msgbox "Error in cmdLink_Click #" & Err.Number & ": " & Err.Description
  Resume ExitHandler
 End Sub

Does it get further or better still, now complete all records?
If it still doesn't complete, look at the first record NOT completed for clues

Access goes to "not responding" immediaitely when I start it, if I wait for like 10 minutes and start it up it has like linked 12 000 files to objects but if I let it run for too long Access fully crashes

**SaraM** · 03-22-2018, 03:20 AM

Originally Posted by orange

Further to ridders52 request, do you have 143000 objects and 143000 files?

No, I've around 5600 objects and 143,000 files. Objects need to be linked with the files who have the same number in their 'name' and then be put in tblObjectFile

Originally Posted by ssanfu

I am surprised that the code runs without errors....

Looking at ridders52 code mods example (better that the original)

Code:

    Do While Not vRST.EOF
        strSQLFiles = "INSERT INTO tblObjectFile (fldobjectid,fldFileID)"
        strSQLFiles = strSQLFiles & " SELECT " & vRST!objectid & ", fldFileID "
        strSQLFiles = strSQLFiles & " FROM tblfile where instr(fldfile," & Chr(34) & vRST!Objectnumber & Chr(34) & ") >0 "
        vDB.Execute strSQLFiles, dbFailOnError    'Per ridders52
        vRST.MoveNext
    Loop

The table "tblObjectFile", does not have a field named "fldobjectid". There IS a field named "objectid".

The table "tblfile", does not have a field named "fldfile". There IS a field named "fldfiles". (with an "s")

Am I missing something????

In my perfect world (in my head

), I would have a suffix of "_PK" for the primary key fields and a suffix of "_FK" for the foreign key fields.

I would also like to see the dB with some records....... or some sample data for the tables and the expected results....
I haven't fully figured out the logic nor the sub query Select statement....

I translated the tables from my native language to English to make it a bit easier to understand, my apologize for using the wrong names

**isladogs** · 03-22-2018, 04:00 AM

Originally Posted by SaraM

I've around 5600 objects and 143,000 files. Objects need to be linked with the files who have the same number in their 'name' and then be put in tblObjectFile
I translated the tables from my native language to English to make it a bit easier to understand, my apologize for using the wrong names

Following on from ssanfu's comments, do the field names in your SQL statement match those in your tables?

Originally Posted by SaraM

Access goes to "not responding" immediaitely when I start it, if I wait for like 10 minutes and start it up it has like linked 12 000 files to objects but if I let it run for too long Access fully crashes

In order for anyone to be able to advise further, please answer these questions - at least one has been asked twice before

1. Not responding issue - Is that the first time or after 18000 files have been linked?
2. Does it always stop at the same file or does it vary?
3. What is different about the first file it doesn't link?
4. Is there any indication that you could have a 'memory' issue? i.e. Access running out of resources so grinding to a halt

5. I'll ask again - did you mean what you wrote here?

I get no error, it just does the first 18,000 lines, after that it just stops running. And if I run press again, it just does the first 143,000 lines again.

If you re-run does it really do 143000 lines rather than 18000?

ALSO Please post the latest code that you are using

**SaraM** · 03-22-2018, 06:27 AM

Originally Posted by ridders52

Following on from ssanfu's comments, do the field names in your SQL statement match those in your tables?
Yes! They do match!

In order for anyone to be able to advise further, please answer these questions - at least one has been asked twice before

1. Not responding issue - Is that the first time or after 18000 files have been linked?
This starts right off when I press the button to link the files
2. Does it always stop at the same file or does it vary?
This starts right off when I press the button to link the files
3. What is different about the first file it doesn't link?
With the new code I don't what the first file is that doesn't link anymore since it says "not responding" as soon as you press the button.
4. Is there any indication that you could have a 'memory' issue? i.e. Access running out of resources so grinding to a halt
No, tried this on a high ending gaming PC and a medium laptop, both stop responding as soon as you press the button

5. I'll ask again - did you mean what you wrote here?

If you re-run does it really do 143000 lines rather than 18000?

Like I mentioned on earlier, Access says "not responding" as soon as you press the link button.

ALSO Please post the latest code that you are using

This is the code I'm using:

Code:

Private Sub cmdLink_Click()
On Error Goto ErrHandler
  Dim vDB As DAO.Database
  Dim vRST As DAO.Recordset
  Dim strSQLObject As String
  Dim strSQLFiles As String

  Set vDB = CurrentDb

  strSQLObject = "SELECT * from tblobject order by objectnumber"
  Set vRST = vDB.OpenRecordset(strSQLObject, dbOpenDynaset, dbSeeChanges)

  vRST.MoveFirst
  Do While Not vRST.EOF
      strSQLFiles= "INSERT INTO tblObjectFile (fldobjectid,fldFileID)"
      strSQLFiles = strSQLFiles & " SELECT " & vRST!objectid & ", fldFileID FROM tblfile where instr(fldfile," & Chr(34) & vRST!Objectnumber & Chr(34) & ") >0 "
      vDB.Execute strSQLFiles, dbFailOnError 'Per ridders52
      vRST.MoveNext
  Loop

  vRST.Close
   'vDB.Close 'NO - don't close the current database!

  Set vRST = Nothing
  Set vDB = Nothing

ExitHandler:     
  Exit Sub

ErrHandler:
  If err=3265 Then Resume Next 'item not found in this collection
  Msgbox "Error in cmdLink_Click #" & Err.Number & ": " & Err.Description
  Resume ExitHandler End Sub

**isladogs** · 03-22-2018, 06:47 AM

OK - remove the test line

Code:

If err=3265 Then Resume Next 'item not found in this collection

as it didn't help
Please can you answer the questions again without that code line

What you are asking Access to do is a large task looping repeatedly through a lot of files
I suspect you do have a system resource issue as part of the problem but without precise answers to the original questions, anything I say is guesswork

Linking records from two tables into an intersection, it only links a small part

Thread Tools

Linking records from two tables into an intersection, it only links a small part

Similar Threads

Linking tables to select multiple records in a form

Event Logger Module Need some help to add a small part

Creating relational links between tables

Access 2007: Linking tables via ODBC will lose some fields unless linking as trusted

How to Clear Tables Links & Re-esteblish them on Runtime?

Posting Permissions