?
Solved

Another newbie dumb question  on Access VBA and SQL

Posted on 2003-02-28
11
Medium Priority
?
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
11 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
Industry Leaders: 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

Industry Leaders: 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!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 10 hours left to enroll

764 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