Solved

Access VBA / SQL - Insert, Select and Where statement error

Posted on 2011-09-28
6
1,045 Views
Last Modified: 2012-05-12
Hi Experts,

I need your help. Can someone please review this SQL VB statement for me. i cant seem to make it work from a push of a button on a form.

strSQL1c = "INSERT INTO [ar_client]([uniqueid_c],[clientcode_c],[firstname_vc],[lastname_vc],[create_dt],[createuser_c],[touch_date],[collection_c],[touch_user],[arnoteid_c])SELECT" & _
"[dbo_IDgenerator.cluniqueID],[dbo_IDgenerator.clientcode_c],[dbo_IDgenerator.firstname_vc],[dbo_IDgenerator.lastname_vc],[dbo_IDgenerator.create_dt],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.create_dt],'n' AS [collection_c],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.cluniqueID] AS arnoteid_c" & _
"From [dbo_IDgenerator] WHERE ((([dbo_IDgenerator.createstatus])='SMF-Pending'))"
0
Comment
Question by:bootyfreakk
  • 3
  • 2
6 Comments
 
LVL 75
ID: 36719930
Seems you are missing an Execute statement:

strSQL1c = "INSERT INTO [ar_client]([uniqueid_c],[clientcode_c],[firstname_vc],[lastname_vc],[create_dt],[createuser_c],[touch_date],[collection_c],[touch_user],[arnoteid_c])SELECT" & _
"[dbo_IDgenerator.cluniqueID],[dbo_IDgenerator.clientcode_c],[dbo_IDgenerator.firstname_vc],[dbo_IDgenerator.lastname_vc],[dbo_IDgenerator.create_dt],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.create_dt],'n' AS [collection_c],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.cluniqueID] AS arnoteid_c" & _
"From [dbo_IDgenerator] WHERE ((([dbo_IDgenerator.createstatus])='SMF-Pending'))"

CurrentDB.Execute strSQL1c , dbFailOnError

0
 
LVL 3

Author Comment

by:bootyfreakk
ID: 36719947
im using
 DoCmd.RunSQL strSQL1c

 to run this statement. let me try that code.
 
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 300 total points
ID: 36719962
You need more spaces:

strSQL1c = "INSERT INTO [ar_client] ([uniqueid_c],[clientcode_c],[firstname_vc],[lastname_vc], " & _
    "[create_dt],[createuser_c],[touch_date],[collection_c],[touch_user],[arnoteid_c]) " & _
    "SELECT [dbo_IDgenerator.cluniqueID],[dbo_IDgenerator.clientcode_c]," & _
    "[dbo_IDgenerator.firstname_vc],[dbo_IDgenerator.lastname_vc]," & _
    "[dbo_IDgenerator.create_dt],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.create_dt]," & _
    "'n' AS [collection_c],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.cluniqueID] AS arnoteid_c " & _
    "From [dbo_IDgenerator] WHERE [dbo_IDgenerator.createstatus]='SMF-Pending'" 

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75
ID: 36719973
Whereas the Execute method is better, that's not the issue then.

What Exactly is the error you are getting ?

My guess ... try this:


WHERE ((([dbo_IDgenerator.createstatus])=" & Chr(34) & "SMF-Pending" & Chr(34) ))"
0
 
LVL 3

Author Comment

by:bootyfreakk
ID: 36720016
still wont let me run it. i'm missing something or wrote something wrong.
 on a simple query if you paste in
INSERT INTO ar_client ( uniqueid_c, clientcode_c, firstname_vc, lastname_vc, create_dt, createuser_c, touch_date, collection_c, touch_user, arnoteid_c )
SELECT dbo_IDgenerator.cluniqueID, dbo_IDgenerator.clientcode_c, dbo_IDgenerator.firstname_vc, dbo_IDgenerator.lastname_vc, dbo_IDgenerator.create_dt, dbo_IDgenerator.createuser_c, dbo_IDgenerator.create_dt, "n" AS collection_c, dbo_IDgenerator.createuser_c, dbo_IDgenerator.cluniqueID AS arnoteid_c
FROM dbo_IDgenerator
WHERE (((dbo_IDgenerator.createstatus)="SMF-Pending"));

you can see what i'm trying to achive. the simple query works but i would like to use a SQL insert instead.
0
 
LVL 3

Author Comment

by:bootyfreakk
ID: 36720064
Hey matthewspatrick
It worked like a charm. Thanks your your help.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now