Error running update query after changing from XLSX to CSV

Hi Experts,

I have a linked table to a file in XLSX format, trying to change it to link to a CSV format instead, and having the following issue.

I have a query updating my Access table according to some values in that linked Excel table by joining two fields.

However since converting file to CSV format, I'm getting the error attached .1
This is the SQL.

UPDATE Skilled_Nursing_Visit_Note INNER JOIN V_Visit_Note_Export ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID SET Skilled_Nursing_Visit_Note.Client_Last_Name = V_Visit_Note_Export.Client_Last_Name, Skilled_Nursing_Visit_Note.Status = V_Visit_Note_Export.Status
WHERE (((Skilled_Nursing_Visit_Note.Status)="draft") AND ((V_Visit_Note_Export.Status)="completed"));

Open in new window


Note - Skilled_Nursing_Visit_Note is an Access table, V_Visit_Note_Export is the CSV linked table.
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try using an outer join to V_Visit_Note_Export
Dale FyeOwner, Developing Solutions LLCCommented:
I have not tried to do this in years.  At one time, there was a lawsuit that forced Microsoft to make the link to Excel files read only.  It had to do with a copyright infringement, and I don't know that that has ever changed.  The only way I am aware of to change values in Excel is to use automation to actually open the Excel file and push values into individual cells.

And I don't thing you can update a linked CSV file from Access either, although I must admit that I have never tried.  I would expect that if you want to make changes to the CSV file, after importing the data, you would simply rewrite the CSV file.
Fabrice LambertConsultingCommented:
Keep in mind that Excel files, CSV files are not database tables, and ADODB drivers are just a conveniant interfaces to perform data operations.
Not all operations are supported.

Your best bet, is to import your data in a table, run your update / delete / insert queries, then export your data.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The issue certainly is that Access thinks it has to be able to update both joined tables, though in fact only one of them should be changed ..,.
bfuchsAuthor Commented:
Hi Experts,
Try using an outer join to V_Visit_Note_Export
when using outer join I get syntax error in update statement.
Do you mean left or right join?


@Dale,
I'm not trying to update CSV table, I am looking to update the Access table according to value in CSV table.

Your best bet, is to import your data in a table, run your update / delete / insert queries, then export your data.
Prefer not do have to do that, if possible...as this will double the process for every update I have (and planning to make in the future..).

The issue certainly is that Access thinks it has to be able to update both joined tables, though in fact only one of them should be changed ..,.
I was assuming that is the issue, however wondering why would that (same exact query) work when V_Visit_Note_Export is a XLSX file and not when its a CSV file?

Thanks,
Ben
bfuchsAuthor Commented:
Tried using Left join and still same issue.

Thanks,
Ben
Fabrice LambertConsultingCommented:
I was assuming that is the issue, however wondering why would that (same exact query) work when V_Visit_Note_Export is a XLSX file and not when its a CSV file?
It isn't the same driver.
bfuchsAuthor Commented:
It isn't the same driver.
I see, but is there something I can do about it, like force it to use different driver etc...?
Thanks,
Ben
Fabrice LambertConsultingCommented:
https://www.connectionstrings.com/
But don't hold your breath too much.
bfuchsAuthor Commented:
FYI- I was using XLSX till now and see what's forcing me to change file format...Perhaps you can solve that issue, so I can continue using XLSX file (like killing two birds with one shot-:)
https://www.experts-exchange.com/questions/29127720/Mapping-Excel-fields-when-linking-to-Access-app.html#a42749712
Thanks,
Ben
bfuchsAuthor Commented:
@Fabrice,

Actually I thought of another approach, creating a function that would open two recordsets and perform the update.
The need here is to loop thru all Access fields in update according to whats on the CSV file.
So the function would accept 3 parameters, (Access table name, CSV table name, the Where clause)

Perhaps you can help building the function, similar to what you did in the past...
https://www.experts-exchange.com/questions/29110361/Constructing-a-SQL-dynamically-by-looping-thru-all-fields-in-a-table.html#a42632029

Thanks,
Ben
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The standard ANSI SQL transformation is (hopefully):
UPDATE Skilled_Nursing_Visit_Note
SET
  Client_Last_Name = (select Client_Last_Name from V_Visit_Note_Export
                       where Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
                         and V_Visit_Note_Export.Status = "completed"),
  Status = (select status from V_Visit_Note_Export
                       where Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
                         and V_Visit_Note_Export.Status = "completed"),
WHERE
   Status = "draft" and exists (
      select * from V_Visit_Note_Export
       where Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
         and V_Visit_Note_Export.Status ="completed"
   )

Open in new window

I've intentionally left the STATUS as a subquery (to allow to see the template), though of course it always will be a constant "completed" for those rows.
bfuchsAuthor Commented:
@Qlemo,
See attached.
Thanks,
Ben
Untitled.png
Fabrice LambertConsultingCommented:
Well, if a query can't update, I doubt a recordset can.
bfuchsAuthor Commented:
Well, if a query can't update, I doubt a recordset can.
I mentioned two recordsets, one for the access table and one for the csv,
The CSV will be used for lookup values to update only.
Will need a way to keep both recordsets in sync while we loop thru all records.
what is the problem with that?
Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,

Actually I went looking thru my notes and realized I once had almost exact need, update one table according to the other, and did that logic in a  function, just got stuck in having successfully put both recordsets in sync...

See this below..
https://www.experts-exchange.com/questions/29104646/looping-thru-all-table-fields-and-constructing-a-SQL-update-string.html#a42596845

And further down...
https://www.experts-exchange.com/questions/29104646/looping-thru-all-table-fields-and-constructing-a-SQL-update-string.html#a42597797

So perhaps you can help me put that code into work...

Thanks,
Ben
BitsqueezerCommented:
Hi Ben,

the problem is once again the very bad Access SQL.

Access has really problems with subqueries, if you use subqueries Access doesn't allow to use this construct updatable. That's why we need to use DLookup in such cases.

Variant 1:

UPDATE Skilled_Nursing_Visit_Note AS SNVN
SET SNVN.Client_Last_Name = Nz(DLookup("Client_Last_Name","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID), SNVN.Client_Last_Name),
SNVN.STATUS = Nz(DLookup("STATUS","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID), SNVN.STATUS)
WHERE SNVN.STATUS ="draft";

Open in new window


This works but has the disadvantage that each and every row of SNVN will be updated, either with the new value or if no value could be found in the CSV with the original value in SNVN again (that's what Nz does).

This would not be very fast and also it updates rows which don't need to be updated so again a DLookup can be used to only update the needed rows:

Variant 2:
UPDATE Skilled_Nursing_Visit_Note AS SNVN
SET SNVN.Client_Last_Name = DLookup("Client_Last_Name","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID),
SNVN.STATUS = DLookup("STATUS","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID)
WHERE SNVN.STATUS ="draft"
AND SNVN.SNV_ID = DLookup("SNV_ID","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID);

Open in new window


Fortunately Access allows to also use a sub-select here:

Variant 3:
UPDATE Skilled_Nursing_Visit_Note AS SNVN
SET SNVN.Client_Last_Name = DLookup("Client_Last_Name","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID),
	SNVN.STATUS 		  = DLookup("STATUS","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID)
WHERE SNVN.STATUS ="draft"
AND EXISTS (SELECT 1 FROM Skilled_Nursing_Visit_Note AS SNVN1 INNER JOIN V_Visit_Note_Export AS VEXP ON SNVN1.SNV_ID = VEXP.SNV_ID
WHERE SNVN1.SNV_ID = SNVN.SNV_ID
AND SNVN1.STATUS = "draft"
AND VEXP.STATUS = "completed")

Open in new window


The last variant assumingly is the most performant one. But if you link a SQL Server backend to a CSV in Access SQL I think it will not be very fast at all. I would not use this approach, in cases where I needed to import something from an Excel or CSV file I upload the file to a resource where SQL Server can find it and then use a stored procedure in SQL Server to load the file, check veeeeery deep if the contents is allowed to update my valuable data and then update the real table.

I hope I did not forget a WHERE clause above as I cannot test the queries but I think you got the idea and you can adjust the rest on your own. I tested all three variants with an easier table construct on a test Access database file and all three are working. The last one has the disadvantage that Access asks with an extra message box about updating the external table, the other ones not.

Cheers,

Christian
bfuchsAuthor Commented:
Hi Bit,

Tested your latest (Variant 3) which should be the fastest and its taking forever.

Actually we dont need the exists part as its joining the snvID which is unique in both tables.

I changed to the below

UPDATE Skilled_Nursing_Visit_Note AS SNVN SET SNVN.Client_Last_Name = DLookUp("Client_Last_Name","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID), SNVN.STATUS = DLookUp("STATUS","V_Visit_Note_Export","STATUS='completed' AND SNV_ID =" & SNVN.SNV_ID)
WHERE SNVN.STATUS="draft"  or SNVN.SNV_ID="SNV26599";

Open in new window


but still not finishing to execute...(and tables are not that large, Access has 25k and CSV only 5k).

Note - Added the following OR SNVN.SNV_ID="SNV26599" So I know there will be one record to update...

Besides, we have to take in consideration that this will be used very frequent, and therefore focus on the max performance...

Therefore I think the best (and perhaps only) way to accomplish it would be the function mentioned above.

If having it in one function by synchronizing two recordsets is not doable, I would suggest two functions, one looping thru all records and where needs to be updated, the other accepting just a key (SNVID) that will open two recordsets and change the Access table according to whats on the CSV file.

Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,

The following is the 2nd function I was referring to

Public Function UpdateRecordFromCSV(sAccTable As String, sCSVTable As String, sPK As String, sPKValue As String) As String
    Dim rstSource As DAO.Recordset
    Dim rstDestination As DAO.Recordset
    Dim i As Integer, b As Boolean
    Set rstSource = CurrentDb.OpenRecordset("SELECT * FROM [" & sCSVTable & "] WHERE " & sPK & " = '" & sPKValue & "'")
    Set rstDestination = CurrentDb.OpenRecordset("SELECT * from " & sAccTable & " WHERE " & sPK & " = '" & sPKValue & "'")
        rstSource.MoveFirst
        rstDestination.MoveFirst
            rstDestination.Edit
            For i = 1 To rstDestination.Fields.count - 1 ' It starts from 1 to avoid .Field(0) that probably is Autonumber...if not adjust accordingly
                On Error Resume Next
                If Nz(rstDestination.Fields(i)) <> Nz(rstSource.Fields(rstDestination.Fields(i).Name)) Then
                    If Err.Number <> 3265 Then
                        rstDestination.Fields(i) = rstSource.Fields(rstDestination.Fields(i).Name)
                    End If
                End If
            Next
            rstDestination.Update
    Set rstSource = Nothing
    Set rstDestination = Nothing
End Function

Open in new window


And now we need a function that will call that function supplying all records that are not the same.

Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,

I just finished putting together the 1st function.

Public Function CallUpdateRecordFromCSV(sAccTable As String, sCSVTable As String, sPK As String) As String
    Dim db As DAO.Database
    Set db = CurrentDb

    Dim rs As Recordset
    Dim rs2 As Recordset
    
    Set rs = db.OpenRecordset(sCSVTable, dbReadOnly, dbSeeChanges)
    
    

        '// Add Mandatory columns
    Dim SQL As String
    SQL = vbNullString
    SQL = SQL & "SELECT " & sAccTable & "." & sPK & vbCrLf
    SQL = SQL & " From " & sAccTable
    SQL = SQL & " Inner join " & sCSVTable
    SQL = SQL & " on " & sAccTable & "." & sPK & " = "
    SQL = SQL & sCSVTable & "." & sPK
    SQL = SQL & " where "
    

        '// Add DatTime type columns
    Dim fld As DAO.Field
    For Each fld In rs.Fields
            SQL = SQL & "cstr(nz(" & sAccTable & ".[" & fld.Name & "],' ')) <> cstr(nz(" & sCSVTable & ".[" & fld.Name & "],' ' ))"
            SQL = SQL & " OR "
    Next

        '// strip the last comma
    SQL = Left(SQL, Len(SQL) - 3)
    
    Set rs2 = CurrentDb.OpenRecordset(SQL)
    
    rs.OpenRecordset
    While Not rs.EOF
         Call UpdateRecordFromCSV(sAccTable, sCSVTable, sPK, rs2(0))
         rs2.MoveNext
    Wend
    
Set rs = Nothing
Set db = Nothing
Set tdf = Nothing

    
End Function

Open in new window


The problem with that is that almost every record is listed there as not matching...

What are your thoughts...?

Thanks,
Ben
BitsqueezerCommented:
Hi Ben,

Hard to believe that this function will work in general. You use "rs2.MoveNext" and test "rs.EOF", this would never reach the end. I think rs is empty if it ran so the Update call was also never reached. Why do you open a recordset with "dbSeeChanges" when you also open it as "Read Only"?

I'm pretty sure that such function is not faster than the UPDATE SQL command above. You should also change the comments so that they fit to the code..;-)

At least I would open a recordset of a JOINed query with all your WHERE conditions and the PK and all fields you want to transfer from the CSV file, then you have everything you need to update the table from the CSV, let's say, 100 of the 5K rows (ordered by PK). Then you can loop through these 100 found rows and use the PK to update the values of this recordset with a second recordset on the table you want to update. In this case you can use "FindFirst" to jump to the PK found in the first recordset, update the values with the Edit method coming from the first recordset and go to the next row of the first recordset. As it is ordered by PK "FindFirst" doesn't need to jump back to a previous row.

Cheers,

Christian
bfuchsAuthor Commented:
Hi Bit,

You use "rs2.MoveNext" and test "rs.EOF"
Good catch.

Why do you open a recordset with "dbSeeChanges" when you also open it as "Read Only"?
You right, and in this case since its an Access table it works w/o the dbseechanges, however I experienced with SQL tables instances, that it would require dbseechanges although dbreadonly option was specified.

At least I would open a recordset...you can use "FindFirst"...
This is exactly what I was trying to accomplish at the comment above, read that link where I was getting stuck...
https://www.experts-exchange.com/questions/29128549/Error-running-update-query-after-changing-from-XLSX-to-CSV.html#a42750877

The problem I'm having with this approach is as mentioned, its considering all records as not matching.

Perhaps you have an answer to the following, which would solve this issue as well...(both are focusing on same task, here I'm trying thru CSV file while there I'm trying same with Excel)
https://www.experts-exchange.com/questions/29127720/Mapping-Excel-fields-when-linking-to-Access-app.html?anchor=a42752950¬ificationFollowed=219708911#a42752950

You should also change the comments so that they fit to the code..;-)
This were intentionally left there to find out if you guys are getting to read my code-:)


Thanks,
Ben
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.