Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Another newbie dumb question  on Access VBA and SQL

Posted on 2003-02-28
11
Medium Priority
?
229 Views
Last Modified: 2010-04-07
I am trying to use SQL statements in my Access VBA code by using the Query tab and then switching to the SQL view and cutting and pasting into my VBA code but it doesn't work when I run it-what do I have to do, am I missing some steps so that it will work correctly? Is there some delimters or whatever that I place and the begin/end of my SQL statements?
0
Comment
Question by:countyworker
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8045230
sometimes you need to have ;'s at the end of statments when you have subqueries and such.
You also need to be aware that Access doesnt full support all SQL statements.
If you post the query here I can help you figure out what is wrong with it.  A basic layout of the table involved in the query would also be useful.
0
 

Author Comment

by:countyworker
ID: 8045345
here's the structure
of 2 tables
micrswrk-
pat_lname a20
pat_fname a20
malta
last name a20
first name a20
want to append the first and last names in malta into
micrswrk (there are several tables like malta with different names but they all would append into micrswrk)
this is the sql coding (that doesn't run) I cut and pasted from the SQL view:
Option Compare Database
Public Sub test()
Set qpt = CurrentDb.openrecordset("malta")
DoCmd.SetWarnings False
INSERT INTO micrswrk ( Pat_lname, Pat_fname)
SELECT qpt.[LAST NAME], qpt.[FIRST NAME]
FROM qpt;
DoCmd.SetWarnings True
End Sub
thanks
0
 
LVL 1

Expert Comment

by:spiritwithin
ID: 8046020
I never tried coding VBA providing itself with SQL, but maybe it is that:

INSERT INTO micrswrk ( Pat_lname, Pat_fname)
SELECT qpt.[LAST NAME], qpt.[FIRST NAME]
FROM qpt;

Shouldnt there be some function call, something like callSql("INSERT INTO ... ") ? I don't know, maybe the DoCmd thing means that the code between True and False is pure SQL.

Cheers.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 200 total points
ID: 8046313
change this:

Public Sub test()
Set qpt = CurrentDb.openrecordset("malta")
DoCmd.SetWarnings False
INSERT INTO micrswrk ( Pat_lname, Pat_fname)
SELECT qpt.[LAST NAME], qpt.[FIRST NAME]
FROM qpt;
DoCmd.SetWarnings True
End Sub


to this:

Public Sub test()
dim strSQL as String

DoCmd.SetWarnings False
strSQL = "INSERT INTO micrswrk ( Pat_lname, Pat_fname) " & _
"SELECT qpt.[LAST NAME], qpt.[FIRST NAME] FROM malta;"
Docmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub

AW
0
 
LVL 1

Expert Comment

by:spiritwithin
ID: 8046326
Alright. Arthur_Wood deserves the points, he knew what i missed to explain to you how to go.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8046342
thank you.  I use VBA inAccess almost exclusively at the moment, though I stay away from using DoCmd.------


AW
0
 

Expert Comment

by:sphairos
ID: 8051142
did you join your database with your vb application???
0
 

Author Comment

by:countyworker
ID: 8058444
It almost worked-I have one problem-I can get the variable
qpt (malta in this case) declared correctly-it's taking that from a field in another table in another sub procedure(this will be a different name as this loops thru several table names)
I can display it but when it gets to the runSQL statment
it finds it as 'qpt' not the variable that's the table name
Public Sub test('put table name variable here?)
'set recordset statement
'declare the variable qpt correctly statment?
dim strSQL as String

DoCmd.SetWarnings False
strSQL = "INSERT INTO micrswrk ( Pat_lname, Pat_fname) " &_
"SELECT qpt.[LAST NAME], qpt.[FIRST NAME] FROM malta(qpt?);"
'if I have several fields (very long list) how do I wrap them
Docmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub

0
 

Expert Comment

by:CleanupPing
ID: 8940423
countyworker:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 9001712
Moderator, my recommended disposition is:

    Accept Arthur_Wood's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question