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 (qryEqiupEmplOn ly), 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? - Option Compare Database
-
-
Private Sub AssetUpdate()
-
Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
-
Dim n As Integer, Table As String
-
-
Set db = CurrentDb
-
Set n = 1
-
-
'Setup Recordset
-
SQL = "SELECT [Employee Name], SerialNo, AssetNo, Category " & _
-
"FROM qryEquipEmplOnly " & _
-
"ORDER BY [Employee Name];"
-
Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)
-
-
'Parse thru tables & update asset numbers
-
Do Until rstName.EOF
-
Table = Switch(rstName!Category = "Computers", "tblComputers", _
-
rstName!Category = "Cameras", "tblDECameras", _
-
rstName!Category = "Docks", "tblDEDocks", _
-
rstName!Category = "MFCs", "tblDEMFCs", _
-
rstName!Category = "Monitors", "tblDEMonitors", _
-
rstName!Category = "Printers", "tblDEPrinters", _
-
rstName!Category = "Other DE", "tblDEOthers")
-
-
SQL = "UPDATE " & Table & " " & _
-
"SET AssetNo = n " & _
-
"WHERE [Employee Name] = '" rstName![Employee Name] "' "& _
-
"AND SerialNo = '" & rstName!SerialNo & "';"
-
db.Execute SQL, dbFailOnError
-
Set n = n + 1
-
rstName.MoveNext
-
Loop
-
-
Set rstName = Nothing
-
Set db = Nothing
-
End Sub
-
-
4 2347
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 - "SET AssetNo = " & n & _
-
" WHERE [Employee Name] = '" & rstName![Employee Name] & "' "& _
-
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: - Private Sub AssetUpdate()
-
Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
-
Dim n As Integer, Table As String
-
-
Set db = CurrentDb
-
n = 1
-
-
'Setup Recordset
-
SQL = "SELECT [Employee Name], SerialNo, AssetNo, Category " & _
-
"FROM qryEquipEmplOnly " & _
-
"ORDER BY [Employee Name];"
-
Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)
-
-
'Parse thru tables & update asset numbers
-
Do Until rstName.EOF
-
Table = Switch(rstName!Category = "Computers", "tblComputers", _
-
rstName!Category = "Cameras", "tblDECameras", _
-
rstName!Category = "Docks", "tblDEDocks", _
-
rstName!Category = "MFCs", "tblDEMFCs", _
-
rstName!Category = "Monitors", "tblDEMonitors", _
-
rstName!Category = "Printers", "tblDEPrinters", _
-
rstName!Category = "Other DE", "tblDEOthers")
-
-
SQL = "UPDATE " & Table & _
-
" SET [AssetNo] = " & n & _
-
" WHERE [Employee Name] = '" & rstName![Employee Name] & "' " & _
-
"AND [SerialNo] = '" & rstName!SerialNo & "';"
-
db.Execute SQL, dbFailOnError
-
n = n + 1
-
rstName.MoveNext
-
Loop
-
-
Set rstName = Nothing
-
Set db = Nothing
-
End Sub
-
-
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 qryEquipEmplOnl y 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.
Stewart,
It turned out that I needed to write a more complex SQL statement, which I did get to work: - SQL = "UPDATE tblEmployees INNER JOIN " & Table & _
-
" ON tblEmployees.EmployeeID = " & Table & ".EmployeeID" & _
-
" SET " & Table & ".AssetNo = " & n & _
-
" WHERE (((tblEmployees.[Employee Name]) = '" & rstName![Employee Name] & "') " & _
-
"AND ((" & Table & ".SerialNo) = '" & rstName!SerialNo & "'));"
-
db.Execute SQL, dbFailOnError
-
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!
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 other table. and
iretrive data from second table by giving some condition. when i get data,
then to update first table i need to use do while loop. instead of that i
want to use select statement directly in update query.
plz give me some help....
|
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 be updated
when a group of items is copied. I can successfully do this with
cursors, but am experimenting with a way to do it with a single update
statement.
I have verified that each row being returned to the Update statement
(in an...
|
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 Update statement.
I have a sample code to reproduce my problem.
To simplify the scenario I am trying to use Order related
tables to explain a little better the tables i have to work with.
|
by: vj |
last post by:
How to Update multiple tables in a single SQL update Statement?
Is there any way out?
vj.
|
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 Access.
Is it even possible, or are there at least any workarounds?
My goal is for my client to just have to run a query (or a macro or
something) to update his products table with a previously imported
| |
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 northwind database that has
an employees, orders, and order details.
the following are the 3 tables in my sql database
students
schyrsem
|
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:
InvalidOperationException:
"Dynamic SQL generation is not supported against a SelectCommand that does
not return any base table information."
I tried to use an update query but when I fill my DataTable using
myDataAdapter.Fill(myDataSet, myTable)
|
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, and etc.
I'm trying to accomplish this in a single query, and be able to run
this query and verify that players are in the right division.
Is it possible to update multiple records in a table with a single
query while having criteria?
|
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 framework to auto-generate a table
adapter specifying both stored procs as Get/Fill and Update. The problem is
that columns from the JOINed table seem to marked as 'read-only' so trying
to update a row results in an exception. BTW, by default a...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |