Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-09-28
6
Medium Priority
?
1,128 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 93

Accepted Solution

by:
Patrick Matthews earned 1200 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 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