CODEWITHSUNDEEP
vb.netupdate-inner-join
TheoH
TheoH

Reputation: 3

Problems coding an Inner join update query for 2 large Access tables in different databases

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.

Upvotes: 0

Views: 39

Answers (0)

Related Questions