Updating a local table from a linked table workaround

[peskywinnets]

So it seems the the consensus is, that it's not possible to update a local table from a linked table.

I googled & found a workaround here ...

https://social.msdn.microsoft.com/F...with-data-from-a-linked-table?forum=accessdev (specifically the code added by Brent Spaulding Friday, January 27, 2012 3:00 PM)

...the problem is I'm not sure I understand it all!

Specifically the bit....




So let's say my linked table (MyCsvData_linked) has a field called AmazonOrderID [/B]& another field called AmazonDispatchedDate & my local table (MyCsvData_local) has the same AmazonOrderID but in a field called OrderID, how would I use the above to update the field called DispatchedDate in my local table?

Here's a diagramatic representation of what I'm hoping to achieve....

[B]MyCsvData_linked[/B].........................................................[B]MyCsvData_local[/B]
AmazonOrderID----------[I]these have the same values in both table[/I]--------------OrderID
AmazonDispatchedDate-------->>>>>[I]I wish to update this field[/I]-------->>>>>>DispatchedDate

 

[MarkK]

In order to only do update operations, the SQL in question only selects rows from the local data where the code exists in the linked data.

When you are ready to do inserts, you would use SQL like...

SELECT *
FROM tLinked As t
WHERE NOT t.Code IN ( SELECT t2.Code FROM tLocal As t2 )

See how that selects rows in the linked table having a Code that doesn't exist in the local table. Do that if you want to update the local table from the linked data.

But you would do your updates and inserts separately.

 

[sneuberg]

Wow I didn't want to believe that but I couldn't find a way to get directly around this ISAM thing either. Even if you just join the linked table with the local table the result cannot be updated. I wonder what that's about. Anyway I believe the follow code (adapted from the source you provided) will do what you want.

Sub UpdateLocalTable()

Dim rstSource As DAO.Recordset
Dim rstDestination As DAO.Recordset
Dim strSQL As String
Set rstSource = CurrentDb.OpenRecordset("MyCsvData_linked")
strSQL = "SELECT * FROM MyCsvData_local WHERE MyCsvData_local.OrderID IN (SELECT AmazonOrderID FROM MyCsvData_linked)"
Set rstDestination = CurrentDb.OpenRecordset(strSQL)
Do While Not rstSource.EOF
    rstDestination.FindFirst "[OrderID]= " & rstSource.Fields("AmazonOrderID")
    If Not rstDestination.NoMatch Then
        rstDestination.Edit
        rstDestination!DispatchedDate = rstSource!AmazonDispatchedDate
        rstDestination.Update
    End If
    rstSource.MoveNext
Loop
rstSource.Close
rstDestination.Close

End Sub

I've attached the database where I tested this code along with the CVS test file. The code is in the the module. You will have to link the CVS file it you want to see it work.

 

[sneuberg]

What makes this crazy is that you can make a temporary table of the linked table with:

SELECT MyCsvData_linked.AmazonOrderID, MyCsvData_linked.AmazonDispatchedDate INTO CopyOfLinkedTable
FROM MyCsvData_linked;

With that you can do a regular update query like:

UPDATE CopyOfLinkedTable INNER JOIN MyCsvData_local ON CopyOfLinkedTable.AmazonOrderID = MyCsvData_local.OrderID SET MyCsvData_local.DispatchedDate = [CopyOfLinkedTable]![AmazonDispatchedDate];

But its better to avoid temporary table and the possible bloat, but why can't Access do this for us internally?

 

[peskywinnets]

Wow I didn't want to believe that but I couldn't find a way to get directly around this ISAM thing either. Even if you just join the linked table with the local table the result cannot be updated. I wonder what that's about. Anyway I believe the follow code (adapted from the source you provided) will do what you want..

Thanks...I'm pleased that perhaps my line of questioning (& general n00bness) ...has maybe flushed out some new awareness/knowledge for you. Along the journey of discovery, I read somewhere that the only reason you can't update from a linked table has nothing to do with the technicals, but that Access's code was crippled because Microsoft lost a court case (dunno).

I really wish to pursue this 'updating from a linked table' option (vs. importing the data into my access database)....as I've had terrible problems with database bloat recently (that a compact/repair doesn't address), when constantly importing data to update existing local data.

Anyway, thanks a million...I'll try your code later & report back (as a relatively inexperienced VBA & database programmer, I struggle with some of the interpretation &/or the syntax...bear with me!)

 

[static]

Turn your OrderID fields into Primary Keys. Then your update should work.

 

[peskywinnets]

Wow I didn't want to believe that but I couldn't find a way to get directly around this ISAM thing either. Even if you just join the linked table with the local table the result cannot be updated. I wonder what that's about. Anyway I believe the follow code (adapted from the source you provided) will do what you want.

The codes works, except there's a slight complication in my actual deployment that stops it working!

To keep things simple, I gave you all different field names when posting the question in my opening post. In actual fact the field names in my linked table have hyphens in them, so rather than this ....

strSQL = "SELECT * FROM MyCsvData_local WHERE MyCsvData_local.OrderID IN (SELECT AmazonOrderID FROM MyCsvData_linked)"


....Due to the field names in my linked table, I need to use this....

strSQL = "SELECT * FROM MyCsvData_local WHERE MyCsvData_local.OrderID IN (SELECT amazon-order-id FROM MyCsvData_linked)"

The highlighted hyphens cause the next line of code.....

Set rstDestination = CurrentDb.OpenRecordset(strSQL)

to error like this...



Any top tips how I can modify the command to embrace the hyphens that are present in field names in my linked table?

 

[JANR]

Enclose amazon-order-id in square brackets

like so: [amazon-order-id]

JanR

 

[peskywinnets]

Enclose amazon-order-id in square brackets

like so: [amazon-order-id]

JanR

thanks that got rid of that error!

My next problem is that the Find.first....

rstDestination.FindFirst "[OrderID]= " & rstSource.Fields("amazon-order-id")


doesn't appear to be working...my line of thought is that it's not working because my OrderID (in both tables) is actually a string with hypens embedded, for example here's a sample OrderID in the destination table...

206-1747792-0665131

Brainache!

 

[sneuberg]

thanks that got rid of that error!

My next problem is that the Find.first....

rstDestination.FindFirst "[OrderID]= " & rstSource.Fields("amazon-order-id")


doesn't appear to be working...my line of thought is that it's not working because my OrderID (in both tables) is actually a string with hypens embedded, for example here's a sample OrderID in the destination table...

206-1747792-0665131

Brainache!

I made the assumption that they were numbers. I should have mentioned that. For text single quotes are needed and that line of code should be:

    rstDestination.FindFirst "[OrderID]= [COLOR="Red"]'[/COLOR]" & rstSource.Fields("amazon-order-id") [COLOR="red"]& "'"[/COLOR]

 

[peskywinnets]

Works like a charm - very happy n00b here! (Yay, I have exorcised the 'I can't update from a linked table' demons!)

 

[peskywinnets]

Just one last thing, without a where clause in the above, each time I run the code it's going to have to go through the whole of the table updating the DispatchedDate in the destination table.

How would I modify the code above to in essence saying ...

"Select only those records in the destination table where DispatchedDate is a null"

 

[sneuberg]

You can change the strSQL string as shown in red below

strSQL = "SELECT * FROM MyCsvData_local WHERE DispatchedDate Is Null AND MyCsvData_local.OrderID IN (SELECT [amazon-order-id] FROM MyCsvData_linked)"

I don't know if that will make much difference as the code executes

 rstDestination.FindFirst "[OrderID]= '" & rstSource.Fields("amazon-order-id") & "'"

for every record in the linked source table. Will there be null dates in the source? Should that be filtered too.

But in spite of the source be the driver I suppose it's a good idea to cut down the destination records as much as possible as the unavoidable subquery will be the real performance killer. Hopefully the optimizer will filter out the null dates before executing the subquery.

 

[peskywinnets]

You can change the strSQL string as shown in red below

strSQL = "SELECT * FROM MyCsvData_local WHERE DispatchedDate Is Null AND MyCsvData_local.OrderID IN (SELECT [amazon-order-id] FROM MyCsvData_linked)"

I don't know if that will make much difference as the code executes

 rstDestination.FindFirst "[OrderID]= '" & rstSource.Fields("amazon-order-id") & "'"

for every record in the linked source table. Will there be null dates in the source? Should that be filtered too.

But in spite of the source be the driver I suppose it's a good idea to cut down the destination records as much as possible as the unavoidable subquery will be the real performance killer. Hopefully the optimizer will filter out the null dates before executing the subquery.

Many Thanks...in the brief attempt I made with your code on my real world tables, I couldn't get any destination table recordset to be selected using your modified code...I will try again later using your test database (fyi: there will never be nulls in the linked/source table....& when I come to use this in the real world, 99% of the destination table's fields will not be a null for DispatchedDate ...which ought to reduce the time taken to update & is why I'm so keen to be able to use a where command)

 

[sneuberg]

Sorry, I should have tested that. Duh it needs a not

strSQL = "SELECT * FROM MyCsvData_local WHERE Not DispatchedDate Is Null AND MyCsvData_local.OrderID IN (SELECT [amazon-order-id] FROM MyCsvData_linked)"

 

[peskywinnets]

Sorry, I should have tested that. Duh it needs a not

strSQL = "SELECT * FROM MyCsvData_local WHERE Not DispatchedDate Is Null AND MyCsvData_local.OrderID IN (SELECT [amazon-order-id] FROM MyCsvData_linked)"

Thanks, but I still don't think it's working as expected, I have 112 records in my destination table with a null in the DispatchedDate field, so - if my understanding is correct re how the code is meant to work - then there oughtn't to be any more records selected than 112, but when I use this code...

(in the real world situation below, AmazonAFNOrders is my source/linked table & OrderList is my Destination/local table)

strSQL = "SELECT * FROM OrderList WHERE not DispatchedDate Is Null AND  OrderID IN (SELECT [amazon-order-id] FROM AmazonAFNOrders)" 'this needs some work
Set rstDestination = CurrentDb.OpenRecordset(strSQL)
rstDestination.MoveLast
Debug.Print rstDestination.RecordCount
rstDestination.MoveFirst

I get a recordcount of 1574 ?

 

[sneuberg]

Sorry again. I had it right the first time. Let's take out the not and figure out why that's not giving you what you expect. This query only has a result if there are matching order numbers in both tables. Is this want you want?

Could you upload the data you are working with and tell me what output you expect given what's in the tables. If you could export the OrderID and DisPatchedDate to a Excel spreadsheet that would suffice for the local table database. Same for the CVS file I only need the two relevant columns

 

[peskywinnets]

I've attached a test database - the structure should be reasonably obvious but nevertheless....

the linked table is to a text file called AmazonAFNOrders ...it has all the DispatchDate for all OrderIDs, the local table is called OrderList ...it needs updating with the DispatchDate for each OrderID from the linked text file (AmazonAFNOrders) both tables share a common field called OrderID.

I ideally only want those fields in table Orderlist which are presently null to be updated with the associated DispatchDate from the linked table (because in real life this will be a long table, & therefore iterating through the whole OrderList table will be wasteful of time & unnecessary)

 

[sneuberg]

I've imported the data you provided into my test database which is attached. This database also has a query name qryDestination which is copied from the code and has the following SQL

SELECT *
FROM MyCsvData_local
WHERE DispatchedDate Is Null AND  MyCsvData_local.OrderID IN (SELECT [amazon-order-id] FROM MyCsvData_linked);

If you run this query you should see that it selects records with matching order numbers and where the date is null in the local table. Do you see anything wrong with this? Also if you run the code I believe it updates the local table properly. Doesn't it?

 

[peskywinnets]

I've imported the data you provided into my test database which is attached. This database also has a query name qryDestination which is copied from the code and has the following SQL

SELECT *
FROM MyCsvData_local
WHERE DispatchedDate Is Null AND  MyCsvData_local.OrderID IN (SELECT [amazon-order-id] FROM MyCsvData_linked);

If you run this query you should see that it selects records with matching order numbers and where the date is null in the local table. Do you see anything wrong with this? Also if you run the code I believe it updates the local table properly. Doesn't it?

yes the query shows those records in the destination table where DispatchDate is null. And yes the code does update the DispatchDate destination table, but it's not just updating DispatchDate with null values ...but all DispatchDate records. You can replicate this but clearing out a couple of the DispatchDate fields in the destination table & then stepping through the VBA code...it loops 6 times (instead of just a couple for the null fields)