473,322 Members | 1,307 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Expected End of Statement thwarting attempt to update multiple tables

25
I'm trying to run an update query on multiple tables, and since Access doesn't allow me to update tables from a union query, I'm writing a module as a workaround. So I've set up a temporary recordest (rstName) from the union query (qryEqiupEmplOnly), and construced a Do-Loop that updates the corresponding table.

Unfortunately the Update SQL code prompts a compile error "Expected End of statement" and highlights "rstName" (line 28) as the culprit. Does anyone know how to fix it?

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub AssetUpdate()
  4.   Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
  5.   Dim n As Integer, Table As String
  6.  
  7.    Set db = CurrentDb
  8.    Set n = 1
  9.  
  10.    'Setup Recordset
  11.    SQL = "SELECT [Employee Name], SerialNo, AssetNo, Category " & _
  12.          "FROM qryEquipEmplOnly " & _
  13.          "ORDER BY [Employee Name];"
  14.    Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)
  15.  
  16.    'Parse thru tables & update asset numbers
  17.    Do Until rstName.EOF
  18.       Table = Switch(rstName!Category = "Computers", "tblComputers", _
  19.             rstName!Category = "Cameras", "tblDECameras", _
  20.             rstName!Category = "Docks", "tblDEDocks", _
  21.             rstName!Category = "MFCs", "tblDEMFCs", _
  22.             rstName!Category = "Monitors", "tblDEMonitors", _
  23.             rstName!Category = "Printers", "tblDEPrinters", _
  24.             rstName!Category = "Other DE", "tblDEOthers")
  25.  
  26.       SQL = "UPDATE " & Table & " " & _
  27.             "SET AssetNo = n " & _
  28.             "WHERE [Employee Name] = '" rstName![Employee Name] "' "& _
  29.             "AND SerialNo = '" & rstName!SerialNo & "';"
  30.       db.Execute SQL, dbFailOnError
  31.       Set n = n + 1
  32.       rstName.MoveNext
  33.    Loop
  34.  
  35.    Set rstName = Nothing
  36.    Set db = Nothing
  37. End Sub
  38.  
  39.  
Apr 23 '08 #1
4 2329
Stewart Ross
2,545 Expert Mod 2GB
Hi. You are missing two '&' operators in line 28. You also have an error in line 27, where you have accidentally put the reference to variable 'n' inside your SQL string. Revised lines shown below.

-Stewart

Expand|Select|Wrap|Line Numbers
  1. "SET AssetNo = " & n & _
  2. " WHERE [Employee Name] = '" & rstName![Employee Name] & "' "& _
  3.  
Apr 23 '08 #2
dstorms
25
Thank you Stewart. That did fix the problem I described above. However, I've run into a different problem. When I try running the code I an error "3061", too few parameters: expected 1. I've determined that it has to do with the Table parameter. Either I'm not getting the value I want or the query the recordset calls is too complex to run. (It's a qruery filtering out one employee name from a previous union query.)

Here's the modified code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub AssetUpdate()
  2.   Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
  3.   Dim n As Integer, Table As String
  4.  
  5.    Set db = CurrentDb
  6.    n = 1
  7.  
  8.    'Setup Recordset
  9.    SQL = "SELECT [Employee Name], SerialNo, AssetNo, Category " & _
  10.          "FROM qryEquipEmplOnly " & _
  11.          "ORDER BY [Employee Name];"
  12.    Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)
  13.  
  14.    'Parse thru tables & update asset numbers
  15.    Do Until rstName.EOF
  16.       Table = Switch(rstName!Category = "Computers", "tblComputers", _
  17.             rstName!Category = "Cameras", "tblDECameras", _
  18.             rstName!Category = "Docks", "tblDEDocks", _
  19.             rstName!Category = "MFCs", "tblDEMFCs", _
  20.             rstName!Category = "Monitors", "tblDEMonitors", _
  21.             rstName!Category = "Printers", "tblDEPrinters", _
  22.             rstName!Category = "Other DE", "tblDEOthers")
  23.  
  24.       SQL = "UPDATE " & Table & _
  25.             " SET [AssetNo] = " & n & _
  26.             " WHERE [Employee Name] = '" & rstName![Employee Name] & "' " & _
  27.             "AND [SerialNo] = '" & rstName!SerialNo & "';"
  28.       db.Execute SQL, dbFailOnError
  29.       n = n + 1
  30.       rstName.MoveNext
  31.    Loop
  32.  
  33.    Set rstName = Nothing
  34.    Set db = Nothing
  35. End Sub
  36.  
  37.  
Apr 24 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
Hi. I am assuming that the failure point is at the OpenRecordset line...

Could you check what the Table and SQL variables are set to and post these back so that the syntax of what is actually being passed to OpenRecordset can be seen? Set a breakpoint within your code, and step through each line one by one checking the values of the SQL string and the Table variable as you go.

Just as an aside, is your SerialNo field text? You are enclosing the reference to the field in single quotes, which is fine as long as it is a text field. If it is numeric then the single quotes are incorrect and should be removed.

-Stewart

ps Looking again at the Where clause in your update, does it really belong to the tables that are listed in your Switch statement? It would be unusual to have employee names in such tables, so I wonder if your Where clause actually relates to query qryEquipEmplOnly instead??? If this is the case you need to completely rethink your Update statement as it cannot work if the Where clause has no relation to the table being updated.
Apr 24 '08 #4
dstorms
25
Stewart,

It turned out that I needed to write a more complex SQL statement, which I did get to work:

Expand|Select|Wrap|Line Numbers
  1.       SQL = "UPDATE tblEmployees INNER JOIN " & Table & _
  2.             " ON tblEmployees.EmployeeID = " & Table & ".EmployeeID" & _
  3.             " SET " & Table & ".AssetNo = " & n & _
  4.             " WHERE (((tblEmployees.[Employee Name]) = '" & rstName![Employee Name] & "') " & _
  5.             "AND ((" & Table & ".SerialNo) = '" & rstName!SerialNo & "'));"
  6.       db.Execute SQL, dbFailOnError
  7.  
The old SQL statemnet was looking for [Employee Name] when the Table had a field [EmployeeID] instead. The [Employee Name] field was actually coming from the tblEmpoyees table, so I had to make the necessary adjustment.

With the new SQL statment it worked beautifullly.

Thanks for your help!
Apr 24 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: avinash | last post by:
hi myself avi i am developing one appliacaion in which i am using vb 6 as front end, adodb as database library and sql sever 7 as backend. i want to update one table for which i required data from...
8
by: pb648174 | last post by:
I have a single update statement that updates the same column multiple times in the same update statement. Basically i have a column that looks like .1.2.3.4. which are id references that need to...
2
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an...
1
by: vj | last post by:
How to Update multiple tables in a single SQL update Statement? Is there any way out? vj.
4
by: marty3d | last post by:
Hi! I'm trying to create one query by combining several smaller UPDATE, INSERT and DELETE queries. I know this is easily done in SQL Server using GO, but I can't figure out how to do it in...
9
by: jaYPee | last post by:
I have search a lot of thread in google newsgroup and read a lot of articles but still i don't know how to update the dataset that has 3 tables. my 3 tables looks like the 3 tables from...
1
by: Foef | last post by:
When I have a stored procedure, with multiple tables in MS Access, in a dataset and I change it in my DataGrid, I get the next message when I want to update my DataSet: ...
1
by: davidevan | last post by:
What I'm trying to do is set a players division according to their age. So if age is 8, update division to junior, if age is 9, update division to medium, if age is 10, update division to pee wee,...
5
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.