Another newbie dumb question on Access VBA and SQL

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?
countyworkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Da_WeaselSenior Software AnalystCommented:
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
countyworkerAuthor Commented:
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
spiritwithinCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arthur_WoodCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spiritwithinCommented:
Alright. Arthur_Wood deserves the points, he knew what i missed to explain to you how to go.
0
Arthur_WoodCommented:
thank you.  I use VBA inAccess almost exclusively at the moment, though I stay away from using DoCmd.------


AW
0
sphairosCommented:
did you join your database with your vb application???
0
countyworkerAuthor Commented:
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
CleanupPingCommented:
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
DanRollinsCommented:
Moderator, my recommended disposition is:

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

DanRollins -- EE database cleanup volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.