0

For this job I'm working in VB.net front-end with back-end MS Access 2019. The issue: There are two separate Access databases each with a large table. OrigTable is where the update qry needs to put the info.

The current query (below) works fine for tables with less than 1 million rows, but fails with with larger tables due to the 2gig limit in Access.

This works:

Sql = "UPDATE (" & DBPath & "." & OrigTable & ") " _
    & "INNER JOIN (" & ExternalDBPath & "." & NewTable & ") " _
    & "ON ([" & OrigTable & "].[AUTONUM] = [" & NewTable & "].[AUTONUM]) " _
    & "SET " _
    & "[" & OrigTable & "].[VEHICLE1] = [" & NewTable & "].[VEHICLE1], " _
    & "[" & OrigTable & "].[VEHICLE2] = [" & NewTable & "].[VEHICLE2] "

My approach to avoiding the 2gig Access DB size crunch, is to limit the inner join update query to working with the first 50% of the table. Then I'll do the other stuff that needs to happen and work with the 2nd half of the table. What I'm trying (below) is yielding syntax error, and I'm guessing there's a better approach that I'm simply missing. Been working on this problem for over 2 hrs now and the solution escapes me.

Sql = "UPDATE (" & DBPath & "." & OrigTable & ") " _
    & "INNER JOIN (Select TOP 50 PERCENT * FROM (" & ExternalDBPath & "." & NewTable & ")) " _
    & "ON ([" & OrigTable & "].[AUTONUM] = [" & NewTable & "].[AUTONUM]) " _
    & "SET " _
    & "[" & OrigTable & "].[VEHICLE1] = [" & NewTable & "].[VEHICLE1], " _
    & "[" & OrigTable & "].[VEHICLE2] = [" & NewTable & "].[VEHICLE2] "

The 'top 50 percent *' thingy is documented as working in Access but I'm most likely using it wrong. I also think a LIMIT could work. Any help, thoughts etc much apprec'd.

djv
  • 15,168
  • 7
  • 48
  • 72
TheoH
  • 3
  • 5
  • 2
    Not really an answer I know, perhaps though if you're running into issues due to the limitations of Access, maybe it's time to evaluate other RDBM systems. More direct answer, you might be better off pulling required data from 2nd DB into memory for processing rather than trying to one step it in a single SQL statement. And final point, learn to parametrise queries, it's much better practice for many reasons than string concatenations – Hursey Jun 20 '23 at 20:41
  • 2
    _Been working on this problem for over 2 hrs now and the solution escapes me_: Two whole hours, wow. Unless you're an AI, 2 hours of research is equivalent to the blink of an eye. These days, it sometimes takes me two hours to figure out how to open Visual Studio again. – Tu deschizi eu inchid Jun 20 '23 at 20:48
  • Split your table approximately by autonum - fisrt update for `autonum < 'xxxxx'`, second one for `autonum >= 'xxxxx'`. – Arvo Jun 22 '23 at 09:14

0 Answers0