473,320 Members | 1,713 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,320 software developers and data experts.

GO Statement in Access?

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
table (same formatting on both tables).
Thanks alot, this one has been bugging me for a while!

Nov 13 '05 #1
4 4771
marty3d wrote:
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?


It is not possible to execute several statements in one query, but it is
very easy to run code or a macro that will execute several individual
queries or SQL Statements in sequence.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2
Actually, GO in Query analyzer doesn;t combine multiple queries, it separates
them and causes them to be executed as separate requests to the server. For a
simple series of UPDATE statements, SQL Server doesn't require GO between them
at all.

In JET, you can't execute multiple statements in one call, so you simply need
to execute one statement per call (equivalent to using GO in Query Analyzer).

If you want to prepare a set of statements prior to calling a single procedure
to execute them all, the simplest way I know of is to build a collection of
SQL statements or a collection of Querydef objects, and pass that to a
procedure that loops through the items in the collection using For Each,
executing each statement in a loop.

If you want, you could actually parse out <newline>GO<newline> from a set of
statements in a single string using the Split function, and use vbCrLf & "GO"
& vbCrLf as the delimiter. That gives you an array of SQL statements you can
loop through.

On 18 Jan 2005 08:23:44 -0800, "marty3d" <ma****@martinkjellberg.com> wrote:
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
table (same formatting on both tables).
Thanks alot, this one has been bugging me for a while!


Nov 13 '05 #3
"marty3d" <ma****@martinkjellberg.com> wrote:
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
table (same formatting on both tables).
Thanks alot, this one has been bugging me for a while!


In Access, one query cannot contain more than one SQL statement. The
quickest way would be to build a macro with a series of OpenQuery
actions. However, you will have no control if anythihng goes wrong.

In VBA code, you would do something like the following:

Public Function DoUpdate()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDB
Set qdf = db.CreateQueryDef("","your_qry1_Update")
qdf.Execute dbFailOnError
qdf.Close

Set qdf = db.CreateQueryDef("","your_qry2_Insert")
qdf.Execute dbFailOnError
qdf.Close

.... etc.

Set qdf = Nothing
db.Close
Set db = Nothing

Exit Function
ErrHandler: Call MsgBox(Err.Number & ": " & Err.Description)
End Function

You can start this function in a macro with the RunCode action.

HTH
Matthias Kläy
--
www.kcc.ch
Nov 13 '05 #4
marty3d wrote:
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
table (same formatting on both tables).
Thanks alot, this one has been bugging me for a while!


I don't think you can have multiple statements in Access queries... just
run a macro that calls each query ??

Br@dley
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

35
by: Thomas Matthews | last post by:
Hi, My son is writing a program to move a character. He is using the numbers on the keypad to indicate the direction of movement: 7 8 9 4 5 6 1 2 3 Each number has a direction except...
7
by: Bob | last post by:
Currently I am using this statement to translate 3 fields in my db thru Visual Basic. I import the data from one table to another then call the IFF statements and the NewDate to translate the...
11
by: Colleyville Alan | last post by:
I posted that I was having trouble with a SQL statement that was working in the SQL window, but not in VBA. I have since discovered that when I create the string in VBA it is over 1023 characters...
14
by: Ryan | last post by:
I want to do the following SQL statement in Access. However, it won't allow me to have the secondary part of my join statement and tells me that this is not supported. OK, so Access doesn't support...
3
by: Andy_Khosravi | last post by:
I have been trying to build a user friendly search engine for a small database I have created. I'm having some particular problems with one of my date fields. Here's the setup: I'm using...
7
by: kosta | last post by:
hello! one of my forms communicates with a database, and is supposed to add a row to a table using an Insert statement... however, I get a 'oledb - syntax error' exception... I have double...
10
by: John Smith | last post by:
Can you do a Select Statement within a Select Statement? I want to build a query similar to queries built in Access which link to other queries but using only SQL Statements. Is it possible? If...
6
by: FayeC | last post by:
I really need help figuring this out. i have a db with mostly text fields but 2. The user_id field is an autonumber (key) and the user_newsletter is a number (1 and 0) field meaning 1 yes the ...
4
by: Dave | last post by:
I have a field with data type of memo in my access database. I run a query from c# to access this field and it cuts off my string to only 255 characters. If i run this query directly in access...
7
by: Steven Bethard | last post by:
I've updated PEP 359 with a bunch of the recent suggestions. The patch is available at: http://bugs.python.org/1472459 and I've pasted the full text below. I've tried to be more explicit about...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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.