Q: multiple inserts in Access query

  • Thread starter Thread starter David Boyer
  • Start date Start date
D

David Boyer

Is it possible to use Access queries in a way similar to SQL Stored
Procedures? We need to perform inserts against several tables, and it would
be nice to encompass all of the inserts into the same same query and just
pass the query some parameters.

TIA!
 
Hi David,

If you are asking could run Access queries like you would a Stored
Procedure in SQL, example:

cmd.execute "stored procedure name" "paramater1",
"parameter2"

Then the answer is no the syntax would not be the same. Instead your
syntax would something like:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("Query3")
qdf![enter a value] = "a"
qdf.Execute

See: ACC2000: "Too Few Parameters. Expected 1" Error Message
http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights



--------------------
| From: "David Boyer" <[email protected]>
| Newsgroups: comp.databases.ms-access,microsoft.public.access.queries
| Subject: Q: multiple inserts in Access query
| Date: Tue, 9 Mar 2004 14:00:49 -0600
| Organization: Airnews.net! at Internet America
| Lines: 8
| Message-ID: <[email protected]>
| Abuse-Reports-To: abuse at airmail.net to report improper postings
| NNTP-Proxy-Relay: library2.airnews.net
| NNTP-Posting-Time: Tue, 09 Mar 2004 14:00:11 -0600 (CST)
| NNTP-Posting-Host: !_(K^1k-VK;4TEc4YjeQ (Encoded at Airnews!)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Path:
cpmsftngxa06.phx.gbl!cpmsftngxa09.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
l.t-online.de!t-online.de!newsfeed.wirehub.nl!news2.euro.net!news.cambrium.n
l!news.cambrium.nl!feeder.enertel.nl!nntpfeed-01.ops.asmr-01.energis-idc.net
!news.glorb.com!newsfeed2.telusplanet.net!newsfeed.telus.net!nntp.abs.net!ne
ws.maxwell.syr.edu!news.airnews.net!cabal13.airnews.net!usenet
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:193023
| X-Tomcat-NG: microsoft.public.access.queries
|
| Is it possible to use Access queries in a way similar to SQL Stored
| Procedures? We need to perform inserts against several tables, and it
would
| be nice to encompass all of the inserts into the same same query and just
| pass the query some parameters.
|
| TIA!
|
|
|
 
Dear David:

A handy approach is to create a linked server in your SQL Server to
the Jet tables. You can then run SQL scripts against the tables. The
alternatives involve creating the queries dynamically in modules using
VBA.

Jet can only run one query at a time, and doesn't allow DECLAREs and
other features that make live so much easier.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
David Boyer said:
Is it possible to use Access queries in a way similar to SQL Stored
Procedures? We need to perform inserts against several tables, and it would
be nice to encompass all of the inserts into the same same query and just
pass the query some parameters.

TIA!
Click to expand...

Hello David.

I believe what your looking for is an "Append" query and yes,
this can be done in Access with some parameters.

Create a query with your required table as the source. Then add
the fields needed. Put your parameters in the field columns, in
the criteria row. Example for a LastName field: "Smith".
Or, [Enter Last Name]. This will prompt the user to type the
Last name being searched for. Or, Like "*Smi*". This will find
all last names that have "smi" in them due to the wild card
character "*" being used.

Do a search in the help files for Access on Append Queries.

Regards,

Ray
 
Back
Top